我在工作上有问题 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
当时正在插入。
1条答案
按热度按时间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中执行它。