嵌入select查询的mysql触发器中的sql死锁

3qpi33ja  于 2021-06-16  发布在  Mysql
关注(0)|答案(1)|浏览(355)

我在工作上有问题 BEFORE INSERT TRIGGER 在同时插入2个或更多服务器种子时会导致死锁。
我想摆脱死锁（如果可能的话），所以我不需要在应用程序级别处理它们。服务器种子使用表将有数百万条甚至数十亿条记录。插入件必须性能良好 uniqueIndex(serverSeedId, nonce) 是必需的，所以单个服务器种子永远不会有多个服务器种子使用同一个 nonce .
与此问题相关的部分db架构：

CREATE TABLE `server_seed` (
    `id` INT NOT NULL AUTO_INCREMENT
    ,`seed` CHAR(64) COLLATE \ "utf8_general_ci\" NOT NULL
    ,`hash` CHAR(64) COLLATE \ "utf8_general_ci\" NOT NULL
    ,`userId` INT NULL
    ,UNIQUE INDEX `IDX_caf256dbf86619a07597158267`(`seed`)
    ,UNIQUE INDEX `IDX_ea5ad02726433cb3e6969e65e3`(`hash`)
    ,PRIMARY KEY (`id`)
    ) ENGINE = InnoDB;

CREATE TABLE `server_seed_use` (
    `id` INT NOT NULL AUTO_INCREMENT
    ,`clientSeed` VARCHAR(64) NOT NULL
    ,`nonce` INT NULL DEFAULT NULL
    ,`serverSeedId` INT NULL
    ,INDEX `IDX_3b9f114a6190aae9cf7b0aeaab`(`nonce`)
    ,UNIQUE INDEX `IDX_2b872fdbfb8da47ca2744c53a8`(`serverSeedId`, `nonce`)
    ,PRIMARY KEY (`id`)
    ) ENGINE = InnoDB;

触发器定义：

CREATE TRIGGER test_trigger
    BEFORE INSERT ON server_seed_use
    FOR EACH ROW BEGIN
        DECLARE nextNonce INT;

        SET nextNonce = (SELECT MAX(nonce) FROM server_seed_use WHERE serverSeedId=NEW.serverSeedId); <--- I am convinced this is causing deadlocks
        IF (nextNonce IS NULL) THEN
            SET nextNonce = -1;
        END IF;

        SET NEW.nonce=nextNonce + 1;
    END

它应该像这样更新nonce（insert时设置）：

user_1(id: 1)
    server_seed_1(id: 1, owner: 1)
        server_seed_use_1(id: 1, serverSeedId: 1, nonce: 0)
        server_seed_use_2(id: 2, serverSeedId: 1, nonce: 1)
        server_seed_use_5(id: 5, serverSeedId: 1, nonce: 2)
    server_seed_2(id: 2, owner: 1)
        server_seed_use_3(id: 3, serverSeedId: 2, nonce: 0)
        server_seed_use_4(id: 4, serverSeedId: 2, nonce: 1)
user_2(id: 2)
    server_seed_3(id: 3, owner: 2)
        server_seed_use_6(id: 6, serverSeedId: 3, nonce: 0)
        server_seed_use_7(id: 7, serverSeedId: 3, nonce: 1)

触发死锁的查询如下所示：

INSERT INTO `server_seed_use`(`id`, `clientSeed`, `nonce`, `serverSeedId`) VALUES (DEFAULT, "clientSeed", DEFAULT, 1)

如果只执行一次或在上一个查询完成后执行，则工作正常，但一旦并发执行2次或更多次，就会发生死锁。
我为select查询测试了不同的锁读机制( SELECT MAX(nonce) FROM server_seed_use WHERE serverSeedId=NEW.serverSeedId ):
没有或 LOCK IN SHARE MODE :
ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction. FOR UPDATE : ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG: Can't update table 'server_seed_use' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 的输出 SHOW ENGINE INNODB STATUS 死锁发生后：

| InnoDB |      |
=====================================
2019-01-04 15:01:26 0x7fa1a642d700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1733 srv_active, 0 srv_shutdown, 67024 srv_idle
srv_master_thread log flush and writes: 68757
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 130200
OS WAIT ARRAY INFO: signal count 81336
RW-shared spins 0, rounds 203699, OS waits 112137
RW-excl spins 0, rounds 145754, OS waits 1538
RW-sx spins 11622, rounds 72192, OS waits 223
Spin rounds per wait: 203699.00 RW-shared, 145754.00 RW-excl, 6.21 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2019-01-04 02:23:41 0x7fa1bf907700 Transaction:
TRANSACTION 1909787, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 608, OS thread handle 140332680378112, query id 36689 172.17.0.10 test
INSERT INTO `server_seed_use`(`id`, `clientSeed`, `nonce`, `serverSeedId`) VALUES (DEFAULT, 'aaa', DEFAULT, 1)
Foreign key constraint fails for table `test`.`server_seed_use`:
,
CONSTRAINT `FK_317cca594917646e8d2667d8317` FOREIGN KEY (`serverSeedId`) REFERENCES `server_seed` (`id`)
Trying to add in child table, in index IDX_2b872fdbfb8da47ca2744c53a8 tuple:
DATA TUPLE: 3 fields;
0: len 4; hex 80000000; asc     ;;
1: SQL NULL;
2: len 4; hex 80000001; asc     ;;

But in parent table `test`.`server_seed`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000001d2419; asc \$ ;;
2: len 7; hex bf0000019c0110; asc ;;
3: len 30; hex 326431623961656431633066316431396630383030363032353037646261; asc 2d1b9aed1c0f1d19f0800602507dba; (total 64 bytes);
4: len 30; hex 376635626535643763393761373036613635663331316531643561333334; asc 7f5be5d7c97a706a65f311e1d5a334; (total 64 bytes);
5: len 4; hex 80000001; asc ;;

---

## LATEST DETECTED DEADLOCK

2019-01-04 14:45:33 0x7fa1a67fc700

**_ (1) TRANSACTION:

TRANSACTION 1980933, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 1114, OS thread handle 140332419888896, query id 153107 172.17.0.10 test
INSERT INTO `server_seed_use`(`id`, `clientSeed`, `nonce`, `serverSeedId`) VALUES (DEFAULT, "clientSeed", DEFAULT, 1)
_**(1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21479 page no 4 n bits 72 index IDX_2b872fdbfb8da47ca2744c53a8 of table `test`.`server_seed_use` trx id 1980933 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

**_ (2) TRANSACTION:

TRANSACTION 1980930, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
7 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 1113, OS thread handle 140332259854080, query id 153101 172.17.0.10 test
INSERT INTO `server_seed_use`(`id`, `clientSeed`, `nonce`, `serverSeedId`) VALUES (DEFAULT, "clientSeed", DEFAULT, 1)
_**(2) HOLDS THE LOCK(S):
RECORD LOCKS space id 21479 page no 4 n bits 72 index IDX_2b872fdbfb8da47ca2744c53a8 of table `test`.`server_seed_use` trx id 1980930 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000000; asc ;;
2: len 4; hex 80000001; asc ;;

\*\*\* (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21479 page no 4 n bits 72 index IDX_2b872fdbfb8da47ca2744c53a8 of table `test`.`server_seed_use` trx id 1980930 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

## \*\*\* WE ROLL BACK TRANSACTION (1)

## TRANSACTIONS

Trx id counter 1980956
Purge done for trx's n:o < 1980956 undo n:o < 0 state: running but idle
History list length 479
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421808177219408, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

---

## FILE I/O

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
3815 OS file reads, 418691 OS file writes, 261409 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

---

## INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

---

## LOG

Log sequence number 6612985217
Log flushed up to 6612985217
Pages flushed up to 6612985217
Last checkpoint at 6612985208
0 pending log flushes, 0 pending chkp writes
165104 log i/o's done, 0.00 log i/o's/second

---

## BUFFER POOL AND MEMORY

Total large memory allocated 137428992
Dictionary memory allocated 855236
Buffer pool size 8192
Free buffers 1024
Database pages 7159
Old database pages 2622
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 17329, not young 362652
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3754, created 155998, written 162468
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7159, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

---

## ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140332486358784, state: sleeping
Number of rows inserted 5343638, updated 573, deleted 0, read 5746447
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

---

# END OF INNODB MONITOR OUTPUT

@edit1:我无法在评论中回答@krokodilko，所以我在这里写了它。
我宁愿避免2+3解决方案，因为它会产生更多的问题。
选项1+4看起来很有趣。我已经补充了 ER_LOCK_DEADLOCK 签入app+重试逻辑的“关键”部分。所以只要加1，我的问题就得到了部分解决。
然而，有很多 ER_LOCK_DEADLOCK 这意味着必须有很多的重试（我想不是真的执行）。如果无法完全消除死锁，我想至少尝试减少死锁的数量。
至于第四个问题，我想你的意思是

SELECT id INTO some_variable FROM server_seed_use WHERE serverSeedId = NEW.serverSeedId FOR UPDATE;

，因为我正在插入 server_seed_use 而不是 server_seed . 如果我错了，请纠正我。
我不是mysqlMaven，但我认为这个锁是不够的。它将保护所有现有的 server_seed_use 有特定的 serverSeedId 但不能防止插入新的 server_seed_use 与 serverSeedId . 有道理吗？如果是，是否可以锁定所有插入件或基于 serverSeedId ，所以只能有一个 server_seed_use 特定的 serverSeedId 当时正在插入。

sqlmysqltriggersrdbmsdatabase-deadlocks

1条答案

按热度按时间
vs3odd8k

vs3odd8k1#

这不是问题的答案（或者-这只是部分答案）。
只是下面的文字太长了，不适合在评论，所以我给它作为一个问题的答案
哪个orm？冬眠，mybatis，toplink？
不管怎么说，这种死锁很可能是由于mysql处理innodb索引间隙锁的方式造成的，尽管必须调试代码才能100%确定（必须花费更多的时间进行实验）。
您有几个选择：
捕获死锁异常，回滚并重复整个事务。
将隔离级别从repeatable read（默认）更改为read committed。
为\u binlog系统变量启用innodb \u locks \u unsafe \u（不建议使用，因为它现在已被弃用-请参阅文档）
使用一个额外的select for update来锁定一些记录，以便序列化所有事务（最好是 SELECT * FROM server_seed WHERE id = NEW.serverSeedId FOR UPDATE ).
选项2+3可能会修复死锁问题，但很可能它们不会令人满意，因为您将得到“复制键错误”，而不是-这是由于触发器逻辑中的缺陷（我现在就离开它，因为它需要更长的解释）。
1+4选项仍然是最好的。
选项1很明显-必须在代码的某个地方实现。
选项4-您可以尝试添加 SELECT id INTO some_variable FROM server_seed WHERE id = NEW.serverSeedId FOR UPDATE 作为触发器中的第一条指令（假定此表中有一个具有此id的记录） server_seed ). 或者，尝试在事务开始时添加它-我只是不知道如何在orm中执行它。

赞(0）分享  回复(0）举报  2021-06-16
我来回答

相关问题

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com