在MySQL中使用SELECT ... FOR UPDATE进行死锁 [英] Deadlock using SELECT ... FOR UPDATE in MySQL

查看:1598
本文介绍了在MySQL中使用SELECT ... FOR UPDATE进行死锁的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

假设我有桌子:

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆