一个InnoDB 加锁的案例
一个InnoDB 加锁的案例
最近一直在优化各个产品的SQL语句,同时还帮一个同事解决deadlock问题,收获就是对InnoDB加锁的理解更加深入了。先来看看今天的这个案例:
- mysql> select version();
- +----------------------+
- | version() |
- +----------------------+
- | 5.5.13.4-log |
- +----------------------+
- mysql> show variables like'%iso%';
- +---------------+-----------------+
- | Variable_name | Value |
- +---------------+-----------------+
- | tx_isolation | REPEATABLE-READ |
- +---------------+-----------------+
- CREATETABLE `t1` (
- `a` int(11) NOTNULL,
- `b` int(11) DEFAULTNULL,
- `c` int(11) DEFAULTNULL,
- PRIMARYKEY (`a`)
- ) ENGINE=InnoDB;
- mysql> select * from t1;
- +----+------+------+
- | a | b | c |
- +----+------+------+
- | 1 | 1 | 1 |
- | 2 | 1 | 1 |
- | 3 | 1 | 1 |
- | 10 | 1 | 1 |
- | 11 | 1 | 1 |
- | 12 | 1 | 1 |
- | 14 | 1 | 1 |
- | 15 | 1 | 1 |
- +----+------+------+
测试环境就是如上,开始下面的测试:
- session1:
- mysql> begin;
- Query OK, 0 rows affected (0.00 sec)
- mysql> select * from t1 where a in (2, 10, 11, 12,14) forupdate;
- +----+------+------+
- | a | b | c |
- +----+------+------+
- | 2 | 1 | 1 |
- | 10 | 1 | 1 |
- | 11 | 1 | 1 |
- | 12 | 1 | 1 |
- | 14 | 1 | 1 |
- +----+------+------+
- 5 rowsinset (0.00 sec)
- session2:
- mysql> insertinto t1 values(7, 1, 1);
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- mysql> select * from t1 where a=15 forupdate;
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- mysql> insertinto t1 values(18,1,1);
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- mysql> insertinto t1 values(18000,1,1);
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
从上面可以看出InnoDB仿佛是将[2, 无穷大)这个区间给锁了,那么原因是什么呢? 你可以先思考下
|
评论暂时关闭