如何锁定一行

一 锁定方法

 二 实战

# 原始数据
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)