间隙锁例子

 

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)

本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/827971