种一致性的锁定读(locking read)操作:
- select ··· for update
- select ··· lock in share mode
select ··· for update对读取的行记录加一个X锁,其他事务不能对已锁定的行加上任何锁。select ··· lock in share mode对读取的行记录加一个S锁,其他事务可
以向被锁定的行加S锁,但是如果加X锁,则会被阻塞。
对于一致性非锁定读,即使读取的行已被执行了select ··· for update,也是可以进行读取的。此外,select ··· for update或者select ··· lock in share mode必须在
一个事务中,当事务提交了,锁也就释放了。因此在使用上述两种select锁定语句时,务必加上begin,start transaction或者set autocommit=0。
3 锁的算法
3.1行锁的3中算法
InnoDB存储引擎有3种行锁的算法,其分别是:
- Record Lock:单个行记录上的锁
- Gap Lock:间隙锁,锁定一个范围,但不包含记录本身
- Next-Key Lock:Gap Lock + Record Lock,锁定一个范围,并且锁定记录本身
Record Lock总是会去锁住主键索引记录,如果InnoDB存储引擎表在建立的时候没有设置任何一个主键或唯一非空索引,那么这时InnoDB存储引擎会使用隐式的
主键来进行锁定。
Next-Key Lock是结合了Gap Lock+Record Lock的一种锁定算法,在Next-Key Lock算法下,InnoDB对于行的查询都是采用这种锁定算法。假如一个索引有10,11
,13和20这4个值,那么该索引可能被Next-Key Locking的区间为:
(-无穷,10] ,(10,11], (11,13], (13,20], (20,+无穷)
采用Next-Key Lock的锁定技术称为Next-Key Locking。其设计的目的是为了解决幻读问题。而利用这种锁定技术,锁定的不是单个值,而是一个范围。 然而,
当查询的索引含有唯一属性时,InnoDB存储引擎会对Next-Key Lock进行优化将其降级为Record Lock,即仅锁住索引本身,而不是范围。下面演示一个例子。
mysql> create table t (a int primary key); Query OK, 0 rows affected (0.01 sec) mysql> insert into t select 1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t select 2; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t select 5; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0
接着按下表时间顺序执行操作。
时间 | 会话A | 会话B |
---|---|---|
1 | begin; | |
2 | select * from t where a = 5 for update; | |
3 | begin; | |
4 | insert into t select 4; | |
5 | commit; #成功,不需要等待 | |
6 | commit; |
表t共有1,2,5三个值。在上面的例子中,在会话A中首先对a=5进行X锁定。而由于a是主键且唯一,因此锁定的仅是5这个值,而不是(2,5)这个范围,这样在会话
B中插入值4而不会阻塞,可以立即插入并返回。即锁定由Next-Key Lock算法降级为了Record Lock,从而提高应用的并发性。
如上,Next-Key Lock降级为Record Lock仅在查询的列是唯一索引的情况下。若是辅助索引,则情况会完全不同。同样,首先创建测试表z进行测试:
mysql> create table z (a int ,b int ,primary key(a), key(b)); mysql> insert into z select 1,1; mysql> insert into z select 3,1; mysql> insert into z select 5,3; mysql> insert into z select 7,6; mysql> insert into z select 10,8;
表z的列b是辅助索引,若在会话A中执行下面的SQL语句:
mysql> select * from z where b = 3 for update;
很明显,这时SQL语句通过索引列b进行查询,因此其使用传统的Next-Key Locking技术加锁,并且由于有两个索引,其需要分别进行锁定。对于聚集索引,其仅对列
a等于5的索引加上Record Lock。而对于辅助索引,其加上的是Next-Key Lock,锁定的范围是(1,3),特别需要注意的是,InnoDB存储引擎还会对辅助索引下一个
键值加上gap lock,即还有一个辅助索引范围为(3,6)的锁。因此,若在新会话B中运行下面的SQL语句,都会被阻塞:
mysql> select * from z where a = 5 lock in share mode; mysql> insert into z select 4,2; mysql> insert into z select 6,5;
第一个SQL语句不能执行,因为在会话A中执行的SQL语句已经对聚集索引中列a=5的值加上X锁,因此执行会被阻塞。第二个SQL语句,主键插入4,没有问题,但是插入
的辅助索引值2在锁定的范围(1,3)中,因此执行同样会被阻塞。第三个SQL语句,插入的主键6没有被锁定,5也不在范围(1,3)之间。但插入的值5在另一个锁定的
范围(3,6)中,故同样需要等待。而下面的SQL语句,不会被阻塞,可以立即执行:
mysql> insert into z select 8,6; mysql> insert into z select 2,0; mysql> insert into z select 6,7;
从上面的例子可以看到,Gap Lock的作用是为了阻止多个事务将记录插入到同一个范围内,而这会导致幻读问题的产生。假如在上面的例子中,会话A中用户已经锁定了
b=3的记录。若此时没有Gap Lock锁定(3,6),那么用户可以插入索引b列为3的记录,这会导致会话A中的用户再次执行同样查询时会返回不同的记录,即幻读。
这里主要探究的是InnoDB存储引擎锁表的机制,至少自己明白了Mysql的行锁机制,不知道读者是否有疑问,欢迎留言。下次会记录关于Mysql事务特性及其内部的实现机制,
包括mysql的内部架构,InnoDB buffer Pool,redo log, undo log等具体的详解,目前只是对知识过了一遍,但还未总结。
总结
到此这篇关于Mysql技术内幕之InnoDB锁的文章就介绍到这了,更多相关Mysql InnoDB锁内容请搜索
更多相关Mysql内容来自木庄网络博客
标签:Mysql
相关阅读 >>
更多相关阅读请进入《mysql》频道 >>

数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。