InnoDB锁机制-转载

InnoDB锁机制

1. 锁类型

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

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

1.1. 共享锁和排他锁

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

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

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

S 锁和 X 锁都是行级锁。

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

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 在不同设置下对自增长的影响:

innodb_autoinc_lock_mode = 0

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

innodb_autoinc_lock_mode = 1(默认值)

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

innodb_autoinc_lock_mode = 2

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

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

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

2. 锁的算法

InnoDB存储引擎行锁的算法

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

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 ;;

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;

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 ;;

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 ;;...

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:如下分不同情况

3.1. id主键

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

 

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语句的存在。违背同一条记录的更新/删除需要串行执行的约束。

 

3.3. id非唯一索引

加锁步骤如下:

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

幻读解决: 这幅图中多了个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 的记录,一定不会插入进来。

3.4. id无索引

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

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

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

 

4. 死锁分析与案例

死锁避免的一些办法:

  1. 如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低死锁机会。
  2. 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率;
 
上一篇:C# Serializable对象序列化的作用


下一篇:MySQL(InnoDB)是如何处理死锁的