一 表脚本
CREATE TABLE test_innodb_lock (
a INT(11),
b VARCHAR(16)
)ENGINE=INNODB;
INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
二 实战
# 在第一个客户端:关闭自动提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
# 在第二个客户端:关闭自动提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
# 在第一个客户端:更新但不提交,没有手写 commit;
mysql> update test_innodb_lock set b='b1' where a = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0
# 在第一个客户端:查询 a=4,更新生效,体现出读己之所写
mysql> select * from test_innodb_lock where a=4;
+------+------+
| a | b |
+------+------+
| 4 | b1 |
+------+------+
1 row in set (0.00 sec)
# 在第二个客户端:查询 a=4,更新没生效,第一个客户端还没提交,所有看不到变化
mysql> select * from test_innodb_lock where a=4;
+------+------+
| a | b |
+------+------+
| 4 | 4000 |
+------+------+
1 row in set (0.00 sec)
# 在第一个客户端:执行提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 在第二个客户端:查询 a=4,还是看不到变化,因为客户端2设置了取消自动提交
mysql> select * from test_innodb_lock where a=4;
+------+------+
| a | b |
+------+------+
| 4 | 4000 |
+------+------+
1 row in set (0.00 sec)
# 在第二个客户端:提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 在第二个客户端: 看到变化了
mysql> select * from test_innodb_lock where a=4;
+------+------+
| a | b |
+------+------+
| 4 | b1 |
+------+------+
1 row in set (0.00 sec)
# 在第三个客户端:不取消自动提交,查询 a=4,这里能看到变化,这就是取消自动提交和不取消自动提交的区别
mysql> select * from test_innodb_lock where a=4;
+------+------+
| a | b |
+------+------+
| 4 | b1 |
+------+------+
1 row in set (0.00 sec)
# 在第一个客户端:对 a=4 执行修改操作
mysql> update test_innodb_lock set b='4002' where a = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 在第二个客户端:对 a=4 也执行修改操作,发生阻塞
mysql> update test_innodb_lock set b='4003' where a = 4;
# 在第一个客户端:提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 在第二个客户端:阻塞解除
mysql> update test_innodb_lock set b='4003' where a = 4;
Query OK, 1 row affected (44.15 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 在第二个客户端:提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 在第一个客户端:查询 a = 4,显示的第二个客户端的修改值,因为它是后提交的
mysql> select * from test_innodb_lock where a=4;
+------+------+
| a | b |
+------+------+
| 4 | 4003 |
+------+------+
1 row in set (0.00 sec)
# 在第二个客户端:查询 a = 4,显示的第二个客户端的修改值,因为它是后提交的
mysql> select * from test_innodb_lock where a=4;
+------+------+
| a | b |
+------+------+
| 4 | 4003 |
+------+------+
1 row in set (0.00 sec)
# 在第一个客户端:更新 a = 4
mysql> update test_innodb_lock set b='4005' where a = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 在第二个客户端:更新 a = 9,更新成功,不会阻塞,因为更新的是不同的行,大路朝天,各走一边
mysql> update test_innodb_lock set b='9000' where a = 9;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
# 在第一个客户端:提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 在第二个客户端:提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 在第一个客户端:查询 a =4 和a=9,查询正确
mysql> select * from test_innodb_lock where a in (4,9);
+------+------+
| a | b |
+------+------+
| 4 | 4005 |
| 9 | 9000 |
+------+------+
2 rows in set (0.00 sec)
# 在第二个客户端:查询 a =4 和a=9,查询正确
mysql> select * from test_innodb_lock where a in (4,9);
+------+------+
| a | b |
+------+------+
| 4 | 4005 |
| 9 | 9000 |
+------+------+
2 rows in set (0.00 sec)
三 行锁定基本演示