MySQL“选择更新”行为

MySQL 'select for update' behaviour

提问by Pragalathan M

As per the MySql documentation, MySql supports Multiple granularity locking(MGL).

根据 MySql 文档，MySql 支持多粒度锁定（MGL）。

case-1

情况1

Opened terminal-1:

打开终端 1：

// connected to mysql

//连接到mysql

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select id, status from tracking_number limit 5 for update;
+----+--------+
| id | status |
+----+--------+
|  1 |      0 |
|  2 |      0 |
|  3 |      0 |
|  4 |      0 |
|  5 |      0 |
+----+--------+
5 rows in set (0.00 sec)
mysql> 

left it opened and opened terminal-2:

让它打开并打开终端2：

// connected to mysql

//连接到mysql

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select id, status from tracking_number limit 5 for update;

<!-- Hangs here. and after some time it says-->
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Though there are plenty of rows to retrieve, T2 waits until t1 completes.

尽管有很多行要检索，但 T2 会等到 t1 完成。

case-2

案例 2

Left terminal-1 as is.Now in terminal-2:

左终端 1 原样。现在在终端 2：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

<!-- case 2.1 -->
mysql> select id, status from tracking_number where id=1;
+----+--------+
| id | status |
+----+--------+
|  1 |      0 |
+----+--------+
1 row in set (0.00 sec)

mysql> select id, status from tracking_number where id=2;
+----+--------+
| id | status |
+----+--------+
|  2 |      0 |
+----+--------+
1 row in set (0.00 sec)

<!-- case 2.2 -->
mysql> select * from tracking_number where id=2 for update;
<!-- Hangs here. and after some time -->
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

1. But why in case 1, T2 waits for the same set of rows that T1 has locked?

2. Does it mean the unbounded select query (even with limint parameter. I have tried with different range also) blocks the entire table?

3. Is there any way to let transactions to lock independently without specifying the field of the record(i.e., without using where field=value)?
4. Generally (or as per Java concurrent locking), write lock is exclusive and read is not. In case 2.1, though the records are in write lock mode, how T2 can read the same records? Since this is allowed what is the point in locking it?
5. Case 2.2 is understood.

1. 但是为什么在情况 1 中，T2 等待 T1 锁定的同一组行？

2. 这是否意味着无界选择查询（即使使用 limit 参数。我也尝试过不同的范围）会阻止整个表？

3. 有没有办法让事务在不指定记录字段的情况下独立锁定（即，不使用where field=value）？
4. 通常（或根据 Java 并发锁定），写锁是排他的，而读不是。在案例2.1中，虽然记录处于写锁定模式，但T2如何读取相同的记录？既然这是允许的，锁定它有什么意义？
5. 情况 2.2 理解。

Opened a terminal and a transaction:

打开一个终端和一个交易：

mysql> update tracking_number set status=4 where status=0 limit 5;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

Left it there and opened another terminal and transaction:

把它留在那里并打开另一个终端和交易：

mysql> update tracking_number set status=5 where status=0 limit 5; 

T2 did not succeed until i committed (or rollback) T1.

直到我提交（或回滚）T1，T2 才成功。

1. Why is this behavior?

1. 为什么会出现这种行为？

采纳答案by ravnur

Let me go through your cases and explain how these locks work:

让我通过你的案例来解释这些锁是如何工作的：

1 case

1例

T1 wants to update some rows in your test table. This transaction puts IX lock on all table and X lock on the first 5 rows.

T1 想要更新测试表中的一些行。此事务在所有表上放置 IX 锁，在前 5 行上放置 X 锁。

T2 wants to update some rows in your test table. This transaction puts IX (because IX compatible with IX) lock on all table and tries to first 5 rows but it can't do it because X is not compatible with X

T2 想要更新测试表中的一些行。此事务将 IX（因为 IX 与 IX 兼容）锁定在所有表上并尝试前 5 行，但无法执行，因为 X 与 X 不兼容

So we are fine.

所以我们很好。

2.1 case

2.1 案例

T1 wants to update some rows in your test table. This transaction put IX lock on all table and X lock on the first 5 rows.

T1 想要更新测试表中的一些行。这个事务在所有表上放了 IX 锁，在前 5 行上放了 X 锁。

T2 wants to select some rows from your test table. And it does not place any locks (because InnoDB provides non-locking reads)

T2 想从您的测试表中选择一些行。并且它不放置任何锁（因为 InnoDB 提供非锁定读取）

2.1 case

2.1 案例

T1 wants to update some rows in your test table. This transaction put IX lock on all table and X lock on the first 5 rows.

T1 想要更新测试表中的一些行。这个事务在所有表上放了 IX 锁，在前 5 行上放了 X 锁。

T2 wants to update (select for update)some rows from your test table. Place IS on the whole table and tries to get S lock on the row and fails because X and S are uncompatible.

T2 想要更新（选择更新）测试表中的一些行。将 IS 放在整个表上并尝试在行上获得 S 锁，但由于 X 和 S 不兼容而失败。

---

---

Also always be aware of isolation level: different level cause different mechanism to free/acquire locks

还要时刻注意隔离级别：不同的级别会导致不同的机制来释放/获取锁

Hope it helps

希望能帮助到你