TIL
2024.08.02~04 Locks Set by SQL 2
Gisungcu
2024. 8. 4. 17:12
MySQL :: MySQL 8.0 Reference Manual :: 17.7.3 Locks Set by Different SQL Statements in InnoDB
17.7.3 Locks Set by Different SQL Statements in InnoDB A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of an SQL statement. It does not matter whether there are WHERE conditions in
dev.mysql.com
읽은 부분은 insert 시 발생하는 lock에 관한 부분입니다.
Insert 시 gap lock은 없지만 insert intention gap lock이라는 것을 거는데 기존 gap lock과 다른 점은 insert 하는 범위가 다르다면 wait 하지 않는다고 하네요.
insert intention gap lock은 기존 gap lock과 다르다. 또 update, delete, locking read 시의 intention exclude lock과도 다르다.
이외에도 insert 시 duplicate-key 에러가 발생하면 해당 index에 shared lock을 건다고 합니다.
이 lock이 여러 트랜잭션에서 동일하게 가져갔다면 X lock 해제 시 서로 가지고 있는 S lock 때문에 X lock을 얻지 못하고 deadlock이 날 수 있다고 합니다.
duplicate-key가 에러가 발생하는 것인지? 발생하면 바로 rollback이 아닌 건지? wait를 하는 것인지?
실습을 해보면 둘 다 deadlock이 나는 것이 아닌 하나는 X lock을 획득하고 insert에 성공한다.
update, delete의 경우는 서로 대기를 하며 순서를 기다리고 순차적으로 rollback이 되면 순차적으로 쿼리가 실행된다.
s lock 얻으려고 시도를 하지 않기때문으로 보인다.
insert만 연속적으로 같은 row로 실행되었을 때 deadlock이 발생하는 것이다.
음..
B,C 둘 다 S lock은 얻었고 B가 X lock을 얻고자 할 때 dead lock detection되어서 트랜잭션 하나를 죽이는 것이지 않을까.
update, delete처럼 X lock만 요청했다면 wait했겠지만 S lock을 요청했으니 deadlock이 나는것으로 생각된다.
밑의 글의 답변도 비슷한 질문으로 생각된다.
MySQL locking in Duplicate Key Error
From the docs: If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to
stackoverflow.com
실습
1. rollback
| A | B | C | |
| 1 | mysql> insert into tb_gaplock Values (2, 'Matt2', 1) ; Query OK, 1 row affected (0.00 sec) |
||
| 2 | mysql> insert into tb_gaplock Values (2, 'Matt2', 1) ; wait; |
||
| 3 | mysql> insert into tb_gaplock Values (2, 'Matt2', 1) ; wait; |
||
| 4 | rollback; | ||
| 5 | Query OK, 1 row affected (22.69 sec) | ERROR 1213 (40001): Deadlock found when trying to g et lock; try restarting transaction |
|
| 6 |
1번까지 실행 후
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694498816:1068:140505627290272
ENGINE_TRANSACTION_ID: 4894
THREAD_ID: 47
EVENT_ID: 33
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140505627290272
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
1 row in set (0.00 sec)
2번까지 실행 후
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694499624:1068:140505627296256
ENGINE_TRANSACTION_ID: 4899
THREAD_ID: 48
EVENT_ID: 19
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140505627296256
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694499624:2:4:37:140505627293344
ENGINE_TRANSACTION_ID: 4899
THREAD_ID: 48
EVENT_ID: 19
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140505627293344
LOCK_TYPE: RECORD
LOCK_MODE: S,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 2
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694498816:1068:140505627290272
ENGINE_TRANSACTION_ID: 4894
THREAD_ID: 47
EVENT_ID: 33
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140505627290272
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694498816:2:4:37:140505627287360
ENGINE_TRANSACTION_ID: 4894
THREAD_ID: 48
EVENT_ID: 19
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140505627287360
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2
4 rows in set (0.00 sec)
3번까지 실행 후
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694498008:1068:140505627284176
ENGINE_TRANSACTION_ID: 4900
THREAD_ID: 49
EVENT_ID: 21
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140505627284176
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694498008:2:4:37:140505627281184
ENGINE_TRANSACTION_ID: 4900
THREAD_ID: 49
EVENT_ID: 21
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140505627281184
LOCK_TYPE: RECORD
LOCK_MODE: S,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 2
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694499624:1068:140505627296256
ENGINE_TRANSACTION_ID: 4899
THREAD_ID: 48
EVENT_ID: 19
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140505627296256
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694499624:2:4:37:140505627293344
ENGINE_TRANSACTION_ID: 4899
THREAD_ID: 48
EVENT_ID: 19
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140505627293344
LOCK_TYPE: RECORD
LOCK_MODE: S,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 2
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694498816:1068:140505627290272
ENGINE_TRANSACTION_ID: 4894
THREAD_ID: 47
EVENT_ID: 33
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140505627290272
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694498816:2:4:37:140505627287360
ENGINE_TRANSACTION_ID: 4894
THREAD_ID: 48
EVENT_ID: 19
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140505627287360
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2
6 rows in set (0.00 sec)
5번까지 실행 후
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694499624:1068:140505627296256
ENGINE_TRANSACTION_ID: 4906
THREAD_ID: 48
EVENT_ID: 23
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140505627296256
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694499624:2:4:11:140505627293688
ENGINE_TRANSACTION_ID: 4906
THREAD_ID: 47
EVENT_ID: 38
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140505627293688
LOCK_TYPE: RECORD
LOCK_MODE: S,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 3
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694499624:2:4:37:140505627293688
ENGINE_TRANSACTION_ID: 4906
THREAD_ID: 47
EVENT_ID: 38
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140505627293688
LOCK_TYPE: RECORD
LOCK_MODE: S,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140505694499624:2:4:11:140505627294032
ENGINE_TRANSACTION_ID: 4906
THREAD_ID: 48
EVENT_ID: 23
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140505627294032
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP,INSERT_INTENTION
LOCK_STATUS: GRANTED
LOCK_DATA: 3
4 rows in set (0.00 sec)2. commit 시
| A | B | C | |
| 1 | mysql> insert into tb_gaplock Values (2, 'Matt2', 1) ; Query OK, 1 row affected (0.00 sec) |
||
| 2 | mysql> insert into tb_gaplock Values (2, 'Matt2', 1) ; wait; |
||
| 3 | mysql> insert into tb_gaplock Values (2, 'Matt2', 1) ; wait; |
||
| 4 | commit; | ||
| 5 | ERROR 1062 (23000): Duplicate entry '2' for ke y 'tb_gaplock.PRIMARY' |
ERROR 1062 (23000): Duplicate entry '2' for ke y 'tb_gaplock.PRIMARY' |
|
| 6 |
3까지 실행 후
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971959000:1076:139653881582288
ENGINE_TRANSACTION_ID: 4910
THREAD_ID: 59
EVENT_ID: 26
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139653881582288
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971959000:10:4:7:139653881579296
ENGINE_TRANSACTION_ID: 4910
THREAD_ID: 59
EVENT_ID: 26
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139653881579296
LOCK_TYPE: RECORD
LOCK_MODE: S,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 2
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971960616:1076:139653881594368
ENGINE_TRANSACTION_ID: 4909
THREAD_ID: 57
EVENT_ID: 20
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139653881594368
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971960616:10:4:7:139653881591456
ENGINE_TRANSACTION_ID: 4909
THREAD_ID: 57
EVENT_ID: 20
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139653881591456
LOCK_TYPE: RECORD
LOCK_MODE: S,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 2
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971959808:1076:139653881588384
ENGINE_TRANSACTION_ID: 4908
THREAD_ID: 58
EVENT_ID: 26
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139653881588384
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971959808:10:4:7:139653881585472
ENGINE_TRANSACTION_ID: 4908
THREAD_ID: 57
EVENT_ID: 20
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139653881585472
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2
6 rows in set (0.00 sec)
5까지 실행 후
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971959000:1076:139653881582288
ENGINE_TRANSACTION_ID: 4910
THREAD_ID: 59
EVENT_ID: 26
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139653881582288
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971959000:10:4:7:139653881579296
ENGINE_TRANSACTION_ID: 4910
THREAD_ID: 59
EVENT_ID: 26
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139653881579296
LOCK_TYPE: RECORD
LOCK_MODE: S,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971960616:1076:139653881594368
ENGINE_TRANSACTION_ID: 4909
THREAD_ID: 57
EVENT_ID: 20
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139653881594368
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971960616:10:4:7:139653881591456
ENGINE_TRANSACTION_ID: 4909
THREAD_ID: 57
EVENT_ID: 20
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139653881591456
LOCK_TYPE: RECORD
LOCK_MODE: S,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2
4 rows in set (0.00 sec)
3. delete 시
| A | B | C | |
| 1 | mysql> delete from tb_gaplock where id = 1; Query OK, 1 row affected (0.00 sec) |
||
| 2 | mysql> insert into tb_gaplock Values (1, 'Matt2', 1) ; wait; |
||
| 3 | mysql> insert into tb_gaplock Values (1, 'Matt2', 1) ; wait; |
||
| 4 | commit; | ||
| 5 | Query OK, 1 row affected (22.69 sec) | ERROR 1213 (40001): Deadlock found when trying to g et lock; try restarting transaction |
|
| 6 |
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971959000:1076:139653881582288
ENGINE_TRANSACTION_ID: 4926
THREAD_ID: 59
EVENT_ID: 30
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139653881582288
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971959000:10:4:2:139653881579296
ENGINE_TRANSACTION_ID: 4926
THREAD_ID: 59
EVENT_ID: 30
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139653881579296
LOCK_TYPE: RECORD
LOCK_MODE: S,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 1
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971960616:1076:139653881594368
ENGINE_TRANSACTION_ID: 4925
THREAD_ID: 57
EVENT_ID: 28
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139653881594368
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971960616:10:4:2:139653881591456
ENGINE_TRANSACTION_ID: 4925
THREAD_ID: 57
EVENT_ID: 28
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139653881591456
LOCK_TYPE: RECORD
LOCK_MODE: S,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 1
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971959808:1076:139653881588384
ENGINE_TRANSACTION_ID: 4924
THREAD_ID: 58
EVENT_ID: 34
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139653881588384
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971959808:10:4:2:139653881585472
ENGINE_TRANSACTION_ID: 4924
THREAD_ID: 58
EVENT_ID: 34
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139653881585472
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1
6 rows in set (0.00 sec)
4. update만 실행
update의 경우는 S lock을 획득하려 하지 않아서 dead lock이 발생하지 않는다.
| A | B | C | |
| 1 | mysql> update tb_gaplock set name = 'msss' where id = 3; Query OK, 1 row affected (0.00 sec) |
||
| 2 | mysql> update tb_gaplock set name = 'msss' where id = 3; ; wait; |
||
| 3 | mysql> update tb_gaplock set name = 'msss' where id = 3; ; wait; |
||
| 4 | rollback; or commit; | ||
| 5 | Query OK, 1 row affected (22.69 sec) | ||
| 6 | rollback; or commit; | ||
| 7 | Query OK, 1 row affected (22.69 sec) | ||
3번까지 실행
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971959000:1076:139653881582288
ENGINE_TRANSACTION_ID: 4964
THREAD_ID: 59
EVENT_ID: 45
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139653881582288
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971959000:10:4:10:139653881579296
ENGINE_TRANSACTION_ID: 4964
THREAD_ID: 59
EVENT_ID: 45
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139653881579296
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 3
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971960616:1076:139653881594368
ENGINE_TRANSACTION_ID: 4963
THREAD_ID: 57
EVENT_ID: 46
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139653881594368
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971960616:10:4:10:139653881591456
ENGINE_TRANSACTION_ID: 4963
THREAD_ID: 57
EVENT_ID: 46
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139653881591456
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 3
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971959808:1076:139653881588384
ENGINE_TRANSACTION_ID: 4958
THREAD_ID: 58
EVENT_ID: 49
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139653881588384
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139653971959808:10:4:10:139653881585472
ENGINE_TRANSACTION_ID: 4958
THREAD_ID: 58
EVENT_ID: 49
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139653881585472
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 3
6 rows in set (0.00 sec)