MySQL中的行级锁定 [英] Row Level Locking in Mysql

问题描述

我在一个表中有5行(1到5).我希望第2行锁定一些更新，与此同时，如果有人尝试更新第4行，那么他应该能够更新.

我正在下面的代码中尝试此操作，但是我觉得它将锁定置于表级别而不是行级别.

------会话1

START TRANSACTION;
SELECT * FROM test WHERE t=1 FOR UPDATE;
UPDATE test SET NAME='irfandd' WHERE t=2;
COMMIT;

-----会话2(已被阻止)

START TRANSACTION;
UPDATE test SET NAME='irfandd' WHERE t=4;
COMMIT;

解决方案

使用LOCK IN SHARE MODE代替FOR UPDATE. FOR UPDATE防止其他事务也读取该行. LOCK IN SHARE MODE允许读取，但禁止更新.

参考: MySQL手册

------会话1

START TRANSACTION;
SELECT * FROM test WHERE t=1 LOCK IN SHARE MODE;
UPDATE test SET NAME='irfandd' WHERE t=2;
COMMIT;

-----会话2(不再被阻止了:))

START TRANSACTION;
UPDATE test SET NAME='irfandd' WHERE t=4;
COMMIT;

更新:

意识到该表没有索引，我有以下解释:

首先，事务T1锁定SELECT * FROM test WHERE t=1 FOR UPDATE

中的行1

接下来，事务T2尝试执行UPDATE test SET NAME='irfandd' WHERE t=4.为了找出受影响的行，它需要扫描所有行，包括 row 1 .但这已被锁定，因此T2必须等待T1完成. 如果存在任何索引，WHERE t=4可以使用索引来确定第1行是否包含t=4，因此无需等待.

选项1:在test.t上添加索引，以便您的更新可以使用它.

选项2:使用LOCK IN SHARE MODE，它仅用于放置读锁. 不幸的是，此选项会造成死锁.有趣的是，T2事务执行(更新第4行)，而T1失败(更新第2行).似乎T1也会读取锁定第4行，并且由于T2对其进行了修改，因此T1由于事务隔离级别(交易隔离级别，使用READ UNCOMMITTED或READ COMMITTED交易级别.

最简单的方法是选项1 ，恕我直言，但这取决于您的选择.

I have 5 rows in a table (1 to 5). I want row 2 lock for some update and in the meanwhile if someone tries to update row 4, then he should able to update.

I am trying this with code below, but I feel its placing lock on table level rather than row level.

------ session 1

START TRANSACTION;
SELECT * FROM test WHERE t=1 FOR UPDATE;
UPDATE test SET NAME='irfandd' WHERE t=2;
COMMIT;

----- session 2 (which is being blocked)

START TRANSACTION;
UPDATE test SET NAME='irfandd' WHERE t=4;
COMMIT;

解决方案

Instead of FOR UPDATE use LOCK IN SHARE MODE. FOR UPDATE prevents other transactions to read the row as well. LOCK IN SHARE MODE allows read, but prevents updating.

Reference: MySQL Manual

------ session 1

START TRANSACTION;
SELECT * FROM test WHERE t=1 LOCK IN SHARE MODE;
UPDATE test SET NAME='irfandd' WHERE t=2;
COMMIT;

----- session 2 (which is not being blocked anymore :) )

START TRANSACTION;
UPDATE test SET NAME='irfandd' WHERE t=4;
COMMIT;

Update:

Realizing that the table has no index on t, I have the following explanation:

First, transaction T1 locks the row 1 in SELECT * FROM test WHERE t=1 FOR UPDATE

Next, transaction T2 tries to execute UPDATE test SET NAME='irfandd' WHERE t=4. To find out which row(s) are affected, it needs to scan all rows, including row 1. But that is locked, so T2 must wait until T1 finishes. If there is any kind of index, the WHERE t=4 can use the index to decide if row 1 contains t=4 or not, so no need to wait.

Option 1: add an index on test.t so your update can use it.

Option 2: use LOCK IN SHARE MODE, which is intended for putting a read lock only. Unfortunately this option creates a deadlock. Interestingly, T2 transaction executes (updating row 4), and T1 fails (updating row 2). It seems that T1 read-locks row 4 also, and since T2 modifies it, T1 fails because of the transaction isolation level (REPEATABLE READ by default). The final solution would be playing with Transaction Isolation Levels, using READ UNCOMMITTED or READ COMMITTED transaction levels.

The simplest is Option 1, IMHO, but it's up to your possibilities.

这篇关于MySQL中的行级锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！