一个InnoDB 加锁的案例
来源:互联网 发布:嵌入式linux如何移植 编辑:程序博客网 时间:2024/06/05 07:39
最近一直在优化各个产品的SQL语句,同时还帮一个同事解决deadlock问题,收获就是对InnoDB加锁的理解更加深入了。先来看看今天的这个案例:
mysql> select version();+----------------------+| version() |+----------------------+| 5.5.13.4-log |+----------------------+mysql> show variables like '%iso%';+---------------+-----------------+| Variable_name | Value |+---------------+-----------------+| tx_isolation | REPEATABLE-READ |+---------------+-----------------+CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`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) for update;+----+------+------+| a | b | c |+----+------+------+| 2 | 1 | 1 || 10 | 1 | 1 || 11 | 1 | 1 || 12 | 1 | 1 || 14 | 1 | 1 |+----+------+------+5 rows in set (0.00 sec)
session2:mysql> insert into t1 values(7, 1, 1);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> select * from t1 where a=15 for update;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> insert into t1 values(18,1,1);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> insert into t1 values(18000,1,1);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
从上面可以看出InnoDB仿佛是将[2, 无穷大)这个区间给锁了,那么原因是什么呢? 你可以先思考下
再来看第二组测试
session1:mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t1 where a in (2, 14) for update;+----+------+------+| a | b | c |+----+------+------+| 2 | 1 | 1 || 14 | 1 | 1 |+----+------+------+2 rows in set (0.00 sec)
session2:mysql> insert into t1 values(7, 1, 1);Query OK, 1 row affected (0.00 sec)
从上面可以看出,这次2与14之间的区间并没有被锁住(14后面的肯定也没锁,只是没贴出结果而已),那这又是为什么呢?
要理解上面的现象首先得明白InnoDB是怎么加锁的。在InnoDB加锁时它是这样做的:InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters.也就是说它是在扫描表或者索引时遇到的行都会被加锁,一定请注意encounters这个单词,是扫描过程中遇到的行都会被锁住。那么再来看上面的现象,为什么对主键的查询还会锁一个那么大的区间呢?首先第一感觉肯定是只加in()中列出的几行,但又想InnoDB是对扫描过程中遇到的行都加锁,所以曾误以为where a in(val1, ..., valn)这种形式的会是锁val1到valn中最小、最大值组成的区间(因为觉得扫描就是从2到14),但是上面的结果不是显示18000也无法插入吗?这又是为什么呢?后来经同事指点才猛然大悟:
mysql> explain select * from t1 where a in (2, 10, 11, 12,14) for update;+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | test | ALL | PRIMARY | NULL | NULL | NULL | 8 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)看了这个之后相信大家都明白原因了吧,因为这个查询走的是全表扫描,所以所有的行都加了记录,包括那些现在不存在记录的区间。那么对于第二个例子呢?
mysql> explain select * from t1 where a in (2, 14) for update;+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE | test | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+1 row in set (0.00 sec)从上面可以看出,这次没走全表扫描,而是主键索引(range,将扫描区间分为[2, 2], [14, 14]两者区间),因为主键索引可以定位到具体的行,自然加锁的行就只有2和14了。
从这个小例子也可以联想到InnoDB的三种类型的锁gap lock/next-key lock/record lock。为什么gap lock/next-key lock是锁一个区间(当然[a, a+1)也是一个区间,这个区间只是特殊点只有一条记录而已)。它的目的是防止幻读。它为什么能实现防止幻读?因为扫描过程中的区间全部加锁,那自然下次再读的时候别人是无法插入数据进来的。
从这个小例子我们也可以明白一点为什么加索引如此重要。假如没有合适的索引,不仅查询的速度会很慢,而当你对表进行update/delete的时候你会发现很多意想不到的记录被锁住,导致锁等待超时或者死锁错误。
参考文章
- refman-5.5-en.pdf 13.3.9/The InnoDB Transaction Model and Locking
- 一个InnoDB 加锁的案例
- 一个奇怪的加锁案例--Execute “ if(not exists (select ..))” locked next record
- InnoDB的行锁模式及加锁方法
- 数据库解析——InnoDB的行锁模式及加锁方法
- MySQL InnoDB中唯一索引和非唯一索引时的加锁情况
- innodb RC级别下加锁特殊情况
- InnoDB 解锁和加锁(死锁)问题
- INNODB加锁分析处理(二)
- MySQL InnoDB中各类语句加锁方式
- 发现了“文件加锁王”的一个通用密码。
- 在线分享《从案例说InnoDB的基本优化》
- iframe的一个案例
- Storm 的一个案例
- 一个小小的案例
- 一个需要避免的InnoDB间隙锁
- mysqldump导数据库数据不加锁(InnoDB)
- (五)MySQL InnoDB中各类语句加锁方式
- MySQL innodb中各种SQL语句加锁分析
- C语言void指针的运算操作[小知识]
- 深入体验JavaWeb开发内幕——两种会话状态之Session会话
- 如何将当前布局用代码保存在png图像文件中?
- sony.vegas.pro.12.0.(build.394).64-bit.patch-MPT
- 如何让你的SQL运行得更快
- 一个InnoDB 加锁的案例
- FastStone.Image.Viewer.4.keygen-REPT
- vs 2005/2008中ActiveX控件的使用
- FastStone.MaxView.2.keygen-REPT
- 关于数组的几道面试题
- 使用数组实现栈和循环队列(JAVA语言)
- MSSQL断电的处理方法
- cocos2dx 内存机制
- 最近写c++程序的心得