InnoDB锁机制【vnsc5858威尼斯城官网】

时间:2020-04-24 02:27来源:计算机教程
select * from lock_example where id = 20 for update; 2.4. 插入意向锁 插入意向锁是一种在数据行插入前设置的gap锁。这种锁用于在多事务插入同一索引间隙时,如果这些事务不是往这段gap的同一位
select * from lock_example where id = 20 for update;

2.4. 插入意向锁

插入意向锁是一种在数据行插入前设置的gap锁。这种锁用于在多事务插入同一索引间隙时,如果这些事务不是往这段gap的同一位置插入数据,那么就不用互相等待。假如有4和7两个索引记录值。不同的事务尝试插入5和6的值。在不同事务获取分别的 X 锁之前,他们都获得了4到7范围的插入意向锁,但是他们无需互相等待,因为5和6这两行不冲突。

例如:客户端A和B,在插入记录获取互斥锁之前,事务正在获取插入意向锁。

客户端A创建了一个表,包含90和102两条索引记录,然后去设置一个互斥锁在大于100的所有索引记录上。这个互斥锁包含了在102记录前的gap锁。

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
 ----- 
| id  |
 ----- 
| 102 |
 ----- 

客户端B 开启一个事务在这段gap上插入新纪录,这个事务在等待获取互斥锁之前,获取了一把插入意向锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

插入意向锁 使用 SHOW ENGINE INNODB STATUS 输出如下:

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

因为是插入操作,虽然是插入同一个区间,但是插入的记录并不冲突,所以使用的是插入意向锁,此处A事务并不会阻塞B事务。

2.3. Next-Key锁

Next-Key Lock是结合了Gap Lock 和 Record Lock的一种锁算法。

当扫描表的索引时,InnoDB以这种形式实现行级的锁:遇到匹配的的索引记录,在上面加上对应的 S 锁或 X 锁。因此,行级锁实际上是索引记录锁。如果一个事务拥有索引上记录 r 的一个 S 锁或 X 锁,另外的事务无法立即在 r 记录索引顺序之前的间隙上插入一条新的记录。

假设有一个索引包含值:10,11,13和20。下列的间隔上都可能加上一个Next-Key 锁(左开右闭)

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

在最后一个区间中,Next-Key锁 锁定了索引中的最大值到 正无穷。

默认情况下,InnoDB启用 RR 事务隔离级别。此时,InnoDB在查找和扫描索引时会使用 Next-Key 锁,其设计的目的是为了解决『幻读』的出现。

当查询的列是唯一索引情况下,InnoDB会对Next-Key Lock进行优化,降级为Record Lock,即只锁住索引本身,而不是范围。

next-key 锁 使用 SHOW ENGINE INNODB STATUS 输出如下:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

默认情况下,innodb使用next-key locks来锁定记录。但当查询的索引含有唯一属性的时候,Next-Key Lock 会进行优化,将其降级为Record Lock,即仅锁住索引本身,不是范围。

1.3. 自增长锁

InnoDB中,对每个含有自增长值的表都有一个自增长计数器(aito-increment counter)。当对含有自增长计数器的表进行插入操作时,这个计数器会被初始化。执行如下语句会获得自增长的值

SELECT MAX(auto_inc_col) FROM t FOR UPDATE;

插入操作会依据这个自增长的计数器值加1赋予到自增长列。这种实现方式是AUTO_INC Locking。这种锁采用了一种特殊的表锁机制,为提高插入的性能,锁不是在一个事务完成后释放,而是在完成对自增长值插入的SQL语句后立即释放。虽然AUTO-INC Locking一定方式提升了并发插入的效率,但还是存在性能上的一些问题:

  • 首先,对自增长值的列并发插入性能较差,事务必须等待前一个插入SQL的完成
  • 其次,对于 insert... select 的大数据量插入会影响插入的性能,因为另一个插入的事务会被阻塞

InnoDB提供了一种轻量级互斥量的自增长实现机制,大大提高了自增长值插入的性能。提供参数innodb_autoinc_lock_mode来控制自增长锁使用的算法,默认值为1。他允许你在可预测的自增长值和最大化并发插入操作之间进行权衡。

插入类型的分类:

插入类型 说明
insert-like 指所有的插入语句,例如:insert、replace、insert ... select、replace... select、load data
simple inserts 指再插入前就确定插入行数的语句。例如:insert、replace等。注意:simple inserts不包含 insert ... on duplicate key update 这类sql语句
bulk inserts 指在插入前不能确定得到插入行数的语句,例如:insert ... select、 replace ... select、load data
mixed-mode inserts 指插入中有一部分的值是自增长的,一部分是确定的。例如:insert into t1(c1, c2) values (1, 'a'), (NULL, 'b'), (5, 'c'), (NULL,'d'); 也可以指 insert ... on duplicate key update 这类sql语句

innodb_autoinc_lock_mode 在不同设置下对自增长的影响:

共享锁,读取数据时加S锁 排他锁,修改数据时加X锁

innodb_autoinc_lock_mode = 0

MySQL 5.1.22版本之前自增长的实现方式,通过表锁的AUTO-INC Locking方式

  1. 按锁的互斥程度来划分,可以分为共享、排他锁;

5.参考

这个SQL语句会封锁区间(8,15),以阻止其他事务插入id位于该区间的记录。

3.2. id唯一索引

id不是主键,而是一个唯一的二级索引,主键是name列。加锁步骤如下:

  1. 会选择走id列的索引进行where条件的过滤。找到id=10的记录后,首先将唯一索引上id=10的索引记录加上 X 锁
  2. 同时,根据读取到的name列回主键索引(聚簇索引),然后将聚簇索引上的 name='d' 对应的主键索引记录添加 X 锁

聚簇索引加锁的原因:如果并发的一个SQL是通过主键索引来更新:update t1 set id = 100 where name = 'd'; 此时,如果delete语句没有将主键索引上的记录加锁,那么并发的update就会感知不到delete语句的存在。违背同一条记录的更新/删除需要串行执行的约束。

vnsc5858威尼斯城官网 1

IS IX IS 兼 容 兼 容 IX 兼 容 兼 容

1.1. 共享锁和排他锁

InnoDB实现了两种标准的行级锁:共享锁(S)和排他锁(X)

共享锁:允许持有该锁的事务读取行记录。如果事务 T1 拥有记录 r 的 S 锁,事务 T2 对记录 r 加锁请求:若想要加 S 锁,能马上获得;若想要获得 X 锁,则请求会阻塞。

排他锁:允许持有该锁的事务更新或删除行记录。如果事务 T1 拥有记录 r 的 X 锁,事务 T2 对记录 r 加锁请求:无论想获取 r 的 S 锁或 X 锁都会被阻塞。

S 锁和 X 锁都是行级锁。

mysql锁详解

1.2. 意向锁

InnoDB 支持多粒度的锁,允许一行记录同时持有兼容的行锁和表锁。意向锁是表级锁,表明一个事务之后要获取表中某些行的 S 锁或 X 锁。

InnoDB中使用了两种意向锁

  • 意向共享锁(IS):事务 T 想要对表 t 中的某些记录加上 S 锁
  • 意向排他锁(IX):事务 T 想要对表 t 中的某些记录加上 X 锁

例如:

  • SELECT ... LOCK IN SHARE MODE,设置了 IS 锁
  • SELECT ... FOR UPDATE,设置了 IX 锁

意向锁协议如下所示:

  • 在一个事务对表 t 中某一记录 r 加 S 锁之前,他必须先获取表 t 的 IS 锁
  • 在一个事务对表 t 中某一记录 r 加 X 锁之前,他必须先获取表 t 的 IX 锁

这些规则可以总结为下面的图表(横向表示一个事务已经获取了对应的锁,纵向表示另外一个事务想要获取对应的锁):

IX,IS是表级锁,不会和行级的X,S锁发生冲突。只会和表级的X,S发生冲突

X IX S IS
X 不兼容 不兼容 不兼容 不兼容
IX 不兼容 兼容 不兼容 兼容
S 不兼容 不兼容 兼容 兼容
IS 不兼容 兼容 兼容 兼容

当请求的锁与已持有的锁兼容时,则加锁成功;如果冲突的话,事务将会等待已有的冲突的锁释放

IX 和 IS 锁的主要目的是表明:某个请求正在或者将要锁定一行记录。意向锁的作用:意向锁是在添加行锁之前添加。当再向一个表添加表级 X 锁的时候

  • 如果没有意向锁的话,则需要遍历所有整个表判断是否有行锁的存在,以免发生冲突
  • 如果有了意向锁,只需要判断该意向锁与即将添加的表级锁是否兼容即可。因为意向锁的存在代表了,有行级锁的存在或者即将有行级锁的存在。因而无需遍历整个表,即可获取结果

意向锁使用 SHOW ENGINE INNODB STATUS 查看当前锁请求的信息:

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

插入意向锁,是间隙锁(Gap Locks)的一种,它是专门针对insert操作的。多个事务,在同一个索引,同一个范围区间插入记录时,如果插入的位置不冲突,不会阻塞彼此。

3.1. id主键

将主键上,id=10的记录加上 X 锁

vnsc5858威尼斯城官网 2

create table lock_example(id smallint(10),name varchar(20),primary key id)engine=innodb;

3.3. id非唯一索引

加锁步骤如下:

  1. 通过id索引定位到第一条满足条件的记录,加上 X 锁
  2. 这条记录的间隙上加上 GAP锁
  3. 根据读取到的name列回主键聚簇索引,对应记录加上 X 锁
  4. 返回读取下一条,重复进行... 直到第一条不满足 where id = 10 条件的记录 [11, f],此时不需要加 X 锁,仍旧需要加 GAP 锁。结束返回

vnsc5858威尼斯城官网 3

幻读解决:
这幅图中多了个GAP锁,并不是加到记录上的,而是加在两个记录之间的位置。GAP 锁就是 RR 隔离级别相对于 RC 隔离级别,不会出现幻读的关键。GAP锁保证两次当前读之前,其他的事务不会插入新的满足条件的记录并提交。

所谓幻读,就是同一个事务,连续做两次当前读 (例如:select * from t1 where id = 10 for update;),那么这两次当前读返回的是完全相同的记录 (记录数量一致,记录本身也一致),第二次的当前读,不会比第一次返回更多的记录 (幻象)。

如图中所示:考虑到B 树索引的有序性,有哪些位置可以插入新的满足条件的项 (id = 10):

  • [6,c] 之前,不会插入id=10的记录
  • [6,c] 与 [10,b] 间,可以插入 [10, aa]
  • [10,b] 与 [10,d] 间,可以插入[10,bb],[10,c]
  • [10,d] 与 [11, f] 间,可以插入[10,e],[10,z]
  • [11,f] 之后,不会插入id=10的记录

因此,不仅将满足条件的记录锁上 (X锁),同时还通过GAP锁,将可能插入满足条件记录的3个GAP给锁上,保证后续的Insert不能插入新的id=10的记录,也就杜绝了同一事务的第二次当前读,出现幻象的情况。

当id是唯一索引时,则不需要加GAP锁。因为唯一索引能够保证唯一性,对于where id = 10 的查询,最多只能返回一条记录,而且新的 id= 10 的记录,一定不会插入进来。

本文主要论述关于mysql锁机制,mysql版本为5.7,引擎为innodb,由于实际中关于innodb锁相关的知识及加锁方式很多,所以没有那么多精力罗列所有场景下的加锁过程并加以分析,仅根据现在了解的知识,结合官方文档,说说自己的理解,如果发现有不对的地方,欢迎指正。

3. SQL加锁分析

给定两个SQL来分析InnoDB下加锁的过程:

SQL1:select * from t1 where id = 10;

SQL2:delete * from t1 where id = 10;

事务隔离级别为默认隔离级别Repeatable Read。而对于id不同的索引类型,会有不同的结论。(总结自何登成大神的 MySQL 加锁处理分析

SQL1:在RC和RR下,因为MVCC并发控制,select操作不需要加锁,采用快照读。读取记录的可见版本(可能是历史版本)

针对SQL2:如下分不同情况

2、意向锁又分为:

1. 锁类型

锁是数据库区别与文件系统的一个关键特性,锁机制用于管理对共享资源的并发访问。
InnoDB使用的锁类型,分别有:

  • 共享锁(S)和排他锁(X)
  • 意向锁(IS和IX)
  • 自增长锁(AUTO-INC Locks)

前言

3.4. id无索引

当id无索引时,只能进行全表扫描,加锁步骤:

  1. 聚簇索引上的所有记录都加 X 锁
  2. 聚簇索引每条记录间的GAP都加上了GAP锁。

如果表中有上千万条记录,这种情况是很恐怖的。这个情况下,MySQL也做了一些优化,就是所谓的semi-consistent read。semi-consistent read开启的情况下,对于不满足查询条件的记录,MySQL会提前放锁。针对上面的这个用例,就是除了记录[d,10],[g,10]之外,所有的记录锁都会被释放,同时不加GAP锁

vnsc5858威尼斯城官网 4

事务A先执行,还未提交: insert into t(name) values(xxx);

4. 死锁分析与案例

死锁避免的一些办法:
1、如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低死锁机会。
2、在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率;

好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。

2. 锁的算法

InnoDB存储引擎行锁的算法

  • Record Locks:单个行记录上的锁
  • Gap Locks:间隙锁,锁定一个范围,不包含记录本身
  • Next-Key Locking:Record Locks Gap Locks,锁住一个范围 记录本身
  • Insert Intention Locks:插入易向锁

表锁:意向锁(IS锁、IX锁)、自增锁; 行锁:记录锁、间隙锁、临键锁、插入意向锁;

innodb_autoinc_lock_mode = 1(默认值)

对于『simple inserts』,该值会用互斥量(mutex)对内存中的计数器进行累加操作。对于『bulk inserts』会用传统的AUTO-INC Locking方式。这种配置下,如果不考虑回滚,自增长列的增长还是连续的。需要注意的是:如果已经使用AUTO-INC Locking方式去产生自增长的值,而此时需要『simple inserts』操作时,还需要等待AUTO-INC Locking的释放

事务B开始,执行如下语句,会阻塞:

innodb_autoinc_lock_mode = 2

对于所有『insert-like』自增长的产生都是通过互斥量,而不是AUTO-INC Locking方式。这是性能最高的方式。但会带来一些问题:

  • 因为并发插入的存在,每次插入时,自增长的值是不连续的
  • 基于statement-base replication会出现问题

因此,使用这种方式,任何情况下都需要使用row-base replication,这样才能保证最大并发性能和replication的主从数据的一致 |

事务B后执行: insert into t(name) values(ooo);

2.2. 间隙锁

间隙锁是加在索引记录间隙之间的锁,或者在第一条索引记录之前、最后一条索引记录之后的区间上加的锁。例如:SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 这条语句阻止其他的事务插入一条 t.c1 = 15 的记录,因为在10-20的范围值都已经被加上了锁。

间隙锁只在RR隔离级别中使用。如果一条sql使用了唯一索引(包括主键索引),那么不会使用到间隙锁

例如:id 列是唯一索引,下面的语句只会在 id = 100 行上面使用Record Lock,而不会关心别的事务是否在上述的间隙中插入数据。如果 id 列没有索引或者不是唯一索引,这个语句会在上述的间隙上加锁。

SELECT * FROM child WHERE id = 100 FOR UPDATE;

数据库隔离级别为RR,表中有如下数据:

2.1. 行锁

行锁是加在索引记录上的锁,例如:SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE,会阻止其他事务插入、更新或删除 t.c1 = 10 的记录

行锁总是在索引记录上面加锁,即使一张表没有设置任何索引,InnoDB会创建一个隐藏的聚簇索引,然后在这个索引上加上行锁。

行锁使用 SHOW ENGINE INNODB STATUS 的输出如下:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`

trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

虽然意向锁之间互相兼容,但是它与共享锁/排它锁互斥,其兼容互斥表如下:

1、意向锁是一个表级别的锁(table-level locking);

事务B后执行,也在10与20两条记录中插入了一行:

临键锁,是记录锁与间隙锁的组合,它的封锁范围,既包含索引记录,又包含索引区间。

举个例子(表依然是如上的例子lock_example),但是id为AUTO_INCREMENT,数据库表中数据为:

事务A执行如下语句,未提交:

Insert Intention Lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

排它锁是很强的锁,不与其他类型的锁兼容。这其实很好理解,修改和删除某一行的时候,必须获得强锁,禁止这一行上的其他并发,以保障数据的一致性。

此时事务B插入操作会阻塞,直到事务A提交。

间隙锁,它封锁索引记录中的间隔,或者第一条索引记录之前的范围,又或者最后一条索引记录之后的范围。依然是上面的例子,InnoDB,RR:

总的来说,InnoDB共有七种类型的锁:

select * from lock_examplewhere id between 8 and 15 for update;

加锁的语法为:

可以看到,一旦写数据的任务没有完成,数据是不能被其他任务读取的,这对并发度有较大的影响。对应到数据库,可以理解为,写事务没有提交,读相关数据的select也会被阻塞,这里的select是指加了锁的,普通的select仍然可以读到数据(快照读)。

6. 插入意向锁(Insert Intention Locks)

select ... lock in share mode; 要设置IS锁;select ... for update; 要设置IX锁;

事务要获得某些行的S/X锁,必须先获得表对应的IS/IX锁,意向锁仅仅表明意向,意向锁之间相互兼容,兼容互斥表如下:

编辑:计算机教程 本文来源:InnoDB锁机制【vnsc5858威尼斯城官网】

关键词: