5.2 MySQL 엔진의 잠금

 

• 잠금의 레벨
  • MySQL 레벨의 잠금
    • 모든 스토리지 엔진에 영향을 미침
    • 테이블 락 외에도 메타데이터 락(테이블 구조 잠금), 네임드 락 등 기능 제공
  • 스토리지 엔진 레벨의 잠금
    • 스토리지 엔진 간 상호 영향 없음

5.2.1 Global Lock(글로벌 락)

DML(Data Manipulation Language, 데이터 조작어)란?

• 정의된 데이터베이스에 입력된 레코드를 조회, 수정, 삭제 등의 역할을 하는 언어
• SELECT INSERT UPDATE DELETE

DDL(Data Definition Language, 데이터 정의어)란?

• 데이터베이스를 정의하는 언어 (데이터 전체의 골격을 결정)
• CREATE ALTER DROP TRUNCATE
• FLUSH TABLES WITH READ LOCK 명령으로 획득
• MySQL에서 제공하는 잠금 가운데 가장 범위가 큼
• 한 세션에서 글로벌 락 획득 시,
  • 다른 세션에서 SELECT를 제외한 대부분의 DDL, DML 문장 실행이 대기상태로 남음
• 글로벌 락의 영향 범위 : MySQL 서버 전체
  • 작업 대상 테이블이나 DB가 다르더라도 동일하게 영향
• MySQL 8.0 이후
  • InnoDB 기본 스트리지 엔진 채택
  • Backup Lock(백업 락) 도입
    • Xtrabackup이나 Enterprise Backup과 같은 백업 툴들의 안정적 실행을 위한 백업 락 도입
  • 코드

  mysql> LOCK INSTANCE FOR BACKUP;
  -- // 백업 실행
  mysql> UNLOCK INSTANCE;

  • 특정 세션에서 백업 락 획득 시, 모든 세션에서 다음 정보는 변경 불가
    • 데이터베이스 및 테이블 들 모든 객체 생성 및 변경, 삭제
    • REPAIR TABLE과 OPTIMIZE TABLE 명령
    • 사용자 관리 및 비밀번호 변경
  • 하지만 일반적인 테이블의 데이터 변경은 허용
  • 백업의 실패를 막기 위해서 DDL 명령이 실행되면 복제를 일시 중지하는 역할

5.2.2 Table Lock(테이블 락)

---

• 개별 테이블 단위로 설정되는 잠금
• 명시적 또는 묵시적으로 특정 테이블 락 획득 가능
• 명시적으로 획득한 테이블 락
  • LOCK TABLES table_name [READ | WRITE] 명령으로 특정 테이블 락 획득
  • UNLOCK TABLES : 잠금 반납(해제)
  • 글로벌 락과 동일하게 온라인 작업에 상당한 영향을 미침
• 묵시적인 테이블 락
  • 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료되고 자동 해제
  • 하지만 InnoDB 테이블은 스토리지 엔진 차원에서 레코드 기반의 잠금 제공
    • 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지 않음
    • 테이블 락이 설정은 되지만 DDL의 경우에만 영향

5.2.3 Named Lock(네임드 락)

---

• 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금
• 사용처
  • 여러 클라이언트가 상호 동기화를 처리해야 하는 경우
  • 많은 레코드에 대해 복잡한 요건으로 레코드를 변경하는 트랜잭션일 경우
• MySQL 8.0 이후, 네임드 락 중첩 사용 가능
  • 한 번에 락 해제도 가능
• 코드

// mylock 이라는 문자열 잠금 관련 코드
mysql> SELECT GET_LOCK('mylock', 2); // 잠금 사용중일 경우, 2초 대기
mysql> SELECT IS_FREE_LOCK('mylock'); // 해당 문자열의 잠금 설정 확인
mysql> SELECT RELEASE_LOCK('mylock'); // 잠금 반납(해제)
// 2개 이상의 네임드 락 사용 가능 (MySQL 8.0 이후)
mysql> SELECT GET_LOCK('mylock_1', 10);
mysql> SELECT GET_LOCK('mylock_2', 10);
mysql> SELECT RELEASE_ALL_LOCK();  // 모든 네임드 락 해제

5.2.4 Metadata Lock(메타데이터 락)

---

• 데이터베이스 객체(테이블, 뷰 등)의 이름이나 구조를 변경하는 경우에 잠금 획득
  • 명시적으로 획득하거나 해제 할 수 있는 것이 아님
  • RENAME TABLE 명령과 같이 테이블 이름 변경 시, 자동 획득
  • RENAME TABLE 명령은 원본 이름과 변경 이름 모두 잠금 설정
• 한 세션에서 트랜잭션을 명시적으로 시작하고 데이터를 조회 중
  • 다른 세션에서 테이블의 구조를 변경하게 되면 Metadata Lock 으로 대기를 하게 됩니다.

-- Session 1
select CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               4 |
+-----------------+
<-- Session 1의 Process ID 는 4 로 확인됨

-- 트랜잭션 시작
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user_info;
+----+------+
| id | name |
+----+------+
|  1 | jade |
|  2 | Tom  |
+----+------+
2 rows in set (0.00 sec)

-- Session 2 : 컬럼 추가 시도
mysql> alter table user_info add column col2 varchar(100);
<!!--- 락 획득 실패 및 락에 의해 대기중

-- Session 1 : Full processlist
mysql> show full processlist;
+----+------------+------+------+---------------------------------+----------------------------------------------------+
| Id | User       | db   | Time | State                           | Info                                               |
+----+------------+------+------+---------------------------------+----------------------------------------------------+
|  2 | system user| NULL | 1321 | Connecting to master            | NULL                                               |
|  4 | root       | npm  |    0 | starting                        | show full processlist                              |
|  8 | root       | npm  |   10 | Waiting for table metadata lock | alter table user_info add column col2 varchar(100) |
+----+------------+------+------+---------------------------------+----------------------------------------------------+
<!!-- Metadata lock 에 의해 Session 2가 대기 하는 상황

참고사이트

• MySQL 트랜잭션과 잠금 참고

MySQL - 트랜잭션 과 잠금 (1) - Real MySQL 8.0

• DDL, DML, DCL 용어 참고

DDL, DML, DCL 이란?