InnoDB 什么时候加锁

查看锁信息

1
2
3
4
5
6
7
8
9
10

-- 查看当前的锁
select * from performance_schema.data_locks\G;

select * from performance_schema.data_locks_wait\G;

-- 查看处于等待状态的锁
SELECT * FROM sys.innodb_lock_waits\G;

-- 在TRANSACTIONS部分可查看事务创建了几个锁结构
SHOW ENGINE INNODB STATUS\G;

行锁

record locks

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

除了 for update 还有 SELECT … LOCK IN SHARE MODE;

无索引

先执行事务1后执行事务2，事务2不会卡住

两个事务都能成功加锁，Read committed级别下会给supremum(伪最大记录)加行锁，这个行锁不会互相阻塞。

两个事务都能成功加锁，RR下会加(伪最小记录，伪最大记录)gap lock，和伪最大记录行锁。

1
2
3
4
5
6
7
8
9

create table t(a int);

-- 事务1
begin;
select * from t where a=1 for update; 

-- 事务2
begin;
select * from t where a=1 for update;

随后插入数据，

事务1会进行全表扫描，在RR中会加[a=1, 最大]，即(最小, a=1)gap，a=1行锁，(a=1, 最大)gap，最大 行锁

事务2在扫描数据时会给所有扫描到的数据加锁，当尝试获取a=1的行锁时导致阻塞。

1
2
3
4
5
6
7
8
9

insert into t values(10);

-- 事务1
begin;
select * from t where a = 1 for update;

-- 事务2
begin;
select * from t where a = 1 for update; 或者 select * from t where a = 2 for update; 都卡住

就算插入许多条数据, select * from t where a = 5 for update; 也会加许多行锁。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

delimiter $$
DROP PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE proc_batch_insert()
BEGIN
    DECLARE i INT;
    SET i=1;
    WHILE i < 100000 DO
            INSERT INTO t VALUES(i);
            SET i=i+1;
        END WHILE;
END $$

delimiter ;
call proc_batch_insert();

唯一索引

都不会卡住

Read committed级别下会给supremum(伪最大记录)加行锁，这个行锁不会互相阻塞。

RR级别下会加(伪最小记录，伪最大记录)gap lock，和伪最大记录行锁。后续如果插入的话会阻塞。

1
2
3
4
5
6
7
8
9

create table t(a int primary key);

-- 事务1
begin;
select * from t where a=1 for update; 

-- 事务2
begin;
select * from t where a=1 for update;

随后插入数据，

事务1在RR中会加(最小, 10)gap

事务2会加(最小, 10)gap，因此不会阻塞。

1
2
3
4
5
6
7
8
9

insert into t values(10);

-- 事务1
begin;
select * from t where a = 1 for update;

-- 事务2
begin;
select * from t where a = 1 for update; 或者 select * from t where a = 2 for update; 都不会卡住

事务1在RR中会加(10, 最大]

事务2会加(10, 最大]，由于最大行锁不会阻塞，因此这里不会阻塞。

-- 事务1
begin;
select * from t where a = 20 for update;

-- 事务2
begin;
select * from t where a = 20 for update;

duplicate key

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

死锁

1
2
3
4
5
6
7
8
9
10

-- 1
begin; 
insert into tp(a) values(10); 
-- 2
begin;
insert into tp(a) values(20);
-- 1
insert into tp(a) values(20); -- 冲突，帮助2添加X锁，自己等待S锁
-- 2
insert into tp(a) values(10); -- 冲突，帮助1添加X锁，自己等待S锁。互相等待导致死锁

至于为什么要加锁：https://stackoverflow.com/questions/21111676/mysql-duplicate-key-error-causes-a-shared-lock-set-on-the-duplicate-index-record

1
2
3
4
5
6
7
8
9
10
11

-- Transaction A
BEGIN TRANSACTION;
INSERT INTO mytable VALUE(1); -- fails as "duplicate"

-- Transaction B
BEGIN;
DELETE FROM mytable WHERE field = 1; -- must be put on hold, see below

-- Transaction A
-- transaction is still in progress
INSERT INTO mytable VALUE(1); -- must fail to stay consistent with the previous attempt。

所以说加锁是为了不让其它事务去删除/修改它，导致出现同一个事务中插入结果不一致的情况。

但是为什么不用排他锁，如果用的是排他锁，这会导致其它事务无法获取共享锁

gap locks

锁住某条记录的前面的空隙，在这条记录之前的空隙插入记录时需要获取插入意向锁，此时会阻塞。gap lock之间不会互相阻塞，它仅阻止其它事务插入。InnoDB的lock_rec_has_to_wait方法实现，可以看到的LOCK_GAP类型的锁只要不带有插入意向标识，不必等待其它锁（表锁除外）。只有RR级别和串行化才有gap lock（不过外键和唯一键重复检查仍然会有）。

结果不唯一就会使用gap lock

无索引的情况：

1
2
3
4
5
6
7
8
9
10
11
12

create table t(a int);
insert into t values(1);
begin; -- trans1
begin; -- trans2

情况1：
insert into t values(0); -- trans1
select * from t where a = 1 for update; -- trans2 阻塞

情况2：
insert into t values(2); -- trans1
select * from t where a = 1 for update; -- trans2 也阻塞（因为这里进行了全表扫描，每行数据都有gap lock，即这里是next-key）

非唯一索引的情况：

1
2
3
4
5
6
7
8
9
10
11
12
13

create table tk(a int, index k_a(a));
insert into tk values(0), (2), (2), (4);

begin; -- trans1
begin; -- trans2

情况1：
insert into t values(0); -- trans1
select * from t where a = 2 for update; -- trans2 阻塞

情况2：
insert into t values(2); -- trans1
select * from t where a = 2 for update; -- trans2 不阻塞（因为这里只为a=2的记录加了gap lock）

If a is not indexed or has a nonunique index, the statement does lock the preceding gap.

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.（防止幻读）（如果结果是唯一的就不可能使用到gap lock）(This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.就是说如果使用的是唯一复合索引的一部分列，也能够使用到gap lock。很好理解因为只是用到了一部分列那么结果不一定是唯一的)

innodb_locks_unsafe_for_binlog：静态参数，默认为0，表示启动gap lock（实际会用next-key lock），如果设置为1，表示禁用gap lock，这时mysql就只有record lock了，不过值得注意的是，即使了设置了1，关于外键和唯一键重复检查方面用到的gap lock依旧有效。

Insert Intention Locks

一条记录前的gap被一个事务锁定后，另外两个事务分别在这个gap里面插入不同的数据，这时它们需要获取这条记录的插入意向锁，且都能获取成功，gap被第一个事务释放时它们可以同时插入。为什么不用gap lock，因为插入意向锁在插入不同数据时可以同时进行。插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。

隐式锁

一个事务对新插入的记录（delete与update不是这样）可以不显式的加锁（生成一个锁结构），但是由于事务id，相当于加了一个隐式锁。别的事务在对这条记录加S锁或者X锁时，由于隐式锁的存在，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构后进入等待状态。

例子：

一个事务在执行INSERT操作时，如果即将插入的间隙已经被其他事务加了gap锁，那么本次INSERT操作会阻塞，并且当前事务会在该间隙上加一个插入意向锁，否则一般情况下INSERT操作是不加锁的。那如果一个事务A首先插入了一条记录（此时并没有与该记录关联的锁结构），然后另一个事务：

• 立即使用SELECT ... LOCK IN SHARE MODE语句读取这条记录，也就是在要获取这条记录的S锁，或者使用SELECT ... FOR UPDATE语句读取这条记录，也就是要获取这条记录的X锁

  如果允许这种情况的发生，那么可能产生脏读问题。(由于A回滚)

• 立即修改这条记录，也就是要获取这条记录的X锁，该咋办？

  如果允许这种情况的发生，那么可能产生脏写问题(由于A回滚)。

这时候事务id要起作用。把聚簇索引和二级索引中的记录分开看一下：

• 情景一：对于聚簇索引记录来说，有一个trx_id隐藏列，该隐藏列记录着最后改动该记录的事务id。那么如果在当前事务中新插入一条聚簇索引记录后，该记录的trx_id隐藏列代表的的就是当前事务的事务id，如果其他事务此时想对该记录添加S锁或者X锁时，首先会看一下该记录的trx_id隐藏列代表的事务是否是当前的活跃事务，如果是的话，那么就帮助当前事务创建一个X锁（也就是为当前事务创建一个锁结构，is_waiting属性是false），然后自己进入等待状态（也就是为自己也创建一个锁结构，is_waiting属性是true）。
• 情景二：对于二级索引记录来说，本身并没有trx_id隐藏列，但是在二级索引页面的Page Header部分有一个PAGE_MAX_TRX_ID属性，该属性代表对该页面做改动的最大的事务id，如果PAGE_MAX_TRX_ID属性值小于当前最小的活跃事务id，那么说明对该页面做修改的事务都已经提交了，否则就需要在页面中定位到对应的二级索引记录，然后回表找到它对应的聚簇索引记录，然后再重复情景一的做法。

表锁

实际上InnoDB本身并不会自动为表添加S锁或X锁（在执行ddl、dml时），在执行Lock table t read或Lock table t write时才会锁整个表。

在两个会话中分别执行ddl与dml时会由Server层使用元数据锁实现阻塞操作。

在获取行S锁或行X锁时需要先获取表级的IS锁或IX锁，IS锁和IX锁只是为了后续在加表级别的S锁和X锁时方便判断表中是否有已经被加S锁或X锁的记录。

AUTO-INC锁是在向一个有auto-inc列的表中插入新数据时需要获取的锁，并且插入成功就自动释放不需要等待事务提交。
并且它还有一种轻量级形式，即在获得锁之后获取所有需要插入的记录的auto_inc值，不需要等待数据插入就将锁释放。
innodb_autoinc_lock_mode值为0时，一律采用AUTO-INC锁；当innodb_autoinc_lock_mode值为2时，一律采用轻量级锁；当innodb_autoinc_lock_mode值为1时，两种方式混着来

意向锁

• 意向锁都是表锁
• 意向锁用于表示表内的某些记录是否被加了 S/X 锁
• 意向共享锁（IS）：事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

意向锁之间不互斥，意向锁与非意向锁之间的互斥关系与对应非意向锁之间的互斥关系一致。