REPEATABLE-READ可重复读隔离级别下的间歇锁模拟测试
作者：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: 0Changed: 0Warnings: 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: 0Changed: 0Warnings: 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)
本帖最后由 风横万里 于 2012-5-15 22:14 编辑
我觉得，两个例子并不是死锁问题。
repeatable read 解决幻读问题，因而有间隙锁。例1中 session A间隙锁锁定了不存在记录，session B 锁等待最后超时，如果session A可以及时commit，session B 也可以完成，并不是死锁。死锁状态下，若无外部机制干预，不可能通过commit解除死锁。
read commit 不管幻读只解决脏读，例2中只有锁定id 1,2,3,4时 session b 才有锁等待。
页:
[1]