间隙锁例子

session A:

　

session B:

mysql> show variables like '%iso%';

+---------------+-----------------+

| Variable_name | Value           |

| tx_isolation  | REPEATABLE-READ |

1 row in set (0.00 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> update user set name='ddd' where id=4;

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, 1 row affected (0.00 sec)

|  4 | ddd    |

4 rows in set (0.01 sec)

4 rows in set (0.00 sec)

+---------------+----------------+

| Variable_name | Value          |

| tx_isolation  | READ-COMMITTED |

4 rows in set (0.02 sec)

mysql> update user set name='cvvt' where id=5;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into user values (5,'cvvt');

|  5 | cvvt   |

5 rows in set (0.00 sec)

mysql>  select * from user;

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