mysql 锁机制与 InnoDB 锁算法

锁的重要性

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

如何保证数据并发访问的一致性，有效性，是所有数据库必须要解决的问题。

锁冲突也是影响数据库并发访问性能的一个重要因素，因此锁对数据库尤其重要。

加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。

本文是基于InnoDB存储引擎

锁分类

• 从性能上分为乐观锁(用版本对比来实现)和悲观锁

• 基于锁的属性分类：共享锁、排他锁。

• 基于锁的粒度分类：行级锁(INNODB)、表级锁(INNODB、MYISAM)、页级锁(BDB引擎 )、记录锁、间隙锁、临键锁。

• 基于锁的状态分类：意向共享锁、意向排它锁。

表锁

表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问；

特点：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

一般用在整表数据迁移的场景。

• 手动增加表锁

lock table 表名称 read(write),表名称2 read(write);

• 查看表上加过的锁

show open tables;

• 删除表锁

unlock tables;

表锁分析

# 查看表锁情况#
show open tables;
# 表锁分析
show status like 'table%';

名词解释：

1. table_locks_waited
   出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次值加1），此值高说明存在着较严重的表级锁争用情况
2. table_locks_immediate
   产生表级锁定次数，不是可以立即获取锁的查询次数，每立即获取锁加1。

行锁

行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问；

特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB与MYISAM的最大不同有两点：

• InnoDB支持事务（TRANSACTION）
• InnoDB支持行级锁

InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

行锁的实现算法

Record Lock 锁

单个行记录上的锁。
Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定。

间隙锁（Gap Lock）

间隙锁，锁的就是两个值之间的空隙。

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引加锁，对于键值在条件范围内但并不存在的记录。

官方文档解释：

间隙锁定是对索引记录之间的间隙的锁定，或者是对第一个或最后一个索引记录之前的间隙的锁定。间隙可能跨越单个索引值，多个索引值，甚至为空。

间隙锁定InnoDB是“纯粹抑制性的”，这意味着它们的唯一目的是防止其他事务插入间隙。间隙锁可以共存。一个事务进行的间隙锁定不会阻止另一事务对相同的间隙进行间隙锁定。共享和专用间隙锁之间没有区别。它们彼此不冲突，并且执行相同的功能。

间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。

范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ（重复读)的事务级别中。

触发条件：防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生如下图的问题，在同一个事务里，A事务的两次查询出的结果会不一样。

比如表里面的数据ID 为 1,4,5,7,10 ,那么会形成以下几个间隙区间，-n-1区间，1-4区间，7-10 区间，10-n区间 （-n代表负无穷大，n代表正无穷大）

优点：解决了事务并发的幻读问题
不足：因为query执行过程中通过范围查找的话，他会锁定争个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成锁定的时候无法插入锁定键值范围内任何数据。在某些场景下这可能会对性能造成很大的危害。

InnoDB使用间隙锁的目的：

1. 防止幻读，以满足相关隔离级别的要求；
2. 满足恢复和复制的需要：

MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制（复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复）有以下特点：

一是 MySQL 的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。

二是 MySQL 的 Binlog 是按照事务提交的先后顺序记录的， 恢复也是按这个顺序进行的。

由此可见，MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。

Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生

有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock）

A.将事务隔离级别设置为RC

B. 将参数innodb_locks_unsafe_for_binlog设置为1

查看 innodb_locks_unsafe_for_binlog 是否禁用：

show variables like 'innodb_locks_unsafe_for_binlog'; #innodb_locks_unsafe_for_binlog：默认值为OFF，即启用间隙锁。

以下示例都是在RR隔离级别测试

CREATE TABLE `test` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL,
	`age` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB
AUTO_INCREMENT=1
;

insert into test(id,name,age)values(2,'刘备',20);
insert into test(id,name,age)values(7,'关羽',30);
insert into test(id,name,age)values(10,'赵云',28);
insert into test(id,name,age)values(15,'曹操',40);

1、唯一索引的间隙锁

• 对于使用唯一索引来锁定唯一行来锁定行的语句，不需要间隙锁定。（这不包括搜索条件仅包含多列唯一索引的某些列的情况；在这种情况下，会发生间隙锁定。）例如，如果该id列具有唯一索引，则以下语句仅使用一个具有id值10的行的索引记录锁定，其他会话是否在前面的间隙中插入行并不重要：

开启事务1：
SELECT * FROM test where  id = 10 for update;
开启事务2：都可以正常执行
insert into test(id,name,age)values(9,'李逵',40);
insert into test(id,name,age)values(8,'吴用',40);
insert into test(id,name,age)values(11,'苏武',40);

• 查询范围数据产生间隙锁

事务1：
select * from test where id <=7 for update;

事务2：
insert into test(id,name,age)values(5,'盖伦',20); -- 阻塞
insert into test(id,name,age)values(7,'盖伦',20);-- 阻塞
insert into test(id,name,age)values(8,'盖伦',20); -- 阻塞
insert into test(id,name,age)values(9,'盖伦',20); -- 阻塞
insert into test(id,name,age)values(10,'盖伦',20); -- 阻塞
insert into test(id,name,age)values(11,'盖伦',20); --成功

操作结束后事务回滚

通过上面例子可以看出在(1,7) (7,10)之间都会有锁

• 范围查询

事务1：
 select * from test where id >3 and id <=7 for update;
事务2：
 insert into test(id,name,age)values(3,'盖伦',20);--阻塞
 insert into test(id,name,age)values(6,'盖伦',20); --阻塞
 insert into test(id,name,age)values(8,'盖伦',20);--阻塞
insert into test(id,name,age)values(9,'盖伦',20);--阻塞
 insert into test(id,name,age)values(10,'盖伦',20);--阻塞
insert into test(id,name,age)values(11,'盖伦',20);--成功

操作后事务回滚

从上面例子看出，(2,10]之间都会加上锁

• 不存在数据加锁

事务1：
select * from test where id = 5 for update;
事务2：
 insert into test(id,name,age)values(9,'盖伦',20);--成功
 insert into test(id,name,age)values(6,'盖伦',20);--阻塞
 insert into test(id,name,age)values(4,'盖伦',20);--阻塞
 insert into test(id,name,age)values(3,'盖伦',20);--阻塞

操作结束后事务回滚

从上面测试可以看出，(2,7)之间存在间隙锁

总结

对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：WHERE id = 5 FOR UPDATE;

对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE id BETWEEN 5 AND 7 FOR UPDATE;

Mysql默认级别是repeatable-read，有办法解决幻读问题吗？间隙锁在某些情况下可以解决幻读问题。

假设account表里数据如下：

那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，

在Session_1下面执行 update account set name = ‘zhuge’ where id > 8 and id <18;，则其他Session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在(3,20]区间都无法修改数据，注意最后那个20也是包含在内的。

间隙锁是在可重复读隔离级别下才会生效。

事务A：

事务B：

事务A：

事务B：

临键锁 (Next-key Lock)

Next-Key Locks是行锁与间隙锁的组合（同时锁住数据+间隙锁）。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。

在Repeatable Read隔离级别下，Next-key Lock 算法是默认的行记录锁定算法。

Gap lock：间隙锁，锁定一个范围，不包括记录本身

Next-key lock：record+gap 锁定一个范围，包含记录本身

无索引行锁会升级为表锁(RR级别会升级为表锁，RC级别不会升级为表锁)

锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁。

当查询的索引含有唯一属性时，将next-key lock降级为record key。

session1 执行：update account set balance = 800 where name = ‘lilei’;

session2 对该表任一行操作都会阻塞住

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)，例如：select * from test_innodb_lock where a = 2 for update; 这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交

结论

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。

行锁的注意点

1. 只有通过索引条件检索数据时，InnoDB才会使用行级锁，否则会使用表级锁(索引失效，行锁变表锁)
2. 即使是访问不同行的记录，如果使用的是相同的索引键，会发生锁冲突
3. 如果数据表建有多个索引时，可以通过不同的索引锁定不同的行

行锁实现方式

• InnoDB 行锁是通过给索引上的索引项加锁来实现的，InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
• 不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
• 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时， 别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。
• 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。 应用设计的时候要注意这一点。

行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';              

对各个状态量的说明如下：

Innodb_row_lock_current_waits: 当前正在等待锁定的数量

Innodb_row_lock_time: 从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg: 每次等待所花平均时间

Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间

Innodb_row_lock_waits: 系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：

Innodb_row_lock_time_avg （等待平均时长）

Innodb_row_lock_waits （等待总次数）

Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

查看INFORMATION_SCHEMA系统库锁相关数据表

-- 查看事务 
select * from INFORMATION_SCHEMA.INNODB_TRX; 
-- 查看锁 
select * from INFORMATION_SCHEMA.INNODB_LOCKS; 
-- 查看锁等待 
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到 
kill trx_mysql_thread_id 
-- 查看锁等待详细信息 
show engine innodb status\G;

优化建议

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
2. 合理设计索引，尽量缩小锁的范围
3. 尽可能较少检索条件，避免间隙锁
4. 尽量控制事务大小，减少锁定资源量和时间长度
5. 尽可能低级别事务隔离

共享锁（shared lock）

共享锁又称读锁，简称S锁；当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复读的问题。

select * from tableName lock in share mode;//读锁

select *** lock in share mode 使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

排他锁（exclusive lock）

排他锁又称写锁，简称X锁；当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读取。避免了出现脏数据和脏读的问题。

在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。

InnoDB会对update\delete\insert语句自动添加排他锁。

select * from tableName for update;//写锁

解锁：提交事务或回滚事务

自增锁

通常是针对MySQL当中的自增字段。如果有事务回滚这种情况，数据会回滚，但是自增序列不会回滚。

页锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

意向锁

是InnoDB自动添加的一种锁，不需要用户干预。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

意向共享锁（IS）：当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。

意向排他锁（IX）：当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁。

IS、S、IX、X锁之间的兼容性比较：

全局锁

加锁之后整个数据库实例都处于只读状态。所有的数据变更操作都会被挂起。一般用于全库备份的时候。

 Flush tables with read lock

死锁

指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。

产生的条件：

1. 互斥条件：一个资源每次只能被一个进程使用
2. 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放
3. 不剥夺条件：进程已获得的资源，在没有使用完之前，不能强行剥夺
4. 循环等待条件：多个进程之间形成的一种互相循环等待的资源的关系

解决方案：

1. 查看死锁：show engine innodb status \G
2. 自动检测机制，超时自动回滚代价较小的事务（innodb_lock_wait_timeout 默认50s）
3. 人为解决，kill阻塞进程（show processlist）
4. wait for graph 等待图（主动检测）

如何避免：

1. 加锁顺序一致，尽可能一次性锁定所需的数据行
2. 尽量基于primary（主键）或unique key更新数据
3. 单次操作数据量不宜过多，涉及表尽量少
4. 减少表上索引，减少锁定资源
5. 尽量使用较低的隔离级别
6. 尽量使用相同条件访问数据，这样可以避免间隙锁对并发的插入影响
7. 精心设计索引，尽量使用索引访问数据
8. 借助相关工具：pt-deadlock-logger

set tx_isolation=‘repeatable-read’;

Session_1执行：select * from account where id=1 for update;

Session_2执行：select * from account where id=2 for update;

Session_1执行：select * from account where id=2 for update;

Session_2执行：select * from account where id=1 for update;

查看近期死锁日志信息：show engine innodb status\G;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁

乐观锁和悲观锁

1、乐观锁(Optimistic Lock)：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。 乐观锁不能解决脏读的问题。

乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

• 数据表中的实现

用数据版本号（version）机制是乐观锁最常用的一种实现方式。一般通过为数据库表增加一个数字类型的 “version” 字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值+1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败。

//step1: 查询出商品信息 
select (quantity,version) from items where id=100; 
//step2: 根据商品信息生成订单 
insert into orders(id,item_id) values(null,100); 
//step3: 修改商品的库存 update items set quantity=quantity-1,version=version+1 where id=100 and version=#{version};

• 锁的粒度

用版本号每次只能有一个成功，比较影响性能，可以减少锁的粒度

// 仍挑选以库存数作为乐观锁
//step1: 查询出商品信息
select (inventory) from items where id=100;
//step2: 根据商品信息生成订单
insert into orders(id,item_id) values(null,100);
//step3: 修改商品的库存
update items set inventory=inventory-1 where id=100 and inventory-1>0;

2.悲观锁(Pessimistic Lock)：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

悲观锁，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

select…for update是MySQL提供的实现悲观锁的方式，在MySQL中用悲观锁务必须确定走了索引，而不是全表扫描，否则将会将整个数据表锁住。

	悲观锁	乐观锁
概念	定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。	假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
实现机制	锁住记录，其他事务不能查询不能更新	更新数据时候检查时间戳或者版本号是否符合

lect…for update**是MySQL提供的实现悲观锁的方式，在MySQL中用悲观锁务必须确定走了索引，而不是全表扫描，否则将会将整个数据表锁住。

	悲观锁	乐观锁
概念	定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。	假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
实现机制	锁住记录，其他事务不能查询不能更新	更新数据时候检查时间戳或者版本号是否符合
实现	数据库	开发人