在MySQL中使用SELECT ... FOR UPDATE进行死锁 [英] Deadlock using SELECT ... FOR UPDATE in MySQL
问题描述
假设我有桌子:
CREATE TABLE t (id INTEGER AUTOINCREMENT NOT NULL, desc TEXT NOT NULL)
我用1个元素填充表格:
I populate the table with 1 element:
INSERT INTO TABLE t VALUES (1, 'Hello')
然后我在MySQL中运行两个事务.在t1
中,我运行:
And I run two transactions in MySQL. In t1
I run:
START TRANSACTION;
SELECT * FROM t WHERE id = 1 FOR UPDATE;
在t2
中,我运行:
START TRANSACTION;
SELECT * FROM t WHERE id = 1 FOR UPDATE;
在这一点上,我希望t1
在该行上持有e(X)排他锁,并且t2
等待直到它可以获得X锁(并且t2
的确被阻塞,到目前为止非常好) ).然后,我在t1
中运行更新(没有任何WHERE子句!):
At this point I expect t1
to hold an e(X)clusive lock on the row, and t2
to wait until it can get an X lock (and t2
gets indeed blocked, so far so good). I then run an update in t1
(without any WHERE clause!):
UPDATE t SET desc = 'Hello from t1';
此时,在t2
中,我立即获得了错误(无需进行COMMIT
交易):
At this point in t2
I get immediately (no need to COMMIT
the transaction) the error:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
为什么会出现此错误?我猜想有一个锁t2
正在获取需要进行完整的UPDATE的操作,从而产生了死锁,但是我不明白t2
如何获取锁,因为它应该等待t1
完成
Why am I getting this error? I guess there is a lock that t2
is obtaining that the full UPDATE needs to proceed, making a deadlock, but I don't understand how can t2
obtain a lock given that it should be waiting for t1
to finish.
推荐答案
什么有效,什么无效
What works and what does not
A way to make both transactions run through without a deadlock is to change the isolation level to READ COMMITED (or READ UNCOMMITED) in both connections:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
(在start transaction
之前).
很可能在t2
中进行设置就足够了,但只是为了确保示例,在这两者中都进行设置即可.
Likely it would be enough to set it in t2
, but just to be sure for the example, set it in both.
更改事务的隔离级别确实会带来一些副作用,这些副作用应该告知手册中的内容,然后在生产环境中进行更改.
Changing the isolation level of transactions does introduce some side-effects, which one should inform about in the manual before changing this in a production environment.
------------------------
LATEST DETECTED DEADLOCK
------------------------
140424 8:45:46
*** (1) TRANSACTION:
TRANSACTION B6F18A3, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 13885, OS thread handle 0x7f8b1dbd2700, query id 901012
localhost root statistics
SELECT * FROM t WHERE id = 1 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 22921 n bits 72 index `PRIMARY` of table
`test`.`t` trx id B6F18A3 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000b6f1883; asc o ;;
2: len 7; hex 06000059a211ea; asc Y ;;
3: len 5; hex 48656c6c6f; asc Hello;;
*** (2) TRANSACTION:
TRANSACTION B6F18A2, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 13888, OS thread handle 0x7f8b1f64d700, query id 901068
localhost root Updating
UPDATE t SET `descc` = 'Hello from t1'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 22921 n bits 72 index `PRIMARY` of table
`test`.`t` trx id B6F18A2 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000b6f1883; asc o ;;
2: len 7; hex 06000059a211ea; asc Y ;;
3: len 5; hex 48656c6c6f; asc Hello;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 22921 n bits 72 index `PRIMARY` of table
`test`.`t` trx id B6F18A2 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000b6f1883; asc o ;;
2: len 7; hex 06000059a211ea; asc Y ;;
3: len 5; hex 48656c6c6f; asc Hello;;
*** WE ROLL BACK TRANSACTION (1)
说明
正如提到的a_horse_with_no_name一样,这似乎是MySQL中的错误.事务(2)保持并等待相同的锁,这没有任何意义.
Explanation
As a_horse_with_no_name mentioned, this seems like a bug in MySQL. Transaction (2) holds and waits for the same lock, which makes no sense.
这篇关于在MySQL中使用SELECT ... FOR UPDATE进行死锁的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!