mariadb 了解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 = 6 bca 9464-f75 d-4141-b21 e-0a 0 b 9a 7 d 06 ca）吗？
根据我的理解和阅读，第一个事务试图获取S锁（用于读取），第二个事务持有X锁（排他锁）用于插入。但第二个事务等待的是第一个事务持有的锁是什么呢？在我看来，第二个事务似乎在等待它自己持有的锁-这是错误的吗？
另外，这两个事务是否可能正在等待一个不是由它们持有的锁？
有间隙和无间隙部分意味着什么？
解决此类问题的首选方法是什么？
编辑：这是纲要

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
;

然后是两次迁移
第一个

争用发生在pt-table-checksum运行的REPLACE和应用程序运行的INSERT之间。
您询问了有关锁定SELECT的问题。一个普通的SELECT在默认情况下是无锁的，但是如果SELECT是锁定语句的一部分，它就隐式地变成了锁定SELECT。也就是说，因为pt-table-checksum运行REPLACE INTO checksums ... SELECT FROM bars，所以它必须在bars中的某些行上获得S锁。
这同样适用于将数据写入行或变量的语句中使用的任何SELECT语句。

创建表...选择...
插入到...选择...
SET @变量=（SELECT...）
SELECT... INTO @变量
在UPDATE或DELETE语句的子查询中使用SELECT
在触发器中使用SELECT

将应用的事务隔离级别更改为READ-COMMITTED可以帮助避免间隙锁。但也许pt-table-checksum有自己的事务隔离级别，无论如何都会创建间隙锁。
底线是死锁是会发生的。你不能完全消除它们，当锁定是由并发会话完成时，它们是一个自然的结果。所以你应该设计代码来捕捉异常，并根据需要重试。
回复您的评论：
事务1不一定持有另一个锁。以下操作序列可能会导致死锁：
1.事务2（您的应用）在bars中的单个行上获取一些锁。
1.事务处理1（pt-table-checksum）需要锁定一批多行。事务处理2至少锁定了一行，因此pt-table-checksum处于等待状态。
1.事务2希望锁定它以前没有锁定的另一行，但该行是pt-table-checksum试图锁定的批处理的一部分。

mariadb 了解InnoDB死锁日志

1条答案

相关问题

热门标签

最新问答