InnoDB 교착 로그 이해

InnoDB 교착 로그 이해

번 가 있습니다.INSERT이와다른 가 실행하고 있는 검사를 할 수 . 이 은 복제 하기 위해 복제 체크섬을 발행합니다.SELECT데이터베이스에 대한 쿼리입니다.

문제는 앱 수준의 트랜잭션이 다음 검사를 수행하는 도구의 트랜잭션과 교착되는 경우가 있다는 것입니다.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-05-19 18:25:20 0x7f6eb63e3700
*** (1) TRANSACTION:
TRANSACTION 421588457956552, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 1012 lock struct(s), heap size 123088, 85725 row lock(s)
MySQL thread id 15333390, OS thread handle 140086616594176, query id 2884722462 10.96.7.108 replication_checksum_user Sending data
REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'bar', 'bars', '17', 'foo+idx', '688438b1-63b7-4cdd-ba14-5ac2811fce08,688438b1-63b7-4cdd-ba14-5ac2811fce08,6149061644177471', '6f2aecfe-44b8-4b04-8913-5086c5402c02,6f2aecfe-44b8-4b04-8913-5086c5402c02,4356115808993199', COUNT(*) AS cnt, COALESCE(LOWER(CONCAT(LPAD(CONV(BIT_XOR(CAST(CONV(SUBSTRING(CRC32(CONCAT_WS('#', convert(`field1` using utf8mb4), convert(`field2` using utf8mb4), convert(`field3` using utf8mb4), convert(`field4` using utf8mb4), `field5`, `field6`, `created_time`, `finished_time`, CONCAT(ISNULL(`notification_id`), ISNULL(`attempt_type`), ISNULL(`successful`), ISNULL(`finished_time`)))), 1, 16), 16, 10) AS UNSIGNED)), 10, 16), 16, '0')))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 109 page no 16087 n bits 192 index foo_idx of table `bar`.`bars` trx id 421588457956552 lock mode S waiting
Record lock, heap no 120 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 30; hex 36626361393436342d663735642d343134312d623231652d306130623961; asc 6bca9464-f75d-4141-b21e-0a0b9a; (total 36 bytes);
 1: len 16; hex 34373236323438353934373634393231; asc 4726248594764921;;
 2: len 6; hex 000028626bdf; asc   (bk ;;
 3: len 7; hex ba0004c0290d2e; asc     ) .;;
 4: SQL NULL;
 5: SQL NULL;
 6: SQL NULL;
 7: len 8; hex 8000000000000a07; asc         ;;
 8: len 5; hex 99ace72654; asc    &T;;
 9: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 677538783, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
MySQL thread id 15333007, OS thread handle 140113480660736, query id 2884722486 10.96.7.206 app_user Update
INSERT into bars(field1, field2, field3, created_time) VALUES('6bca9464-f75d-4141-b21e-0a0b9a7d06ca', '1656221208545861', 272.97000, CURRENT_TIMESTAMP())
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 109 page no 16087 n bits 192 index foo_idx of table `bar`.`bars` trx id 677538783 lock_mode X locks rec but not gap
Record lock, heap no 120 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 30; hex 36626361393436342d663735642d343134312d623231652d306130623961; asc 6bca9464-f75d-4141-b21e-0a0b9a; (total 36 bytes);
 1: len 16; hex 34373236323438353934373634393231; asc 4726248594764921;;
 2: len 6; hex 000028626bdf; asc   (bk ;;
 3: len 7; hex ba0004c0290d2e; asc     ) .;;
 4: SQL NULL;
 5: SQL NULL;
 6: SQL NULL;
 7: len 8; hex 8000000000000a07; asc         ;;
 8: len 5; hex 99ace72654; asc    &T;;
 9: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 109 page no 16087 n bits 192 index foo_idx of table `bar`.`bars` trx id 677538783 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 120 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 30; hex 36626361393436342d663735642d343134312d623231652d306130623961; asc 6bca9464-f75d-4141-b21e-0a0b9a; (total 36 bytes);
 1: len 16; hex 34373236323438353934373634393231; asc 4726248594764921;;
 2: len 6; hex 000028626bdf; asc   (bk ;;
 3: len 7; hex ba0004c0290d2e; asc     ) .;;
 4: SQL NULL;
 5: SQL NULL;
 6: SQL NULL;
 7: len 8; hex 8000000000000a07; asc         ;;
 8: len 5; hex 99ace72654; asc    &T;;
 9: SQL NULL;

*** WE ROLL BACK TRANSACTION (2)

나는 그 로그를 이해하는 데 어려움을 겪고 있다.두 트랜잭션이 싸우고 있는 공통 자원은 무엇입니까?삽입되는 특정 행의 인덱스 레코드입니까(ID = 6bca9464-f75d-4141-b21e-0a0b9a7d06ca)?

제가 알고 있는 바로는 첫 번째 트랜잭션은 S 잠금(읽기용)을 취득하려고 하고, 두 번째 트랜잭션은 삽입용 X 잠금(전용 잠금)을 보유하고 있습니다.하지만 두 번째 거래가 첫 번째 거래가 보류되기를 기다리고 있는 것은 무엇일까요?제 생각에는 두 번째 거래 자체가 잠겨있는 자물쇠를 기다리고 있는 것 같습니다만, 잘못된 것입니까?

또, 2개의 거래가, 그들이 보관하고 있지 않은 자물쇠로 대기하고 있는 것은 아닐까요?

갭 부분과 갭이 없는 부분이 무슨 뜻이죠?

이러한 유형의 문제를 해결하기 위해 선호하는 방법은 무엇입니까?

편집: 스키마입니다.

CREATE TABLE `bars` (
    `field1` VARCHAR(50) NOT NULL,
    `field2` VARCHAR(50) NOT NULL,
    `field3` VARCHAR(50) NULL DEFAULT NULL,
    `field4` ENUM('WHOLE','PARTIAL') NOT NULL,
    `field5` TINYINT(4) NULL DEFAULT NULL,
    `field6` DECIMAL(16,2) NOT NULL,
    `created_time` DATETIME NOT NULL,
    `finished_time` DATETIME NULL DEFAULT NULL,
    UNIQUE INDEX `foo_idx` (`field1`, `field2`),
    CONSTRAINT `f1_idx` FOREIGN KEY (`field1`) REFERENCES `bays` (`field1`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

그 후 두 번의 이행을 통해

ALTER TABLE `bars`
ADD FOREIGN KEY `ff_fk` (`field1`, `field2`)
REFERENCES `fields_references` (`field1`, `field2`);

ALTER TABLE bars
DROP CONSTRAINT ff_fk;

pt-table-checksum에서 실행되는 REPLACE와 앱에서 실행되는 INSERT의 경합입니다.

선택해주세요.일반 SELECT는 기본적으로 잠금이 없지만 SELECT가 잠금 문의 일부인 경우 암묵적으로 잠금 SELECT가 됩니다.은 ,, pt-table-checksum을 실행하므로REPLACE INTO checksums ... SELECT FROM bars에서 S bars.

행 또는 변수에 데이터를 쓰는 문에서 사용되는 SELECT도 마찬가지입니다.

• 테이블 작성...선택...
• 삽입처...선택...
• SET @select = (선택...)
• 선택... @variable로
• UPDATE 또는 DELETE 문의 서브쿼리에서 SELECT 사용
• 트리거에서 SELECT 사용

앱의 트랜잭션 분리 수준을 READ-COMMITED로 변경하면 갭 잠금을 방지할 수 있습니다.그러나 pt-table-checksum에는 갭 잠금을 생성하는 자체 트랜잭션 격리 수준이 있을 수 있습니다.

결론은 교착 상태가 일어난다는 것이다.이러한 모든 것을 제거할 수는 없습니다. 동시 세션에 의해 잠금이 수행되면 자연스럽게 발생합니다.따라서 예외를 포착하기 위한 코드를 설계하고 필요에 따라 재시도해야 합니다.

---

코멘트 참조:

트랜잭션 1이 다른 잠금을 유지할 필요는 없습니다.다음과 같은 일련의 작업으로 인해 교착 상태가 발생할 수 있습니다.

1. 트랜잭션 2(앱)는 의 한 줄에 대한 잠금을 획득합니다.bars.

2. 트랜잭션 1(pt-table-checksum)에는 여러 행의 배치에 대한 잠금이 필요합니다.트랜잭션 2에 의해 적어도 하나가 이미 잠겨 있으므로 pt-table-checksum이 대기합니다.

3. 트랜잭션 2는 이전에 잠겨 있지 않았던 다른 행의 잠금을 원하지만 이 행은 pt-table-checksum이 잠그려고 하는 배치의 일부입니다.

언급URL : https://stackoverflow.com/questions/72431047/understanding-innodb-deadlock-log