InnoDB的七种锁

参考:https://www.phpmianshi.com/?id=22

InnoDB的七种锁

 

1. 自增锁（Auto-inc Locks）

2. 共享/排他锁（Shared and Exclusive Locks）

3. 意向锁（Intention Locks）

4. 插入意向锁（Insert Intention Locks）

5. 记录锁（Record Locks）

6. 间隙锁（Gap Locks）

7. 临键锁（Next-Key Locks）

 

相关名词

|--表级锁（锁定整个表）

|--页级锁（锁定一页）

|--行级锁（锁定一行）

|--共享锁（S锁，MyISAM 叫做读锁）

|--排他锁（X锁，MyISAM 叫做写锁）

|--悲观锁（抽象性，不真实存在这个锁）

|--乐观锁（抽象性，不真实存在这个锁）

 

默认事务隔离级别为可重复读（Repeated Read, RR）

 

InnoDB的锁，与索引类型，事务的隔离级别相关

 

自增锁

 

    自增锁是一种特殊的表级别锁（table-level lock）

    从MySQL 5.1开始，InnoDB中提供了一种轻量级互斥量的自增长实现机制

    同时InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式

    进而提高自增长值插入的性能

    

innodb_autoinc_lock_mode和插入类型有关，在介绍它之前，我们先来看看都有哪些插入类型

 

+ **insert-like**  任何会产生新记录的语句，都叫上insert-like，

比如：INSERT,INSERT ...SELECT,REPLACE,REPLACE ...SELECT,and  LOAD DATA，总之包括：simple-inserts,bulk-inserts,mixed-mode inserts.

 

+ **simple inserts**

插入的记录行数是确定的：比如：insert into values，replace

但是不包括： INSERT ... ON DUPLICATE KEY UPDATE.

 

+ **bulk inserts**

插入的记录行数不能马上确定的，比如： INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA

 

+ **mixed-mode inserts**

这些都是simple-insert，但是部分auto increment值给定或者不给定. 例子如下(where c1 is an AUTO_INCREMENT column of table t1):

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 

+ 另外一种 mixed-mode insert 就是 INSERT ... ON DUPLICATE KEY UPDATE

 

#### innodb_autoinc_lock_mode 的说明

 

 

    0 这个表示tradition 传统

    1 这个表示consecutive 连续 (默认)

    2 这个表示interleaved 交错 （MySQL 8.0下默认值为2）

 

1.1 tradition(innodb_autoinc_lock_mode=0) 模式:

　　

+ 它提供了一个向后兼容的能力

+ 在这一模式下，**所有的insert语句("insert like")** 都要在语句开始的时候得到一个表级的auto_inc锁，在语句结束的时候才释放这把锁，**注意呀，这里说的是语句级而不是事务级的，** 一个事务可能包含有一个或多个语句。

+ 它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。

+ 由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入。

 

 

 

1.2 consecutive(innodb_autoinc_lock_mode=1) 模式:

 

+ 对simple insert做了优化，由于simple insert一次性插入值的个数可以立马得到确定，所以mysql可以一次生成几个连续的值，用于这个insert语句，**该值会用互斥量mutex去对内存（dict_table_struct.autoinc）中的计数器进行累加操作。**mysqld重启后，从哪里得到AUTO_INCREMENT呢？内存值肯定是丢失了，实际上MySQL采用执行类似select max(id)+1 from t1;方法来得到AUTO_INCREMENT

+ **Mysql8.0优化:将自增主键的计数器持久化到redo log中。每次计数器发生改变，都会将其写入到redo log中。如果数据库发生重启，InnoDB会根据redo log中的计数器信息来初始化其内存值。**

+ 对于bulk inserts 还是使用传统表锁，该配置下如果不考虑回滚，对于自增列的增长还是连续的。

+ 深入思考：为什么这个模式要产生表级别的锁呢？因为：他要保证bulk insert自增id的连续性，防止在bulk insert的时候，被其他的insert语句抢走auto increment值。

+ 这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁

 

 

 

1.3 interleaved(innodb_autoinc_lock_mode=2) 模式

 

+ 由于这个模式下已经没有了auto_inc锁，对于所有insert-like 自增长值得产生都是通过互斥量mutex,所以这个模式下的性能是最好的

+ 但是它也有一个问题，就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的，所以基于statement-base replication会出现问题，因此使用这个模式，任何时候都应该使用row-base replication,才能保证最大的并发性能和主从一致性

 

 

    不要没事去更新一个auto_increment列的值，否则自增时，有可能跟你更新的值冲突，导致插入失败

 

共享/排他锁

 

 

    共享/排它锁是标准的行级锁(row-level locking)

 

 

1. 事务拿到某一行记录的共享S锁，才可以读取这一行；

 

2. 事务拿到某一行记录的排它X锁，才可以修改或者删除这一行；

 

3. 多个事务可以拿到一把S锁，读读可以并行；

 

4. 而只有一个事务可以拿到X锁，写写/读写必须互斥；

 

5. 共享/排它锁的潜在问题是，不能充分的并行，解决思路是数据多版本

 

意向锁

 

    意向锁，是一个表级别的锁(table-level locking)，但是却表示事务正在读或写某一行记录，而不是整个表。所以意向锁之间不会产生冲突，真正的冲突在加行锁时检查。

    为了方便检测表级锁和行级锁之间的冲突，就引入了意向锁，主要目的是解决表锁和行锁共存的问题。

    

 

1. 意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁

2. 意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁

 

##### 例如

 

```

select ... lock in share mode，要设置IS锁；

select ... for update，要设置IX锁；

```

 

    事务要获得某些行的S锁，必须先获得表的IS锁

    

    事务要获得某些行的X锁，必须先获得表的IX锁

 

+ 意向锁之间互不排斥，但除了 IS 与 S兼容外，意向锁会与共享锁/排他锁互斥

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

+ InnoDB支持多粒度锁，意向锁在保证并发性的前提下，实现了行锁和表锁共存

 

##### 解决的问题

 

+ 如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。

 

插入意向锁

 

    插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的）

    它是专门针对insert操作的。

    

 

+ 普通的Gap Lock 不允许 在 （上一条记录，本记录） 范围内插入数据

+ 插入意向锁Gap Lock 允许 在 （上一条记录，本记录） 范围内插入数据

+ 如果多个事务插入到相同的索引间隙中，如果它们不在间隙中的相同位置插入，则无需等待其他事务。比如说有索引记录4和7，有两个事务想要分别插入5，6，在获取插入行上的独占锁之前，每个锁都使用插入意图锁锁定4和7之间的间隙，但是不要互相阻塞，因为行是不冲突的，意向锁的涉及是为了插入的正确和高效。

 

##### 插入的过程

 

假设现在有记录 10， 30， 50 且为主键 ，需要插入记录 25 

 

1. 找到 小于等于25的记录 ，这里是 10

2. 找到 记录10的下一条记录 ，这里是 30

3. 判断 下一条记录30 上是否有锁

+ 判断 30 上面如果 没有锁 ，则可以插入

+ 判断 30 上面如果有Record Lock，则可以插入

+ 判断 30 上面如果有Gap Lock/Next-Key Lock，则无法插入，因为锁的范围是 (10, 30) /（10, 30] ；在30上增加insert intention lock（ 此时处于waiting状态），当 Gap Lock / Next-Key Lock 释放时，等待的事物（ transaction）将被 唤醒 ，此时 记录30 上才能获得 insert intention lock ，然后再插入 记录25

 

4. 注意：一个事物 insert 25 且没有提交，另一个事物 delete 25 时，记录25上会有 Record Lock

 

```

select * from a;

+----+

| a  |

+----+

| 5 |

| 10 |

| 13 |

| 20 |

+----+

```

 

```

//事务A

select * from a where a<=13 for update

```

 

```

//事务B

insert into a values (12)

```

 

```

//事务C

insert into a values (11)

```

事务A不提交，事务B，C被阻塞了，这时候查看

 

```

show engine innodb status\G

```

 

```

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 3, OS thread handle 140018685810432, query id 240 localhost root update

--等待插入的SQL

insert into a values(12)

------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:

--插入记录12的事物等待中，等待获得插入意向锁(lock_mode X locks gap before rec insert intention waiting)

 

```

 

此时事务A commit

 

 

+ 事务B输出：Query OK, 1 row affected (17.40 sec)

前提条件是insert操作的锁没有超时

+ 事务B未提交，再执行：show engine innodb status\G

 

```

2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 3, OS thread handle 140018685810432, query id 247 localhost root

```

 

    

记录锁

 

    记录锁，它封锁索引记录，例如： select * from t where id=1 for update;

 

+ 记录锁锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

+ 所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

 

 

间隙锁

 

    它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

    开区间，不包括双端端点

 

```

select * from t 

    where id between 7 and 15 

    for update;

```

 

这个SQL语句会封锁区间，以阻止其他事务id=10的记录插入。

 

    间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”

    如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

 

临键锁

 

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

    左闭右开区间

 

+ 但当查询唯一索引的时候，且记录存在，Next-Key Lock 会进行优化，将其降级为RecordLock，即仅锁住索引本身，不是范围。

+ 但当查询唯一索引的时候，且记录不存在，使用Gap Lock

+ 但当查询唯一索引的时候，使用范围查询 > <，使用Gap Lock + Record Lock,锁上界，不锁下界

 

 

    临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

 

 

### 总结：

 

    InnoDB使用共享锁，可以提高读读并发；

    排他锁，为了保证数据强一致，InnoDB使用强互斥锁，保证同一行记录修改与删除的串行性；

    InnoDB使用插入意向锁，可以提高插入并发；

    记录锁锁定索引记录；

    间隙锁锁定间隔，防止间隔中被其他事务插入；

    临键锁锁定索引记录+间隔，防止幻读；

 

普通select

加锁select

update与delete

insert

各类SQL语句分别加了什么锁？

 

select

 

普通的select是快照读，而select ... for update或select ... in share mode则会根据情况加不同的锁

 

如果在唯一索引上用唯一的查询条件时（ where id=1），加记录锁

 

否则，其他的查询条件和索引条件，加间隙锁（BETWEEN AND ）或Next-Key 锁（可重复隔离级别）

 

update与delete

 

如果在唯一索引上使用唯一的查询条件来update/delete，加记录锁

 

否则，符合查询条件的索引记录之前，都会加Next-Key 锁

 

注：如果update的是聚集索引，则对应的普通索引记录也会被隐式加锁，这是由InnoDB索引的实现机制决定的：普通索引存储PK的值，检索普通索引本质上要二次扫描聚集索引。

 

insert

 

insert和update与delete不同，它会用排它锁封锁被插入的索引记录，同时，会在插入区间加插入意向锁，但这个并不会真正封锁区间，也不会阻止相同区间的不同KEY插入。

 

### 查看锁等待情况

 

```

select * from information_schema.innodb_locks;

select * from information_schema.innodb_lock_waits;

select * from information_schema.innodb_trx;

```

 

##### 显示锁 vs 隐示锁

 

 

1. 显示锁(explicit lock)

    显示的加锁，在show engine innoDB status 中能够看到  ，会在内存中产生对象，占用内存

    eg: select ... for update , select ... lock in share mode

 

2. 隐示锁(implicit lock)

    implicit lock 是在索引中对记录逻辑的加锁，但是实际上不产生锁对象，不占用内存空间

 

3. 哪些语句会产生implicit lock 呢？

   eg: insert into xx values(xx)

   eg: update xx set t=t+1 where id = 1 ; 会对辅助索引加implicit lock

 

4. implicit lock 在什么情况下会转换成 explicit lock

  eg： 只有implicit lock 产生冲突的时候，会自动转换成explicit lock,这样做的好处就是降低锁的开销

  eg: 比如：我插入了一条记录10，本身这个记录加上implicit lock，如果这时候有人再去更新这条10的记录，那么就会自动转换成explicit lock

 

5. 数据库怎么知道implicit lock的存在呢？如何实现锁的转化呢？

  1. 对于聚集索引上面的记录，有db_trx_id,如果该事务id在活跃事务列表中，那么说明还没有提交，那么implicit则存在

  2. 对于非聚集索引：由于上面没有事务id，那么可以通过上面的主键id，再通过主键id上面的事务id来判断，不过算法要非常复杂，这里不做介绍

 

##### metadata lock

 

1. 这是Server 层实现的锁，跟引擎层无关

2. 当你执行select的时候，如果这时候有ddl语句，那么ddl会被阻塞，因为select语句拥有metadata lock，防止元数据被改掉