MariaDB社区
标题: REPEATABLE-READ可重复读隔离级别下的间歇锁模拟测试 [打印本页]
作者: kider 时间: 2012-5-4 18:21
标题: 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: 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)
作者: 风横万里 时间: 2012-5-15 22:11
本帖最后由 风横万里 于 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 才有锁等待。
| 欢迎光临 MariaDB社区 (http://123.56.88.72/) |
Powered by Discuz! X3.2 |