REPEATABLE-READ可重复读隔离级别下的间歇锁模拟测试

kider

作者：suqall

大致是往表里添加一条记录前，先在表里检查一下，然后更新表里的字段，如果发现没有，再insert 表添加记录。

如果是默认的REPEATABLE-READ可重复读隔离级别，多个会话insert就会出现死锁，原理就是间歇锁对其加个排它锁。

以下是我在虚拟上模拟的：

session A:        　        　        session B:        　        　        　
　                                                                　
mysql> show variables like '%iso%';        mysql> show variables like '%iso%';        　
+---------------+-----------------+        +---------------+-----------------+        　
| Variable_name | Value           |        | Variable_name | Value           |        　
+---------------+-----------------+        +---------------+-----------------+        　
| tx_isolation  | REPEATABLE-READ |        | tx_isolation  | REPEATABLE-READ |        　
+---------------+-----------------+        +---------------+-----------------+        　
1 row in set (0.00 sec)                1 row in set (0.00 sec)                　
　                                                                　
mysql> begin;                        mysql> begin;                        　
Query OK, 0 rows affected (0.00 sec)        Query OK, 0 rows affected (0.00 sec)
　                                                                　
mysql> select * from user;                mysql> select * from user;                　
+----+--------+                        +----+--------+                        　
| id | name   |                        | id | name   |                        　
+----+--------+                        +----+--------+                        　
|  1 | ligang |                        |  1 | ligang |                        　
|  2 | lvbu   |                        |  2 | lvbu   |                        　
|  3 | zhaoyu |                        |  3 | zhaoyu |                        　
+----+--------+                        +----+--------+                        　
3 rows in set (0.00 sec)                3 rows in set (0.00 sec)                　
　                                                                　
mysql> update user set name='ddd' where id=4;                                　
Query OK, 0 rows affected (0.00 sec)                                        　
Rows matched: 0  Changed: 0  Warnings: 0                                　
(把存在ddd之外的记录全部加X锁)                                        　
　                                mysql> insert into user values(4,'ddd');
　                                ERROR 1205 (HY000): Lock wait timeout exceeded;
　                                try restarting transaction                　
　                                                                　
mysql> commit;                                                        　
Query OK, 0 rows affected (0.00 sec)                                        　
　                                                                　
　                                mysql> insert into user values(4,'ddd');
　                                Query OK, 1 row affected (0.00 sec)        　
　                                                                　
　                                mysql> select * from user;                　
　                                +----+--------+                        　
　                                | id | name   |                        　
　                                +----+--------+                        　
　                                |  4 | ddd    |                        　
　                                |  1 | ligang |                        　
　                                |  2 | lvbu   |                        　
　                                |  3 | zhaoyu |                        　
　                                +----+--------+                        　
　                                4 rows in set (0.01 sec)                　
　                                                                　
mysql> begin;                                                        　
Query OK, 0 rows affected (0.00 sec)                                        　
　                                                                　
mysql> select * from user;                                                　
+----+--------+                                                        　
| id | name   |                                                        　
+----+--------+                                                        　
|  1 | ligang |                                                        　
|  2 | lvbu   |                                                        　
|  3 | zhaoyu |                                                        　
+----+--------+                                                        　
3 rows in set (0.00 sec)                                                　
　                                                                　
　                                mysql> commit;                        　
　                                Query OK, 0 rows affected (0.00 sec)
　                                                                　
mysql> select * from user;                                                　
+----+--------+                                                        　
| id | name   |                                                        　
+----+--------+                                                        　
|  1 | ligang |                                                        　
|  2 | lvbu   |                                                        　
|  3 | zhaoyu |                                                        　
+----+--------+                                                        　
3 rows in set (0.00 sec)                                                　
　                                                                　
mysql> commit;                                                        　
Query OK, 0 rows affected (0.00 sec)                                        　
　                                                                　
mysql> select * from user;                                                　
+----+--------+                                                        　
| id | name   |                                                        　
+----+--------+                                                        　
|  4 | ddd    |                                                        　
|  1 | ligang |                                                        　
|  2 | lvbu   |                                                        　
|  3 | zhaoyu |                                                        　
+----+--------+                                                        　
4 rows in set (0.00 sec)        　        　        　        　        　        　
                                                               

---

　        　        　        　        　        　        　        　        　
session A:        　        　        session B:        　        　        　
　                                                                　
mysql> show variables like '%iso%';        mysql> show variables like '%iso%';        　
+---------------+----------------+        +---------------+----------------+        　
| Variable_name | Value          |        | Variable_name | Value          |        　
+---------------+----------------+        +---------------+----------------+        　
| tx_isolation  | READ-COMMITTED |        | tx_isolation  | READ-COMMITTED |        　
+---------------+----------------+        +---------------+----------------+        　
1 row in set (0.00 sec)                1 row in set (0.00 sec)                　
　                                                                　
mysql> begin;                        mysql> begin;                        　
Query OK, 0 rows affected (0.00 sec)        Query OK, 0 rows affected (0.00 sec)
　                                                                　
mysql> select * from user;                mysql> select * from user;                　
+----+--------+                        +----+--------+                        　
| id | name   |                        | id | name   |                        　
+----+--------+                        +----+--------+                        　
|  4 | ddd    |                        |  4 | ddd    |                        　
|  1 | ligang |                        |  1 | ligang |                        　
|  2 | lvbu   |                        |  2 | lvbu   |                        　
|  3 | zhaoyu |                        |  3 | zhaoyu |                        　
+----+--------+                        +----+--------+                        　
4 rows in set (0.02 sec)                4 rows in set (0.02 sec)                　
　                                                                　
mysql> update user set name='cvvt' where id=5;                        　
Query OK, 0 rows affected (0.01 sec)                                        　
Rows matched: 0  Changed: 0  Warnings: 0                                　
　                                                                　
　                                mysql> insert into user values (5,'cvvt');
　                                Query OK, 1 row affected (0.00 sec)        　
　                                                                　
　                                mysql> select * from user;                　
　                                +----+--------+                        　
　                                | id | name   |                        　
　                                +----+--------+                        　
　                                |  5 | cvvt   |                        　
　                                |  4 | ddd    |                        　
　                                |  1 | ligang |                        　
　                                |  2 | lvbu   |                        　
　                                |  3 | zhaoyu |                        　
　                                +----+--------+                        　
　                                5 rows in set (0.00 sec)                　
　                                                                　
mysql>  select * from user;                                        　
+----+--------+                                                        　
| id | name   |                                                        　
+----+--------+                                                        　
|  4 | ddd    |                                                        　
|  1 | ligang |                                                        　
|  2 | lvbu   |                                                        　
|  3 | zhaoyu |                                                        　
+----+--------+                                                        　
4 rows in set (0.00 sec)                                                　
　                                                                　
　                                mysql> commit;                        　
　                                Query OK, 0 rows affected (0.00 sec)
　                                                                　
mysql>  select * from user;                                        　
+----+--------+                                                        　
| id | name   |                                                        　
+----+--------+                                                        　
|  5 | cvvt   |                                                        　
|  4 | ddd    |                                                        　
|  1 | ligang |                                                        　
|  2 | lvbu   |                                                        　
|  3 | zhaoyu |                                                        　
+----+--------+                                                        　
5 rows in set (0.00 sec)        　        　        　        　        　        　

风横万里

我觉得，两个例子并不是死锁问题。
repeatable read 解决幻读问题，因而有间隙锁。例1中 session A间隙锁锁定了不存在记录，session B 锁等待最后超时，如果session A可以及时commit，session B 也可以完成，并不是死锁。死锁状态下，若无外部机制干预，不可能通过commit解除死锁。
read commit 不管幻读只解决脏读，例2中只有锁定id 1,2,3,4时 session b 才有锁等待。