InnoDB锁学习

发布时间：2022-03-25 17:11:36 所属栏目：MySql教程来源：互联网

导读：Innodb实现标准行级锁，有2种模式的锁： l S (shared locks) 共享锁:允许拥有锁的事务读一行数据 l X (exclusive locks) 排他锁:允许有用锁的事务更新或删除一行数据如果一个事务T1在行r拥有一个S共享锁，从不同的事务T2请求锁定行r，处理如下： l 事务T2

Innodb实现标准行级锁，有2种模式的锁：
l S (shared locks)         共享锁:允许拥有锁的事务读一行数据

l X (exclusive locks)     排他锁:允许有用锁的事务更新或删除一行数据

如果一个事务T1在行r拥有一个S共享锁，从不同的事务T2请求锁定行r，处理如下：

l 事务T2能立即获得行r的S共享锁，因此，T1和T2都在行r上持有S共享锁

l 事务T2不能获得行r的X排它锁

如果一个事务T1在行r持有x排它锁，其他事务T2无法获得任何类型的锁。
InnoDB锁学习

InnoDB意向锁
Innodb支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，innodb支持一种额外的锁方式，称为意向锁（intention locks）。

Innodb意向锁为表级别的锁，设计的主要目的主要是为了在下一个事务中揭示下一行将被请求的锁类型。

InnoDB中有2中类型的意向锁：

? Intention shared (IS): Transaction T intends to set S locks on individual rows in table t.

                                               事务T想对表t的某些行设置共享锁

? Intention exclusive (IX): Transaction T intends to set X locks on those rows.

                                               事务T想对表t的某些行设置排它锁

例如：SELECT ... LOCK IN SHARE MODE 设置了一个IS锁

        SELECT ... FOR UPDATE 设置一个IX锁

意向锁协议如下：

? 一个事务在可以获得表t的某些行的S共享锁，必须先获取表t的IS意向共享锁

? 一个事务在可以会的表t的某些行的X排它锁，必须先获取表t的IX意向排它锁

Innodb锁兼容性矩阵

意向锁不会堵塞除了全表操作（例：LOCK TABLES ...WRITE）以外的任何请求，其意向锁的主要目的是显示某些操作锁定了某行，或者将要锁定某行。

意向锁的存在价值在于在定位到特定的行所持有的锁之前，提供一种更粗粒度的锁，可以大大节约引擎对于锁的定位和处理的性能，因为在存储引擎内部，锁是由一块独立的数据结构维护的，锁的数量直接决定了内存的消耗和并发性能。例如，事务A对表t的某些行修改（DML通常会产生X锁），需要对t加上意向排它锁，在A事务完成之前，B事务来一个全表操作（alter table等），此时直接在表级别的意向排它锁就能告诉B需要等待（因为t上有意向锁），而不需要再去行级别判断

意向锁实际上可以理解为一种“暗示”未来需要什么样行级锁：

IS表示未来可能需要在这个表的某些记录上加共享锁

IX表示未来可能需要在这个表的某些记录上加排他锁。

意向锁是表级别的，IS和IX锁之间相互并不冲突，但与表级S/X锁冲突。

在对记录加S锁或者X锁时，必须保证其在相同的表上有对应的意向锁或者锁强度更高的表级锁

InnoDB记录锁、GAP锁和Next-Key锁
Innodb的行锁的类型：

? Record lock: 锁定一个索引记录

? Gap lock: 锁定一个范围，不包含记录本身

? Next-key lock: Gap lock+ Record lock，锁定一个范围，并且锁定记录本身

Record Locks总是锁住索引记录，即使表没有定义索引，Innodb会创建一个隐试的聚集索引来锁定。

Next-key Locks 默认Innodb使用RR的隔离级别。在这种情况下，InnoDB使用next-key锁机制来查询数据和索引扫描，防止幻象问题

Next-key locking结合了index-row锁和Gap锁。当搜索和查询索引操作时，InnoDB使用行锁的方式，在相应的索引记录上设置共享或者排他锁。因此，行级别的锁实际上是索引记录锁。而Next-Key锁是索引记录锁加范围锁。如果一个会话在索引记录R上加了一个共享或者排他锁，其他会话不能立即插入一个新的索引记录在索引记录R之前的间隙中。

假设一个索引的值有10, 11, 13, 和20，那么该索引可能被Next-Key Locking的区间为：

最后一个间隔范围，Next-Key 实际上锁定的范围是仅仅是最大索引值后面的范围。

当操作的索引是唯一的情况下，Innodb会对Next-key Locking进行优化，将其降级为Record Lock,只锁住索引本身，而不是锁定范围。

如下：

Session A

Session B

Session A>drop table t;
Session A>create table t (a int primary key);
Session A>insert into t select 1;
Session A>insert into t select 2;
Session A>insert into t select 5;
Session A>begin;
Session A>select * from t where a=5 for update;
+---+
| a |
+---+
| 5 |
+---+
1 row in set (0.00 sec)

Session A>begin;
Query OK, 0 rows affected (0.00 sec)

Session A>insert into t select 4;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
#此时并没有出现等待，能插入并且提交
Session A>commit;
Query OK, 0 rows affected (0.00 sec)

#session A提交上面的事务                          Session A>commit;
Query OK, 0 rows affected (0.00 sec)

上面的实验中，表t有1,2，5三个值。在session A中对a=5进行锁定读X锁定，但因为字段a是主键（唯一索引），因此此时锁定的仅是5这个索引记录，而不是（2，5）]这个范围，这样在Session B中能顺利的插入a=4（4在（2,5]之间）而不会阻塞。上述正式因为Next-Key Lock对唯一索引的记录降级为record lock的原因，从而可以提高应用的并发性。

当查询的列是非唯一的索引时，情况如下：

Session A

Session B

Session A>drop table t;
Session A>create table t(id int primary key,v int ,key (v));
Session A>insert into t values (2,20),(6,13),(10,15),(9,15),(3,20),(8,25);
Session A>begin;
Session A>select * from t where v=20 for update;

此时V是非唯一索引，for update锁定读会将满足V=20对应的聚集索引3相应的行加上X行锁，而索引V则加Next-key Lock,锁定范围是[15,20)以及下一个范围(20,25)

注意：右边的Session B看到15被堵塞，而20没有被堵塞；所以，推测此处Gap Lock的范围是【15,20）和（20,25）。这与官当上的http://dev.mysql.com/doc/refman/5.7/en/innodb-record-level-locks.html举的例子的可能的间隔范围刚好闭开区间是反的

#18在[15,20)之间，被堵塞
Session B>insert into t select 11,18;
#20记录上持有辅助索引的X锁，被堵塞
Session B>insert into t select 11,20;
#21在(20,25)之间被堵塞
Session B>insert into t select 11,21;
#15在[15,20)之间，被堵塞
Session B>insert into t select 11,15;
#25插入成功
Session B>insert into t select 11,25;
Query OK, 1 row affected (0.00 sec)                                   #14和26在间隙范围外都能顺利插入成功
Session B>insert into t select 12,14;
Query OK, 1 row affected (0.00 sec)
Session B>insert into t select 13,26;
Query OK, 1 row affected (0.00 sec)

Gap Locks 当使用唯一索引查询唯一的行时，不需要Gap locks.

前面next-key的例子表明gap可能覆盖一个单独的索引值，多个索引值，或可能是空的。

例如：下面的语句只需要在id为100的行上使用一个index-record lock，而不会影响其他会话插入数据：

SELECT * FROM child WHERE id = 100 for update;

如果ID不是索引或者不是一个uniq index，该语句则使用Gap Locking.

插入意向Gap锁（insert intention gap lock）在Insert操作之前被设置。这种锁的目的是：当多个事务插入数据到相同的索引间隙，只要他们插入的索引间隙内的位置不同，则不需要相互等待。假如有索引值4和7，某个事务视图在4和7的间隙间插入5和6，在插入的行会分配插入意向Gap锁，但因为5和6的行并不冲突，所以不会堵塞对方。

注意：不同的事务在一个间隙（Gap）上可以持有互斥的锁。例如：当事务B在某个Gap上拥有一个排他Gap锁,事务A在相同的Gap上可以持有共享Gap锁。此处允许锁冲突共存的原因是：当从一个索引上purge一个记录，其他事务对此记录加的Gap锁会被被合并。

Gap锁在Innodb中是”完全禁止的”。这意味着它们只能防止其他事务在一个Gap中的插入操作。因此，排他Gap锁和共享Gap锁具有同样的效果。

Disabling Gap Locking

Gap锁可以被完全关闭方法是设置事务隔离级别修改为RC，或者开启innodb_locks_unsafe_for_binlog系统变量（此选项5.6和5.7已经被弃用）。关闭Gap的情况下，除了在外键一致性检查和逐渐冲突检查的时候，gap锁均不生效。

RC隔离级别和开启innodb_locks_unsafe_for_binlog参数也会带来其他影响，比如:在mysql分析where条件之后，未匹配行的记录锁就会被释放（违反了2PL：Two Parsing Lock原则）例如：在update中，innodb执行"半一致性"读，这样，最新的提交版本被告知mysql，然后由mysql决定行是否匹配执行Update操作的where条件。

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

数据库中间件ProxySQL	zabbix 一次悲惨的分区
mysql如何实现数据备份	建立数据表主键外键