MYSQL教程详谈innodb的锁(record,gap,Next-Key lock)_mysql

概述介绍《MYSQL教程详谈innodb的锁(record,gap,Next-Key lock)》开发教程，希望对您有用。

《MysqL教程详谈innodb的锁(record,gap,Next-Key lock)》要点：@H_404_2@
本文介绍了MysqL教程详谈innodb的锁(record,Next-Key lock)，希望对您有用。如果有疑问，可以联系我们。

Record lock单条索引记录上加锁,record lock锁住的永远是索引,而非记录本身,即使该表上没有任何索引,那么innodb会在后台创建一个暗藏的聚集主键索引,那么锁住的就是这个暗藏的聚集主键索引.所以说当一条sql没有走任何索引时,那么将会在每一条聚集索引后面加X锁,这个类似于表锁,但原理上和表锁应该是完全不同的.
MysqL教程

Gap lock在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身.gap lock的机制主要是办理可重复读模式下的幻读问题,关于幻读的演示和gap锁如何办理了幻读.关于这一块,先给出几个定义
MysqL教程

快照读： @H_404_2@MysqL教程

简单的select *** 作,没有lock in share mode或for update,快照读不会加任何的锁,而且由于MysqL的一致性非锁定读的机制存在,任何快照读也不会被阻塞.但是如果事务的隔离级别是SERIAliZABLE的话,那么快照读也会被加上共享的next-key锁,本文不对SERIAliZABLE隔离级别做叙述.
MysqL教程

当前读： @H_404_2@MysqL教程

官方文档的术语叫locking read,也便是insert,update,delete,select..in share mode和select..for update,当前读会在所有扫描到的索引记录上加锁,不管它后面的where条件到底有没有命中对应的行记录.当前读可能会引起死锁.MysqL教程

意向锁： @H_404_2@MysqL教程

innodb的意向锁主要用户多粒度的锁并存的情况.比如事务A要在一个表上加S锁,如果表中的一行已被事务B加了X锁,那么该锁的申请也应被阻塞.如果表中的数据很多,逐行检查锁标志的开销将很大,系统的性能将会受到影响.为了办理这个问题,可以在表级上引入新的锁类型来表示其所属行的加锁情况,这就引出了“意向锁”的概念.举个例子,如果表中记录1亿,事务A把其中有几条记录上了行锁了,这时事务B需要给这个表加表级锁,如果没有意向锁的话,那就要去表中查找这一亿条记录是否上锁了.如果存在意向锁,那么假如事务Ａ在更新一条记录之前,先加意向锁,再加Ｘ锁,事务B先检查该表上是否存在意向锁,存在的意向锁是否与自己准备加的锁冲突,如果有冲突,则等待直到事务Ａ释放,而无须逐条记录去检测.事务Ｂ更新表时,其实无须知道到底哪一行被锁了,它只要知道反正有一行被锁了就行了.
说白了意向锁的主要作用是处理行锁和表锁之间的矛盾,能够显示“某个事务正在某一行上持有了锁,或者准备去持有锁”
MysqL教程

不可重复读： @H_404_2@MysqL教程

指的是在同一个事务中,连续几次快照读,读取的记录应该是一样的MysqL教程

不可重复读的演示较为简单,本文不做讨论.MysqL教程

幻读： @H_404_2@MysqL教程

指的是在一个事务A中执行了一个当前读 *** 作,而另外一个事务B在事务A的影响区间内insert了一条记录,这时事务A再执行一个当前读 *** 作时,出现了幻行.这和不可重复读的主要区别就在与事务A中一个是快照读,一个当前读；并且事务B中一个是任何的dml *** 作,一个只是insert.好比在A中select * from test where ID<10 lock in share mode结果集为(1,2,3),这时在B中对test表插入了一条记录4,这时在A中重新查询结果集就是(1,3,4),和事务A在第一次查询出来的结果集不一致,这里的4就是幻行.MysqL教程

演示条件：@H_404_2@由于可重读的隔离级别下,默认采用Next-Key Locks,就是Record lock和gap lock的结合,即除了锁住记录自己,还要再锁住索引之间的间隙,所以这个gap lock机制默认打开,并不会产生幻行,那么我们要演示幻行的话,要么将隔离级别改为read-commited,要么在REPEAtable-READ模式下禁用掉gap lock,这里我们采用的是第二种方式.
MysqL教程

幻读的演示在演示之前又引入了innodb_locks_unsafe_for_binlog参数,该参数可以禁用gap lock.
MysqL教程

innodb_locks_unsafe_for_binlog：静态参数,默认为0,表示启动gap lock,如果设置为1,表示禁用gap lock,这时MysqL就只有record lock了,不过值得注意的是,即使了设置了1,关于外键和唯一键重复检查方面用到的gap lock依旧有效.这时可以简单地理解成事务的隔离级别退化成可重复读,然后两者应该还是有所区别的.建议是不要随便设置,我们这里设置只是做个简单的幻读演示,MysqL后续的版本可能都会废弃掉这个参数了.
MysqL教程

session 1 先将myID>95的记录加一个当前读
@H_404_2@MysqL教程

MysqL> show create table test_gap_lock\G
*************************** 1. row ***************************
table: test_gap_lock
Create table: CREATE table `test_gap_lock` (
`ID` int(11) NOT NulL,
`name` varchar(100) DEFAulT NulL,
`myID` int(11) DEFAulT NulL,
PRIMARY KEY (`ID`),
UNIQUE KEY `uniq_name` (`name`),
KEY `IDex_myID` (`myID`)
) ENGINE=InnoDB DEFAulT CHARSET=utf8
1 row in set (0.00 sec)
MysqL> begin;
MysqL> select * from test_gap_lock where myID>95 for update;
+----+------------+------+
| ID | name | myID |
+----+------------+------+
| 1 | jiang | 99 |
| 2 | hubingmei | 99 |
| 5 | hubingmei4 | 100 |
+----+------------+------+
3 rows in set (0.00 sec)
MysqL教程

session 2 这时session 2插入myID=98的记录成功了.
@H_404_2@MysqL教程

insert into test_gap_lock values(6,'jiang2',98);
MysqL教程

query OK,1 row affected (0.00 sec)
MysqL教程

session 1 这时session 1再次查看时发现记录myID=98的记录已经存在了,这条记录便是幻行.
@H_404_2@MysqL教程

MysqL> select * from test_gap_lock where myID>95 for update;
+----+------------+------+
| ID | name | myID |
+----+------------+------+
| 1 | jiang | 99 |
| 2 | hubingmei | 99 |
| 5 | hubingmei4 | 100 |
| 6 | jiang2 | 98 |
+----+------------+------+
4 rows in set (0.00 sec)
MysqL教程

gap lock机制办理幻读问题演示条件：我们再把innodb_locks_unsafe_for_binlog值改回默认值0,并且tx_isolation为MysqL教程

REPEAtable-READ,演示时务必explain下,确保该sql走了非唯一索引IDx_myID(因为如果测试数据较少的话,可能优化器直接走全表扫描,那就导致锁住所有记录,无法模拟出gap锁)
MysqL教程

演示范例 1(非唯一索引+范围当前读)MysqL> show create table test_gap_lock\G
@H_404_2@MysqL教程

*************************** 1. row ***************************
table: test_gap_lock
Create table: CREATE table `test_gap_lock` (
`ID` int(11) NOT NulL,
KEY `IDex_myID` (`myID`)
) ENGINE=InnoDB DEFAulT CHARSET=utf8
1 row in set (0.00 sec)
MysqL教程

session 1 先explain确保session的当前读sql执行走了索引IDx_myID
@H_404_2@MysqL教程

session 2 先插入myID=56的成功,因为锁住的间隙是myID>100,56不在该范围内；再插入myID=109时,会一直卡住直到session 1commit,rollback或者直接锁等待超时,在锁等待超时前在session 1中执行同样的sql,得到的结果依旧只有ID=5,98的记录,这样就避免了幻读问题
MysqL教程

MysqL> insert into test_gap_lock values(999,'test2',56);
query OK,1 row affected (0.00 sec)
MysqL> insert into test_gap_lock values(123,'test4',109);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MysqL教程

演示范例2(非唯一索引+等值当前读)MysqL> select * from test_gap_lock;
@H_404_2@MysqL教程

+-----+------------+------+
| ID | name | myID |
+-----+------------+------+
| 1 | jiang | 98 |
| 2 | hubingmei | 99 |
| 5 | hubingmei4 | 101 |
| 6 | jiang2 | 100 |
| 7 | jiang22 | 70 |
| 67 | jiang222 | 80 |
| 98 | test | 105 |
| 123 | test4 | 109 |
| 999 | test2 | 56 |
+-----+------------+------+
9 rows in set (0.00 sec)
session 1
MysqL> begin;
query OK,0 rows affected (0.00 sec)
MysqL> explain delete from test_gap_lock where myID=100;
+----+-------------+---------------+-------+---------------+-----------+---------+-------+------+-------------+
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+-----------+---------+-------+------+-------------+
| 1 | SIMPLE | test_gap_lock | range | IDex_myID | IDex_myID | 5 | const | 2 | Using where |
+----+-------------+---------------+-------+---------------+-----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
MysqL> delete from test_gap_lock where myID=100;
query OK,2 rows affected (0.00 sec)
MysqL教程

session 2 插入myID=99的记录依旧阻塞,存在gap锁；插入myID=97的记录成功
@H_404_2@MysqL教程

MysqL> insert into test_gap_lock values(676,'gap recored test',99);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MysqL> insert into test_gap_lock values(675,'gap recored test1',97);
query OK,1 row affected (0.00 sec)
MysqL教程

范例3(主键索引+范围当前读)
@H_404_2@MysqL教程

MysqL> select * from test_gap_lock ;
+-----+------------+------+
| ID | name | myID |
+-----+------------+------+
| 1 | jiang | 98 |
| 2 | hubingmei | 98 |
| 5 | hubingmei4 | 100 |
| 6 | jiang2 | 100 |
| 7 | jiang22 | 70 |
| 67 | jiang222 | 80 |
| 98 | test | 105 |
| 123 | test4 | 109 |
| 999 | test2 | 56 |
+-----+------------+------+
9 rows in set (0.00 sec)
session 1
MysqL> begin;
query OK,0 rows affected (0.00 sec)
MysqL> explain select * from test_gap_lock where ID > 100 for update;
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | test_gap_lock | range | PRIMARY | PRIMARY | 4 | NulL | 2 | Using where |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
MysqL> select * from test_gap_lock where ID > 100 for update;
+-----+-------+------+
| ID | name | myID |
+-----+-------+------+
| 123 | test4 | 109 |
| 999 | test2 | 56 |
+-----+-------+------+
2 rows in set (0.00 sec)
MysqL教程

session 2(ID=3可插入；ID=108无法插入,存在gap lock；ID=123的记录无法select..in share mode,因为该记录上存在record lock；ID=125可以被select..in share mode和update,这点比拟奇怪,应该这也算是当前读,不过后来查看官方文档得知,gap锁只会阻塞insert *** 作,因为gap间隙中是不存在任何记录的,除了insert *** 作,其他的 *** 作结果应该都等价于空 *** 作,MysqL就不去阻塞它了)
MysqL教程

MysqL> insert into test_gap_lock values(108,'gap lock test3',123);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MysqL> insert into test_gap_lock values(3,123);
query OK,1 row affected (0.00 sec)
MysqL> select * from test_gap_lock where ID=125 lock in share mode;
Empty set (0.00 sec)
MysqL> explain select * from test_gap_lock where ID=125 lock in share mode;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NulL | NulL | NulL | NulL | NulL | NulL | NulL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
MysqL> update test_gap_lock set myID=12345 where ID=125;
query OK,0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
MysqL教程

gap lock的内部加锁原理gap lock的前置条件：1 事务隔离级别为REPEAtable-READ,innodb_locks_unsafe_for_binlog参数为0,且sql走的索引为非唯一索引
MysqL教程

2 事务隔离级别为REPEAtable-READ,且sql是一个范围的当前读 *** 作,这时即使不是非唯一索引也会加gap lockMysqL教程

gap lock的加锁步骤 @H_404_2@MysqL教程

针对上面的范例1(非唯一索引+范围当前读)和范例3(主键索引+范围当前读)比较好理解,那为什么范例2(非主键索引+等值当前读)为什么也会产生gap lock,这要从btree 索引的原理讲起,我们都知道,btree索引是依照顺序排列的,并且innodb存在主键聚集索引,本人绘图能力有限,已范例2的加锁过程分析举例,手写加锁过程如下图MysqL教程

MysqL教程

从图中的数据组织顺序可以看出,myID=100的记录有两条,如果加gap锁就会产生三个间隙,分别是gap1(98,100),gap2(100,gap3(100,105),在这三个开区间(如果我高中数学没记错的话)内的myID数值无法插入,显然gap1还有(myID=99,ID=3)(myID
MysqL教程

=99,ID=4)等记录,gap2无实际的间隙,gap3还有(myID=101,ID=7)等记录.并且,在myID=100的两条记录上加了record lock,也便是这两条数据业务无法被其他session进行当前读 *** 作(范例三可以看出)
MysqL教程

Next-Key Locks @H_404_2@MysqL教程

在默认情况下,MysqL的事务隔离级别是可重复读,并且innodb_locks_unsafe_for_binlog参数为0,这时默认采用next-key locks.所谓Next-Key Locks,还要再锁住索引之间的间隙.MysqL教程

下面我们针对大部分的sql类型分析是如何加锁的,假设事务隔离级别为可重复读@H_404_2@.MysqL教程

select .. from @H_404_2@MysqL教程

不加任何类型的锁MysqL教程

select...from lock in share mode @H_404_2@MysqL教程

在扫描到的任何索引记录上加共享的(shared)next-key lock,还有主键聚集索引加排它锁MysqL教程

select..from for update @H_404_2@MysqL教程

在扫描到的任何索引记录上加排它的next-key lock,还有主键聚集索引加排它锁MysqL教程

update..where delete from..where @H_404_2@MysqL教程

在扫描到的任何索引记录上加next-key lock,还有主键聚集索引加排它锁MysqL教程

insert into.. @H_404_2@MysqL教程

简单的insert会在insert的行对应的索引记录上加一个排它锁,这是一个record lock,并没有gap,所以并不会阻塞其他session在gap间隙里插入记录.不过在insert *** 作之前,还会加一种锁,官方文档称它为insertion intention gap lock,也就是意向的gap锁.这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时,只要插入的记录不是gap间隙中的相同位置,则无需等待其他session就可完成,这样就使得insert *** 作无须加真正的gap lock.想象一下,如果一个表有一个索引IDx_test,表中有记录1和8,那么每个事务都可以在2和7之间插入任何记录,只会对当前插入的记录加record lock,并不会阻塞其他session插入与本身不同的记录,因为他们并没有任何冲突.
MysqL教程

假设发生了一个唯一键冲突错误,那么将会在重复的索引记录上加读锁.当有多个session同时插入相同的行记录时,如果另外一个session已经获得改行的排它锁,那么将会导致死锁.MysqL教程

insert导致的死锁现象演示1 @H_404_2@MysqL教程

MysqL> show create table t1\G
*************************** 1. row ***************************
table: t1
Create table: CREATE table `t1` (
`i` int(11) NOT NulL DEFAulT '0',
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAulT CHARSET=utf8
1 row in set (0.00 sec)
MysqL教程

session 1 @H_404_2@MysqL教程

MysqL> begin;
query OK,0 rows affected (0.00 sec)
MysqL> INSERT INTO t1 VALUES(1);
query OK,1 row affected (0.00 sec)
MysqL教程

session 2 这时session2一直被卡住@H_404_2@MysqL教程

MysqL> begin;
query OK,0 rows affected (0.00 sec)
MysqL> INSERT INTO t1 VALUES(1);
MysqL教程

session 3 这时session3也一直被卡住@H_404_2@MysqL教程

MysqL> begin;MysqL教程

query OK,0 rows affected (0.00 sec)MysqL教程

MysqL> INSERT INTO t1 VALUES(1);MysqL教程

session 1 这时我们回滚session1@H_404_2@MysqL教程

MysqL> rollback;
query OK,0 rows affected (0.00 sec)
MysqL教程

发现session 2的insert成功,而session3检测到死锁回滚@H_404_2@MysqL教程

session 2 query OK,1 row affected (28.87 sec)MysqL教程

session 3 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionMysqL教程

死锁原因分析： @H_404_2@MysqL教程

首先session1插入一条记录,获得该记录的排它锁,这时session2和session3都检测到了主键冲突错误,但是由于session1并没有提交,所以session1并不算插入成功,于是它并不能直接报错吧,于是session2和session3都申请了该记录的共享锁,这时还没获取到共享锁,处于等待队列中.这时session1 rollback了,也就释放了该行记录的排它锁,那么session2和session3都获取了该行上的共享锁.而session2和session3想要插入记录,必须获取排它锁,但由于他们本身都拥有了共享锁,于是永远无法获取到排它锁,于是死锁就发生了.如果这时session1是commit而不是rollback的话,那么session2和session3都直接报错主键冲突错误.查看死锁日志也是一目了然MysqL教程

MysqL教程

insert导致的死锁现象2 @H_404_2@MysqL教程

另外一个类似的死锁是session1删除了ID=1的记录并未提交,这时session2和session3插入ID=1的记录.这时session1 commit了,session2和session3必要insert的话,就必要获取排它锁,那么死锁也就发生了；session1 rollback,则session2和session3报错主键冲突.这里不再做演示.MysqL教程

INSERT ... ON DUPliCATE KEY UPDATEMysqL教程

这种sql和insert加锁的不同的是,如果检测到键冲突,它直接申请加排它锁,而不是共享锁.MysqL教程

replace@H_404_2@MysqL教程

replace *** 作如果没有检测到键冲突的话,那么它的加锁策略和insert相似；如果检测到键冲突,那么它也是直接再申请加排它锁MysqL教程

INSERT INTO T SELECT ... FROM S WHERE ...MysqL教程

在T表上的加锁策略和普通insert一致,另外还会在S表上的相关记录上加共享的next-key lock.(如果是可重复读模式,则不会加锁)MysqL教程

CREATE table ... SELECT ...在select的表上加共享的next-key lockMysqL教程

自增ID的加锁策略@H_404_2@MysqL教程

当一张表的某个字段是自增列时,innodb会在该索引的末位加一个排它锁.为了拜访这个自增的数值,需要加一个表级锁,不过这个表级锁的持续时间只有当前sql,而不是整个事务,即当前sql执行完,该表级锁就释放了.其他session无法在这个表级锁持有时插入任何记录.MysqL教程

外键检测的加锁策略@H_404_2@MysqL教程

如果存在外键约束,任何的insert,delete将会检测约束条件,将会在相应的记录上加共享的record lock,无论是否存在外键冲突.MysqL教程

以上这篇详谈innodb的锁(record,Next-Key lock) 便是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持内存溢出PHP.MysqL教程