一 锁定方法
二 实战
# 原始数据
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b1 |
| 3 | mm |
| 5 | mm |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9001 |
| 1 | b1 |
| 2 | 2000 |
+------+------+
9 rows in set (0.00 sec)
# 第一个客户端:锁 a = 8 这一行
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_innodb_lock where a = 8 for update;
+------+------+
| a | b |
+------+------+
| 8 | 8000 |
+------+------+
1 row in set (0.00 sec)
# 第二个客户端:执行对 a = 8 的更新,发生阻塞
mysql> update test_innodb_lock set b='YYY' where a = 8;
# 第一个客户端:执行对 a = 8 的更新,操作成功
mysql> update test_innodb_lock set b='zzz' where a = 8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 第一个客户端:提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 第二个客户端:阻塞解除
mysql> update test_innodb_lock set b='YYY' where a = 8;
Query OK, 1 row affected (11.96 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 第二个客户端:提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 在两个客户端分别查询数据,最终 b 的数据是 YYY
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b1 |
| 3 | mm |
| 5 | mm |
| 6 | 6000 |
| 7 | 7000 |
| 8 | YYY |
| 9 | 9001 |
| 1 | b1 |
| 2 | 2000 |
+------+------+
9 rows in set (0.00 sec)