MySQL“选择更新"行为 [英] MySQL 'select for update' behaviour

问题描述

根据MySql文档，MySql支持多重粒度锁定(MGL).

案例1

打开1号航站楼:

//连接到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> 

打开并打开2号航站楼:

//连接到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

尽管要检索的行很多，但T2等待直到t1完成.

案例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. 但是为什么在情况1中，T2等待T1锁定的同一行?

2. 这是否意味着无限制的选择查询(即使使用limint参数.我也尝试了不同的范围)阻塞了整个表?

3. 有没有什么方法可以使事务独立锁定而无需指定记录的字段(即不使用 where field = value )?
4. 通常(或按照Java并发锁定)，写锁定是排他的，读不是.在情况2.1中，尽管记录处于写锁定模式，但是T2如何读取相同的记录?既然允许这样做，锁定它有什么意义?
可以理解情况2.2.

打开终端并进行交易:

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

将其留在那里并打开另一个终端和交易记录:

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

在我提交(或回滚)T1之前，T2不会成功.

1. 为什么会这样?

解决方案

让我仔细研究一下您的情况并解释这些锁的工作原理:

1例

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

T2想要更新测试表中的某些行.此事务将IX(因为IX与IX兼容)锁定在所有表上，并尝试进入前5行，但由于X与X不兼容而无法执行操作

所以我们很好.

2.1例

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

T2希望从测试表中选择一些行.而且它不会放置任何锁(因为InnoDB提供了非锁定读取)

2.1例

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

T2想要更新(选择更新)测试表中的某些行.将IS放在整个表上，并尝试在行上获取S锁，但由于X和S不兼容而失败.

---

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

希望有帮助

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

case-1

Opened terminal-1:

// connected to 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:

// connected to 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.

case-2

Left terminal-1 as is.Now in terminal-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.

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.

1. Why is this behavior?

解决方案

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

1 case

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.

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

So we are fine.

2.1 case

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.

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

2.1 case

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.

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.

---

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

Hope it helps

这篇关于MySQL“选择更新"行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！