역시 해치웠나를 외치면 안 되는 것인가... 또 울기 시작한 페페... (그만 울어잇!)
의미있는 경험으로 남기기위해 기록해보자.
CREATE TABLE parent
(
id bigint not null primary key,
name varchar(255) null,
updated_at datetime(6) null
);
CREATE TABLE child
(
id bigint not null primary key,
name varchar(255) null,
parent_id bigint null,
CONSTRAINT parent_id_unique UNIQUE (parent_id)
);
CREATE TABLE child_index
(
id bigint not null primary key,
name varchar(255) null,
parent_id bigint null,
);
CREATE INDEX parent_id ON child_index (parent_id);
INSERT INTO parent VALUES (1, 'parent_1', NOW());
TX1 | TX2 | lock |
---|---|---|
BEGIN ; DELETE FROM child_index WHERE parent_id = 2; | (1) child Gap Lock | |
BEGIN ; DELETE FROM child_index WHERE parent_id = 2; | (2) child Gap Lock | |
INSERT INTO child_index VALUES ('1', 'name2', 2); | (3) child X,INSERT_INTENTION Lock 대기 | |
INSERT INTO child_index VALUES ('2', 'name2', 2); | (4) child X,INSERT_INTENTION Lock 대기 | |
Deadlock found when trying to get lock; try restarting transaction | (4) 해소를 위해서 (1) 해소 필요 -> (1) 해소 위해서 TX1 커밋 필요 -> TX1 커밋하려면 (2) 해소 필요 -> 데드락 발생 |
SELECT * from parent WHERE id = 1;
INSERT INTO child VALUES (1, 'child_1', 1);
DELETE FROM child_index WHERE parent_id = 1;
INSERT INTO child_index VALUES (1, 'child_index_1', 1);
UPDATE parent SET updated_at = NOW() WHERE id = 1;
@Transactional
public void createChildAndChildIndex (long parentId) {
var parent = parentRepository.findById(parentId);
childIndexRepository.deleteByParent(parent);
childIndexRepository.save(new Child_Index('child_index_1', parent));
}
@Transactional
public void createParent (long parentId) {
var parent = parentRepository.findById(parentId);
if(childIndexRepository.findByParent(parent).isPresent()) {
childIndexRepository.deleteByParent(parent);
}
childIndexRepository.save(new Child_Index('child_index_1', parent));
}
CREATE TABLE child_index
(
id bigint not null primary key,
name varchar(255) null,
parent_id bigint null,
CONSTRAINT parent_id_unique UNIQUE (parent_id)
);
CREATE INDEX parent_id ON child_index (parent_id);
BEGIN ;
DELETE FROM child_index WHERE parent_id = 1;
INSERT INTO child_index VALUES (1, 'child_index_1', 1);
COMMIT ;
BEGIN ;
DELETE FROM child_index WHERE parent_id = 1;
INSERT INTO child_index VALUES (1, 'child_index_1', 1);
COMMIT ;
BEGIN ;
DELETE FROM child_index WHERE name = 'child_index_2';
INSERT INTO child_index VALUES (1, 'child_index_1', 1);
COMMIT ;
SHOW ENGINE innodb STATUS;
** (1) TRANSACTION:TRANSACTION 13034, ACTIVE 6 sec insertingmysql tables in use 1, locked 1LOCK WAIT 4 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1MySQL thread id 280, OS thread handle 6136639488, query id 21716 localhost 127.0.0.1 root update/ ApplicationName=DataGrip 2022.3.2 */ insert into child values ('2', 'name2', 2)
*** (1) HOLDS THE LOCK(S):RECORD LOCKS space id 154 page no 4 n bits 72 index PRIMARY of table jpa.child trx id 13034 lock_mode X locks rec but not gapRecord lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 154 page no 5 n bits 72 index parent_id of table jpa.child trx id 13034 lock_mode X insert intention waitingRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
** (2) TRANSACTION:TRANSACTION 13035, ACTIVE 4 sec insertingmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)MySQL thread id 281, OS thread handle 6135525376, query id 21726 localhost 127.0.0.1 root update/ ApplicationName=DataGrip 2022.3.2 */ insert into child values ('2', 'name2', 2)
*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 154 page no 5 n bits 72 index parent_id of table jpa.child trx id 13035 lock_mode XRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 154 page no 4 n bits 72 index PRIMARY of table jpa.child trx id 13035 lock mode S locks rec but not gap waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
SELECT * FROM performance_schema.data_locks;
INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
---|---|---|---|---|---|
null | 4813003272 | TABLE | IX | GRANTED | null |
parent_id | 4823656472 | RECORD | X | GRANTED | supremum pseudo-record |
parent_id | 4823656816 | RECORD | X,INSERT_INTENTION | GRANTED | supremum pseudo-record |
parent_id | 4823657160 | RECORD | X,GAP | GRANTED | 1, 1 |