Last Updated on 3월 31, 2023 by Jade(정현호)
안녕하세요.
이번 포스팅은 MySQL 의 트랜잭션 과 잠금(Lock) 에 대해서 확인 해보려고 합니다.
전체적인 내용은 Real MySQL 8.0 책 과 MySQL Document 를 정리 한 내용 으로 아래 포스팅에서 이어지는 글 입니다.
InnoDB 스토리지 엔진 잠금
InnoDB 스토리지 엔지은 MySQL 에서 제공하는 잠금과 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재 하고 있습니다.
InnoDB는 레코드 기반의 잠금을 지원함으로 MyISAM 보다 훨씬 뛰어난 동시성 처리를 제공 하고 있습니다.
하지만 이원화된 잠금 처리를 하기 때문에 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해서 접근 하기가 상당히 까다롭고 어렵습니다.
예전 버전의 MySQL서버에서는 InnoDB의 잠금 정보를 진단할 수 있는도구라고는 lock_monitor(innodb_lock_monitor 라는 이름의 InnoDB 테이블을 생성해서 InnoDB의 잠금 정보를 덤프 하는 방법) 와 show engine innodb status 명령이 전부 였습니다.
하지만 이 내용의 결과를 보는데도 이해하기가 상당히 어려웠습니다.
최근 버전의 MySQL 에서는 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입 되었습니다. MySQL 서버의 Information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 라는 딕셔너리 뷰를 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지를 확인 할 수 있습니다.
InnoDB의 잠금에 대한 모니터링도 더 강화되면서 Performance schema 를 이용해 InnoDB 스토리지 엔진의 내부 잠금(세마포어)에 대한 모니터링 방법도 추가 되었습니다.
InnoDB 엔진의 잠금의 종류
InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리 되기 때문에 레코드 락이 페이지 락으로 또는 테이블 락으로 레벨업(락 에스컬레이션) 되는 경우가 없습니다.
(Oracle Database 도 락 에스컬레이션이 발생되지 않습니다)
보통의 RDBMS 와는 다르게 InnoDB 스토리지 엔진에서는 레코드 락 뿐 만아니라 레코드 와 레코드 사이의 간격을 잠그는 갭(Gap) 락이라는 것이 존재 합니다.
레코드 락(Record Lock)
레코드 자체만을 잠금는 것을 레코드 락(Record lock, Record only lock) 이라고 하며, 다른 상용 DBMS의 레코드 락(또는 Row lock) 과 동일한 역할을 하게 됩니다.
한가지 중요한 차이점은 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점 입니다.
InnoDB 테이블은 테이블 생성시 PK 인덱스를 명시적으로 지정하지 않아도 내부적으로 자동 생성된 클러스터 인덱스를 이용해서 잠금을 설정 합니다.
레코드 자체를 잠그는 것과 인덱스를 잠그는 것은 상당히 크고 중요한 차이를 만들어 내기 때문 입니다.
인덱스의 잠금 확인
위에서 언급한 레코드 자체를 잠그는 것과 인덱스를 잠그는 것의 차이에 대해서 InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리가 됩니다 즉 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 합니다
(다만 이것은 isolation 에 따라서 결과가 달라지긴 합니다)
테스트를 위해서 employees 테이블 데이터를 가공하여 아래와 같이 생성을 하였습니다
(테이블 insert 구문 다운로드)
mysql> select * from tb_test_user_info;
+----+--------+------------+-------------+------------+
| id | emp_no | first_name | last_name | hire_date |
+----+--------+------------+-------------+------------+
| 1 | 10001 | Georgi | Facello | 1985-11-21 |
| 2 | 10002 | Mary | Simmel | 1986-08-28 |
| 3 | 10003 | Mary | Bamford | 1986-12-01 |
| 4 | 10004 | Mary | Koblick | 1989-09-12 |
| 5 | 10005 | Mary | Maliniak | 1989-06-02 |
| 6 | 10006 | Mary | Preusig | 1989-02-10 |
| 7 | 10007 | Mary | Zielinski | 1994-09-15 |
| 8 | 10008 | Saniya | Kalloufi | 1985-02-18 |
| 9 | 10009 | Sumant | Peac | 1989-08-24 |
| 10 | 10010 | Duangkaew | Piveteau | 1990-01-22 |
| 11 | 10011 | Mary | Sluis | 1992-12-18 |
| 12 | 10012 | Patricio | Bridgland | 1985-10-20 |
| 13 | 10013 | Mary | Terkki | 1987-03-11 |
| 14 | 10014 | Mary | Genin | 1987-07-02 |
| 15 | 10015 | Guoxiang | Nooteboom | 1995-01-27 |
| 16 | 10016 | Mary | Cappelletti | 1993-08-03 |
| 17 | 10017 | Mary | Bouloucos | 1987-04-03 |
| 18 | 10018 | Mary | Peha | 1999-04-30 |
| 19 | 10019 | Lillian | Haddadi | 1991-01-26 |
| 20 | 10020 | Mayuko | Warwick | 1988-02-10 |
| 21 | 10021 | Ramzi | Erde | 1995-08-22 |
| 22 | 10022 | Mary | Famili | 1989-12-17 |
| 23 | 10023 | Bojan | Montemayor | 1997-05-19 |
| 24 | 10024 | Mary | Pettey | 1987-08-17 |
| 25 | 10025 | Mary | Heyers | 1995-03-20 |
| 26 | 10026 | Mary | Berztiss | 1989-07-07 |
| 27 | 10027 | Divier | Reistad | 1991-10-22 |
| 28 | 10028 | Mary | Tempesti | 1985-11-20 |
| 29 | 10029 | Mary | Herbst | 1994-02-17 |
| 30 | 10030 | Elvis | Demeyer | 1991-09-01 |
| 31 | 10031 | Karsten | Joslin | 1990-06-20 |
| 32 | 10032 | Jeong | Reistad | 1987-03-18 |
| 33 | 10033 | Arif | Merlo | 1988-09-21 |
| 34 | 10034 | Bader | Swan | 1988-09-05 |
| 35 | 10035 | Alain | Chappelet | 1992-01-03 |
| 36 | 10036 | Adamantios | Portugali | 1990-12-05 |
| 37 | 10037 | Pradeep | Makrucki | 1989-09-20 |
| 38 | 10038 | Huan | Lortz | 1988-01-19 |
| 39 | 10039 | Alejandro | Brender | 1993-02-14 |
| 40 | 10040 | Weiyi | Meriste | 1989-11-12 |
| 41 | 10041 | Uri | Lenart | 1993-03-21 |
| 42 | 10042 | Magy | Stamatiou | 1990-10-20 |
| 43 | 10043 | Mary | Tzvieli | 1994-05-21 |
| 44 | 10044 | Mingsen | Casley | 1989-09-02 |
| 45 | 10045 | Moss | Shanbhogue | 1992-06-20 |
| 46 | 10046 | Mary | Rosenbaum | 1989-03-31 |
| 47 | 10047 | Mary | Nyanchama | 1985-02-24 |
| 48 | 10048 | Mary | Syrotiuk | 1992-05-04 |
| 49 | 10049 | Basil | Tramer | 1990-12-25 |
| 50 | 10050 | Mary | Dredge | 1992-10-15 |
| 51 | 10051 | Mary | Caine | 1988-05-21 |
| 52 | 10052 | Heping | Nitsch | 1986-02-04 |
| 53 | 10053 | Mary | Zschoche | 1995-03-13 |
| 54 | 10054 | Mary | Schueller | 1992-04-27 |
| 55 | 10055 | Mary | Dredge | 1990-02-01 |
| 56 | 10056 | Brendon | Bernini | 1992-01-15 |
| 57 | 10057 | Ebbe | Callaway | 1987-04-13 |
| 58 | 10058 | Berhard | McFarlin | 1991-06-26 |
| 59 | 10059 | Alejandro | McAlpine | 1987-11-02 |
| 60 | 10060 | Breannda | Billingsley | 1985-09-17 |
| 61 | 10061 | Tse | Herber | 1991-08-30 |
| 62 | 10062 | Anoosh | Peyn | 1989-04-08 |
| 63 | 10063 | Gino | Leonhardt | 1985-11-20 |
| 64 | 10064 | Udi | Jansch | 1988-05-18 |
| 65 | 10065 | Satosi | Awdeh | 1986-02-26 |
| 66 | 10066 | Mary | Schusler | 1987-03-04 |
| 67 | 10067 | Mary | Stavenow | 1987-08-07 |
| 68 | 10068 | Charlene | Brattka | 1989-11-05 |
| 69 | 10069 | Margareta | Bierman | 1985-10-14 |
| 70 | 10070 | Reuven | Garigliano | 1987-10-01 |
| 71 | 10071 | Hisao | Lipner | 1988-07-21 |
| 72 | 10072 | Hironoby | Sidou | 1991-12-01 |
| 73 | 10073 | Shir | McClurg | 1990-08-13 |
| 74 | 10074 | Mary | Bernatsky | 1987-03-19 |
| 75 | 10075 | Gao | Dolinsky | 1985-07-09 |
| 76 | 10076 | Erez | Ritzmann | 1990-03-02 |
| 77 | 10077 | Mona | Azuma | 1987-05-26 |
| 78 | 10078 | Danel | Mondadori | 1986-03-27 |
| 79 | 10079 | Kshitij | Gils | 1985-11-19 |
| 80 | 10080 | Mary | Baek | 1986-10-30 |
| 81 | 10081 | Zhongwei | Rosen | 1990-01-03 |
| 82 | 10082 | Mary | Lortz | 1987-03-31 |
| 83 | 10083 | Mary | Zockler | 1995-12-15 |
+----+--------+------------+-------------+------------+
mysql> show create table tb_test_user_info\G
*************************** 1. row ***************************
Table: tb_test_user_info
Create Table: CREATE TABLE `tb_test_user_info` (
`id` int NOT NULL,
`emp_no` int DEFAULT NULL,
`first_name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`last_name` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`hire_date` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ix_first_name` (`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
first_name 과 last_name 컬럼 중에서 first_name 컬럼에만 인덱스가 생성되어 있습니다(ix_first_name)
그리고 데이터의 경우 아래와 같이 first_name='Mary' 이면서 last_name='Peha' 인 사람은 1명이고, first_name='Mary' 인 사람은 33명이 있습니다.
select * from tb_test_user_info where first_name='Mary' and last_name='Peha'; +----+--------+------------+-----------+------------+ | id | emp_no | first_name | last_name | hire_date | +----+--------+------------+-----------+------------+ | 18 | 10018 | Mary | Peha | 1999-04-30 | +----+--------+------------+-----------+------------+ mysql> select count(*) from tb_test_user_info where first_name='Mary'; +----------+ | count(*) | +----------+ | 33 | +----------+
isolation level 은 REPEATABLE-READ 이고, MySQL 8.0.25 입니다.
mysql> show session variables like '%isola%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ mysql> show variables like 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.25 | +-----------+
이와 같은 데이터와 DB 상황에서 아래와 같이 업데이트를 실행 하면 어떻게 될까요?
-- 테스트를 위해서 auto commit 은 OFF 하였습니다. mysql> set session autocommit=off; mysql> update tb_test_user_info set hire_date=DATE_FORMAT(now(), '%Y-%m-%d') where first_name='Mary' and last_name='Peha'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Rows matched 에는 1건 이라고 표기가 되지만, 이 UPDATE 문장의 조건절에서 사용할수 있는 인덱스는 first_name 으로 구성된 ix_first_name 인덱스 입니다.
ix_first_name 에는 last_name 컬럼이 없기 때문에 first_name='Mary' 인 레코드 33건에 대한 레코드를 모두 잠그게 됩니다.
performance_schema.data_locks 딕셔너리에서 Lock 을 건 정보를 확인 해보면 아래와 같이 많은 LOCK 이 걸려 있음을 확인 할 수 있습니다.
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139959967079832:1069:139959898023488
ENGINE_TRANSACTION_ID: 9926
THREAD_ID: 52
EVENT_ID: 101
OBJECT_SCHEMA: sysbench
OBJECT_NAME: tb_test_user_info
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139959898023488
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139959967079832:10:5:12:139959898020384
ENGINE_TRANSACTION_ID: 9926
THREAD_ID: 52
EVENT_ID: 101
OBJECT_SCHEMA: sysbench
OBJECT_NAME: tb_test_user_info
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: ix_first_name
OBJECT_INSTANCE_BEGIN: 139959898020384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 'Mary', 11
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139959967079832:10:5:70:139959898020384
ENGINE_TRANSACTION_ID: 9926
THREAD_ID: 52
EVENT_ID: 101
OBJECT_SCHEMA: sysbench
OBJECT_NAME: tb_test_user_info
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: ix_first_name
OBJECT_INSTANCE_BEGIN: 139959898020384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 'Mary', 60
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139959967079832:10:5:71:139959898020384
ENGINE_TRANSACTION_ID: 9926
THREAD_ID: 52
EVENT_ID: 101
OBJECT_SCHEMA: sysbench
OBJECT_NAME: tb_test_user_info
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: ix_first_name
OBJECT_INSTANCE_BEGIN: 139959898020384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 'Mary', 55
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139959967079832:10:5:72:139959898020384
ENGINE_TRANSACTION_ID: 9926
THREAD_ID: 52
EVENT_ID: 101
OBJECT_SCHEMA: sysbench
OBJECT_NAME: tb_test_user_info
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: ix_first_name
OBJECT_INSTANCE_BEGIN: 139959898020384
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 'Mary', 30
*************************** 6. row ***************************
< ... 중략 ...>
*************************** 35. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139959967079832:10:4:145:139959898020728
ENGINE_TRANSACTION_ID: 9926
THREAD_ID: 52
EVENT_ID: 101
OBJECT_SCHEMA: sysbench
OBJECT_NAME: tb_test_user_info
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139959898020728
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 75
*************************** 36. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139959967079832:10:5:127:139959898021072
ENGINE_TRANSACTION_ID: 9926
THREAD_ID: 52
EVENT_ID: 101
OBJECT_SCHEMA: sysbench
OBJECT_NAME: tb_test_user_info
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: ix_first_name
OBJECT_INSTANCE_BEGIN: 139959898021072
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 'Mayumi', 54
36 rows in set (0.04 sec)
mysql> select count(*) from performance_schema.data_locks
where OBJECT_NAME='tb_test_user_info';
+----------+
| count(*) |
+----------+
| 36 |
+----------+
Record Lock 과 GAP Lock 이 같이 걸려 있는 상태 입니다. LOCK 모드별로 확인 하면 아래와 같이 잠금이 잡혀 있는 상태 입니다.
mysql> select LOCK_MODE,COUNT(*) from performance_schema.data_locks group by LOCK_MODE; +---------------+----------+ | LOCK_MODE | COUNT(*) | +---------------+----------+ | IX | 1 | | X | 17 | | X,REC_NOT_GAP | 17 | | X,GAP | 1 | +---------------+----------+
UPDATE 구문을 실행한 세션이 아닌 다른 세션에서 first_name='Mary' 인 데이터 1건을 업데이트 하려고 하면 Lock 으로 대기하는 것을 확인할 수 있습니다.
mysql> select * from tb_test_user_info
where id=10\G
*************************** 1. row ***************************
id: 10
emp_no: 10010
first_name: Mary
last_name: Piveteau
hire_date: 1990-01-22
-- 테스트를 위해서 auto commit 은 OFF 하였습니다.
mysql> set session autocommit=off;
mysql> update tb_test_user_info
set hire_date=DATE_FORMAT(now(), '%Y-%m-%d')
where id=10;
<--- 락으로 대기함
이와 같이 레코드 자체를 잠그는것과 인덱스 레코드를 잠그는 것과는 큰 차이를 만들게 되며 위와 같이 UPDATE 나 DELETE 를 위해서 적절한 인덱스가 준비되어 있지 않다면 동시성이 상당히 많이 떨어지게 될 것 입니다.
인덱스가 하나도 없다면 어떻게 될까요? 이 경우에는 테이블을 풀 스캔 하여 UPDATE 를 하게 됩니다.
-- 인덱스 삭제
mysql> alter table tb_test_user_info drop index ix_first_name;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- UPDATE 수행 전에 PLAN 확인
mysql> explain
update tb_test_user_info
set hire_date=DATE_FORMAT(now(), '%Y-%m-%d')
where first_name='Mary' and last_name='Peha';
+----+-------------+-------------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | UPDATE | tb_test_user_info | index | NULL | PRIMARY | 4 | NULL | 83 | 100.00 | Using where |
+----+-------------+-------------------+-------+---------------+---------+---------+------+------+----------+-------------+
<!- 대상이 전체 건수임
-- 테스트를 위해서 auto commit 은 OFF 하였습니다.
mysql> set session autocommit=off;
mysql> update tb_test_user_info
set hire_date=DATE_FORMAT(now(), '%Y-%m-%d')
where first_name='Mary' and last_name='Peha';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
인덱스 삭제 후 업데이트를 하였고 performance_schema.data_locks 를 조회해보면 건수가 전체 건수 만큼 락이 걸린 것을 확인 할 수 있습니다.
그리고 지금 전체 대상으로 잠금을 하고 있기 때문에 first_name='Mary' 가 아닌 다른 데이터에 대해서 UPDATE 를 수행 하여도 락으로 대기하는 것을 확인 할 수 있습니다.
-- LOCK 건수가 이전에 비해 늘어남
mysql> select count(*) from performance_schema.data_locks;
+----------+
| count(*) |
+----------+
| 85 | <!!--
+----------+
-- first_name='Mary' 와 관계 없는 데이터임
mysql> select * from tb_test_user_info
where id=56\G
*************************** 1. row ***************************
id: 56
emp_no: 10056
first_name: Brendon
last_name: Bernini
hire_date: 1992-01-15
-- 업데이트 수행 -> 락으로 대기
mysql> update tb_test_user_info
set hire_date=DATE_FORMAT(now(), '%Y-%m-%d')
where id=56;
<!!-- 락에 의해서 대기함
위와 같은 과정에서 Isolation 을 REPEATABLE-READ 에서 READ-COMMITTED 으로 변경하면 또 다르게 진행 됩니다.
이전 단계에서 삭제한 인덱스를 다시 생성 하였고 isolation level 을 변경 하고 다시 UPDATE 를 수행 하겠습니다.
-- 다시 테스트를 위해서 삭제한 인덱스를 다시 생성 mysql> alter table tb_test_user_info add index ix_first_name(first_name); -- 테스트하는 2개 세션 모두에서 적용 mysql> set session transaction_isolation='READ-COMMITTED'; mysql> show variables like '%transaction_isolation%'; +-----------------------+----------------+ | Variable_name | Value | +-----------------------+----------------+ | transaction_isolation | READ-COMMITTED | +-----------------------+----------------+ -- 먼저 PLAN 을 확인 mysql> explain update tb_test_user_info set hire_date=DATE_FORMAT(now(), '%Y-%m-%d') where first_name='Mary' and last_name='Peha'; +----+-------------+-------------------+-------+---------------+---------------+---------+-------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+-------+---------------+---------------+---------+-------+------+----------+-------------+ | 1 | UPDATE | tb_test_user_info | range | ix_first_name | ix_first_name | 43 | const | 17 | 100.00 | Using where | +----+-------------+-------------------+-------+---------------+---------------+---------+-------+------+----------+-------------+ * 가로 길이에 따른 일부 컬럼이 편집 되어 있습니다. -- 테스트를 위해서 auto commit 은 OFF 하였습니다. mysql> set session autocommit=off; mysql> update tb_test_user_info set hire_date=DATE_FORMAT(now(), '%Y-%m-%d') where first_name='Mary' and last_name='Peha'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
그 다음에는 performance_schema.data_locks 을 조회해보겠습니다.
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139959967079832:1069:139959898023488
ENGINE_TRANSACTION_ID: 9949
THREAD_ID: 52
EVENT_ID: 130
OBJECT_SCHEMA: sysbench
OBJECT_NAME: tb_test_user_info
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139959898023488
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139959967079832:10:5:43:139959898020384
ENGINE_TRANSACTION_ID: 9949
THREAD_ID: 52
EVENT_ID: 130
OBJECT_SCHEMA: sysbench
OBJECT_NAME: tb_test_user_info
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: ix_first_name
OBJECT_INSTANCE_BEGIN: 139959898020384
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 'Mary', 18
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139959967079832:10:4:101:139959898020728
ENGINE_TRANSACTION_ID: 9949
THREAD_ID: 52
EVENT_ID: 130
OBJECT_SCHEMA: sysbench
OBJECT_NAME: tb_test_user_info
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139959898020728
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 18
3 rows in set (0.01 sec)
이전과는 다르게 Lock 이 적게 잡고 있는 것을 확인 할 수 있으며 갭 락 도 없는 상태입니다(REC_NOT_GAP)
fisrt_name='Mary' 인 데이터를 업데이트를 해보면 락 대기 없이 수행 되는 것을 확인 할 수 있습니다.
-- 업데이트 대상 확인
mysql> select * from tb_test_user_info
where id=10\G
*************************** 1. row ***************************
id: 10
emp_no: 10010
first_name: Mary
last_name: Piveteau
hire_date: 1990-01-22
mysql> set session autocommit=off;
mysql> update tb_test_user_info
set hire_date=DATE_FORMAT(now(), '%Y-%m-%d')
where id=10;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
<!!-- 락 대기 없이 UPDATE 수행됨
이와 같이 InnoDB 에서는 인덱스 레코드를 잠근 다는 것과 UPDATE 와 DELETE 와 같이 대상에 대해서 적절한 인덱스가 준비가 되어있지 않다면 동시성이 떨어지는 상황을 발생될 수 있으며 아래에서 계속 설명할 Next Key Lock 과 더불어서 많은 동시성에 문제나 제약이 발생될 수 있습니다. 그래서 이와 같은 상황으로 인하여 성능적인 저하 이슈인 경우 가능하다면 isolation 레벨을 READ-COMMITTED 으로 변경하는 것도 고려 해볼 수 있을 것 같습니다
갭 락(Gap Lock)
다른 DBMS 와의 또 다른 차이가 바로 갭 락(Gap Lock) 입니다. 갭 락은 레코드 자체가 아닌 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미 합니다.
이러한 갭락은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(Insert) 되는 것을 제어 하는 역할을 하게 됩니다.
인덱스 레코드의 가장 처음 레코드의 이전, 그리고 마지막 레코드의 이후에 대해서도 gap 에도 lock을 설정합니다.
(아래 예제에서 설명)
유니크 인덱스를 사용해서 특정 행을 검색할 때는 갭락이 사용 되지 않습니다. 다만 multi-column unique index 를 사용할 경우 검색 조건이 일부만 사용하는 경우는 제외합니다, 이 경우에는 갭 락을 사용하게 됩니다.
예를 들어, emp_no + last_name 으로 결합된 유니크 인덱스가 있을 경우 WHERE 절 검색 조건 중 일부 컬러만(예를 들어 emp_no 만) 사용할 경우 갭락은 발생하게 됩니다.
테스트 예제
위에서 사용한 테스트 테이블을 사용하여 계속 테스트를 진행하겠습니다. 먼저 Unique 인덱스를 생성 하도록 하겠습니다.
-- 이전의 테스트시 삭제를 안했다면 인덱스 삭제 mysql> alter table tb_test_user_info drop index ix_emp_no; -- 유니크 인덱스로 다시 생성 mysql> alter table tb_test_user_info add unique index ux_emp_no_last_name(emp_no,last_name);
테스트 상황에서의 isolation 과 binlog_format 정보 입니다.
mysql> show variables like '%isola%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | | tx_isolation | REPEATABLE-READ | +-----------------------+-----------------+ mysql> show variables like 'binlog_format'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | ROW | +---------------+-----------+
첫번째 세션부터 UPDATE 를 수행해보겠습니다.
## Session 1 -- thread id 값 조회 mysql> select thread_id from performance_schema.threads where processlist_id=(select connection_id()); +-----------+ | thread_id | +-----------+ | 48 | +-----------+ mysql> set session autocommit=off; -- 결합 인덱스 컬럼 모두를 조건절로 하여 UPDATE 수행 mysql> update tb_test_user_info set hire_date=DATE_FORMAT(now(), '%Y-%m-%d') where emp_no=10036 and last_name='Portugali'; -- LOCK 정보 조회 mysql> select LOCK_MODE,THREAD_ID,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; +---------------+-----------+-------------+------------------------+ | LOCK_MODE | THREAD_ID | LOCK_STATUS | LOCK_DATA | +---------------+-----------+-------------+------------------------+ | IX | 48 | GRANTED | NULL | | X,REC_NOT_GAP | 48 | GRANTED | 10036, 'Portugali', 36 | | X,REC_NOT_GAP | 48 | GRANTED | 36 | +---------------+-----------+-------------+------------------------+
이제 두번째 세션에서 UPDATE 를 수행 및 LOCk 정보를 확인 해보도록 하겠습니다.
이번 UPDATE 에서는 결합 인덱스 컬럼 중에서 선행 컬럼인 emp_no 컬럼만 where 절 조건으로 사용하여 UPDATE 를 수행하였습니다.
-- thread id 값 조회 mysql> select thread_id from performance_schema.threads where processlist_id=(select connection_id()); +-----------+ | thread_id | +-----------+ | 49 | +-----------+ -- 결함 인덱스 일부 커럼(선행 컬럼) 만 사용하여 UPDATE mysql> set session autocommit=off; update tb_test_user_info set hire_date=DATE_FORMAT(now(), '%Y-%m-%d') where emp_no=10045; -- LOCK 조회 mysql> select LOCK_MODE,THREAD_ID,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks order by THREAD_ID; +---------------+-----------+-------------+-------------------------+ | LOCK_MODE | THREAD_ID | LOCK_STATUS | LOCK_DATA | +---------------+-----------+-------------+-------------------------+ | IX | 48 | GRANTED | NULL | | X,REC_NOT_GAP | 48 | GRANTED | 10036, 'Portugali', 36 | | X,REC_NOT_GAP | 48 | GRANTED | 36 | | IX | 49 | GRANTED | NULL | | X | 49 | GRANTED | 10045, 'Shanbhogue', 45 | | X,REC_NOT_GAP | 49 | GRANTED | 45 | | X,GAP | 49 | GRANTED | 10046, 'Rosenbaum', 46 | <--- GAP LOCK 잠금 +---------------+-----------+-------------+-------------------------+ * THREAD_ID=49 가 두번째 세션임
THREAD 48 과는 달리 THREAD 49 에서는 결합 인덱스 컬럼 중 일부 컬럼만 WHERE 절에서 사용하였고, 위에서 확인되는 내용과 같이 THREAD 49 에서는 갭 락 잠금이 사용 되는 것을 확인할 수 있습니다.
그럼 결합 컬럼 중 다음 후행 컬럼만 사용하면 어떻게 될까요? 아래와 같이 잠금으로 대기 하게 됩니다.
-- 다시 테스트를 위해서 Session2 에서 ROLLBACK을 진행 mysql> rollback; # session 2 에서 다시 테스트 진행 mysql> set session autocommit=off; -- 이번에는 결합 인덱스 컬럼 중 후행 컬럼을 조건절로 사용하여 UPDATE 를 수행 mysql> update tb_test_user_info set hire_date=DATE_FORMAT(now(), '%Y-%m-%d') where last_name='Stavenow'; <--!! 락으로 대기 <-- 결합 인덱스의 선행 커럼이 누락이 되어서 인덱스를 사용할 수 없기 때문에 <-- full scan 으로 되고 full scan 시 session 1 에서 잠금을 하고 있기 때문에 대기를 하게 됨
결합 인덱스에서 선행 컬럼이 제외 되었기 때문에 Full scan 으로 수행이 되게 되고 Session 1 의 트랜잭션이 아직 활성화 이기 때문에 세션 2는 잠금으로 대기 하게 됩니다
갭락은 그 자체보다 이어서 설명하게 될 넥스트 키 락의 일부로 자주 표현/사용 됩니다
넥스트 키 락
레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락(Next Key Lock) 이라고 합니다.
InnoDB 는 검색을 하거나 테이블 인덱스를 스캔할 때, 인덱스에 해당하는 레코드(이하, 인덱스 레코드)들에 shared lock 이나 exclusive lock 을 획득하는 방식으로 행-레벨의 잠금을 수행합니다. 따라서 행-레벨 잠금은 인덱스-레코드 잠금을 의미합니다.
위의 갭 락에서 설명한 내용과 같이 인덱스 레코드의 가장 처음 레코드의 이전, 그리고 마지막 레코드의 이후에 대해서도 gap 에도 잠금을 설정합니다.
테스트 예제
테스트를 위해서 먼저 이전에 생성한 유니크 인덱스 삭제 및 일반 인덱스를 다시 생성하도록 하겠습니다.
-- 유니크 인덱스 삭제 mysql> alter table tb_test_user_info drop index ux_emp_no_last_name; -- 일반 단일 컬럼 인덱스 다시 생성 mysql> alter table tb_test_user_info add index ix_emp_no(emp_no);
그 다음 Session 1 에서 emp_no = 10009 의 last_name 을 변경 하도록 하겠습니다.
## session 1 -- 먼저 SQL 플랜 확인 mysql> explain update tb_test_user_info set last_name='Jade' where emp_no = 10009; +----+-------------+-------------------+-------+---------------+-----------+---------+-------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+-------+---------------+-----------+---------+-------+------+----------+-------------+ | 1 | UPDATE | tb_test_user_info | range | ix_emp_no | ix_emp_no | 5 | const | 1 | 100.00 | Using where | +----+-------------+-------------------+-------+---------------+-----------+---------+-------+------+----------+-------------+ * 인덱스 ix_emp_no 가 정상적으로 사용되고 있음 mysql> set session autocommit=off; mysql> update tb_test_user_info set last_name='Jade' where emp_no = 10009; mysql> select LOCK_MODE,LOCK_DATA from performance_schema.data_locks; +---------------+-----------+ | LOCK_MODE | LOCK_DATA | +---------------+-----------+ | IX | NULL | | X | 10009, 9 | | X,REC_NOT_GAP | 9 | | X,GAP | 10010, 10 | <-- 10010 에 GAP LOCK 설정됨 +---------------+-----------+
그 다음 emp_no=10010 에 대해서 UPDATE,DELETE ,INSERT 순으로 진행해 보겠습니다.
## Session 2 mysql> set session autocommit=off; mysql> update tb_test_user_info set last_name='A' where emp_no=10010; <-- UPDATE 가능 mysql> delete from tb_test_user_info where emp_no=10010; <-- DELETE 가능 -- 데이터 삭제 여부 확인 mysql> select * from tb_test_user_info where emp_no=10010; Empty set (0.00 sec) -- 다음 테스트를 위해 COMMIT 으로 트랜잭션 종료 mysql> commit; -- DELETE 된 emp_no=10010 에 INSERT 시도 mysql> insert into tb_test_user_info values(10,10010,'Mary','Piveteau','1990-01-22'); <-- lock으로 대기
UPDATE 및 DELETE 는 락의 간섭 없이 진행되며, INSERT 의 경우 LOCK 으로 대기 하게 됩니다.
이제 emp_no=10009 보다 작은 10008 에 대해서도 UPDATE,DELETE ,INSERT 순으로 진행해 보겠습니다.
mysql> set session autocommit=off; mysql> update tb_test_user_info set last_name='A' where emp_no=10008; <-- UPDATE 가능 mysql> delete from tb_test_user_info where emp_no=10008; <-- DELETE 가능 -- 데이터 삭제 여부 확인 mysql> select * from tb_test_user_info where emp_no=10008; Empty set (0.00 sec) -- 다음 테스트를 위해 COMMIT 으로 트랜잭션 종료 mysql> commit; mysql> insert into tb_test_user_info values(10,10008,'Saniya','Kalloufi','1985-02-18'); <-- lock으로 대기
emp_no=10008 도 Insert 에서 잠금으로 대기하는 것을 확인 할 수 있습니다.
performance_schema.data_locks 를 조회해보면 아래와 같이 갭 락 간섭에 의해 대기하는 것을 확인할 수 있습니다.
mysql> select LOCK_MODE,THREAD_ID,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; +------------------------+-----------+-------------+-----------+ | LOCK_MODE | THREAD_ID | LOCK_STATUS | LOCK_DATA | +------------------------+-----------+-------------+-----------+ | IX | 48 | GRANTED | NULL | | X | 48 | GRANTED | 10009, 9 | | X,REC_NOT_GAP | 48 | GRANTED | 9 | | X,GAP | 48 | GRANTED | 10011, 11 | | IX | 49 | GRANTED | NULL | | X,GAP,INSERT_INTENTION | 49 | WAITING | 10009, 9 | <!!-- +------------------------+-----------+-------------+-----------+
Next Key Lock 은 사용하는 isolation level 과 관계 있으며, 그외 innodb_locks_unsafe_for_binlog 시스템 변수와도 연괸되어 있습니다. innodb_locks_unsafe_for_binlog 이 비활성화(=0) 으로 되어있으면 변경을 위해 검색하는 레코드에 넥스트 키 락 방식으로 잠금이 발생 합니다.
innodb_locks_unsafe_for_binlog 의 Default 값은 0(OFF) 이며 의미는 위에서 설명한 것과 같이 Next Key Lock 을 활성화 해서 사용하겠다 라는 의미 입니다.
해당 시스템변수는 MySQL 버전 5.6.3 에서 Deprecated 가 되었으며, 최종적으로 MySQL 버전 8.0 버전에서 시스템 변수는 Removed(제거) 되었으며 isolation level - READ COMMITTED 이 이와 유사한 기능을 제공합니다.
즉 Isolation Level 을 READ COMMITTED 으로 변경하면 Next Key Lock 을 사용하지 않는다는 의미 입니다.
이번 포스팅은 여기서 마무리하며, 다음 포스팅에서는 MySQL 격리 수준에 대해서 확인 해보도록 하겠습니다.
이어지는 다음 글
Reference
Reference Book
• Real MySQL 8.0
Reference URL
• mysql.com/innodb-locking
• mysql.com/news-8-0-0
• mysql.com/innodb_locks_unsafe_for_binlog
관련된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
