트랜젝션과 잠금 실습하며 로그 확인해보기
앞서 정리한 개념 내용을 실제 DB를 띄워서 RealMySQL 8.0 책의 실습 내용을 더해보기로 했다.
레코드 잠금 실습
- 상황
- 커넥션 두개를 시작해두고, 커넥션 1이 트랜젝션에서 update 후에 commit 하지 않고 대기한다.
- 커넥션 2는 이를 모른채로 같은 행을 수정하려고 한다.
-
커넥션 1
mysql> use employees; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select count(*) from employees where emp_no=100001; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update employees set birth_date=now() where emp_no=100001; Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 1 -
커넥션 2
mysql> use employees Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update employees set hire_date=now() where emp_no=100001; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction # 너무 오래 기다리고 있으면 위와 같이 뜬다 -
다른 커넥션에서 확인해보면 하나는 커밋을 하지 않고있고, 하나는 기다리는 중이다
mysql> show processlist; +----+-----------------+-----------+-----------+---------+------+------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+-----------+---------+------+------------------------+----------------------------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 1717 | Waiting on empty queue | NULL | | 13 | root | localhost | employees | Sleep | 82 | | NULL | | 14 | root | localhost | employees | Query | 41 | updating | update employees set hire_date=now() where emp_no=100001 | | 16 | root | localhost | employees | Query | 0 | init | show processlist | +----+-----------------+-----------+-----------+---------+------+------------------------+----------------------------------------------------------+ 4 rows in set, 1 warning (0.00 sec) -
performance_schema.data_locks 으로 어떤 스레드가 어떤 잠금을 가지고 있는지 찾아볼 수 있다.
mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 281473216299008:1084:281473097722960 ENGINE_TRANSACTION_ID: 2240 THREAD_ID: 53 EVENT_ID: 42 OBJECT_SCHEMA: employees OBJECT_NAME: employees PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 281473097722960 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 281473216299008:18:746:25:281473097720048 ENGINE_TRANSACTION_ID: 2240 THREAD_ID: 53 EVENT_ID: 42 OBJECT_SCHEMA: employees OBJECT_NAME: employees PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 281473097720048 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 100001 2 rows in set (0.01 sec) # 커밋하고 나면 아무것도 뜨지 않음 mysql> select * from performance_schema.data_locks\G Empty set (0.00 sec)- X,REC_NOT_GAP: 레코드락
- X,GAP: 갭락
- X: 넥스트키락
- LOCK_TYPE이 RECORD라는 것은 테이블 수준의 잠금이 아닌 레코드 수준의 잠금을 의미
넥스트 키 락
-
범위로 update 를 했을 때 data_locks은 어떻게 되는지 궁금해서 실행해봤다.
mysql> update employees set hire_date=now() where emp_no >=499997; Query OK, 0 rows affected, 3 warnings (0.01 sec) Rows matched: 3 Changed: 0 Warnings: 3 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 281473216299008:1084:281473097722960 ENGINE_TRANSACTION_ID: 2245 THREAD_ID: 53 EVENT_ID: 61 OBJECT_SCHEMA: employees OBJECT_NAME: employees PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 281473097722960 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 281473216299008:18:2325:285:281473097720048 ENGINE_TRANSACTION_ID: 2245 THREAD_ID: 53 EVENT_ID: 61 OBJECT_SCHEMA: employees OBJECT_NAME: employees PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 281473097720048 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 499997 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 281473216299008:18:2325:1:281473097720392 ENGINE_TRANSACTION_ID: 2245 THREAD_ID: 53 EVENT_ID: 61 OBJECT_SCHEMA: employees OBJECT_NAME: employees PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 281473097720392 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: supremum pseudo-record *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 281473216299008:18:2325:286:281473097720392 ENGINE_TRANSACTION_ID: 2245 THREAD_ID: 53 EVENT_ID: 61 OBJECT_SCHEMA: employees OBJECT_NAME: employees PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 281473097720392 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 499998 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 281473216299008:18:2325:287:281473097720392 ENGINE_TRANSACTION_ID: 2245 THREAD_ID: 53 EVENT_ID: 61 OBJECT_SCHEMA: employees OBJECT_NAME: employees PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 281473097720392 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 499999 5 rows in set (0.00 sec)
더 보면 좋은 것 들
https://medium.com/daangn/mysql-gap-lock-다시보기-7f47ea3f68bc
https://medium.com/daangn/mysql-gap-lock-두번째-이야기-49727c005084
Leave a comment