这个Mysql锁，看明白算你厉害

「时光不负，创作不停，本文正在参加2021年终总结征文大赛」

SQL语句查询过程

锁介绍

Mysql引擎对比

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

Mysql锁是Mysql在服务层和存储引擎层的并发控制，保证数据并发访问的一致性、有效性

解决锁冲突也是影响数据并发访问性能的一个重要因素

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

Mysql锁粒度：

默认情况下，表级锁和行级锁都是自动获得的，不需要额外的命令，但在有些情况下，用户需要明确的进行锁表或者事务控制，以确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成

数据页是mysql中磁盘和内存交换的基本单位，也是mysql管理存储空间的基本单位，同一个数据库实例的所有表空间都有相同的页大小；默认情况下，表空间中的页大小都为16KB

事务是指通过将一组相关操作组合为一个要么全部成功要么全部失败的单元；这组操作是一个最小的不可再分的工作单元；单个SQL可以看做是一个事务

基本表锁类型（所有引擎都支持）

在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表

SQL语句类型

MyISAM锁

MyISAM锁类型

MyISAM存储引擎只支持表级锁（基本表锁）：

MyISAM总是一次获得SQL语句所需要的全部锁，这也正是MyISAM表不会出现死锁（Deadlock Free）的原因

MyISAM自动获取锁的竞争

MyISAM 表的读操作与写操作之间，以及写操作之间都是串行的，串行是通过加锁来实现的

MyISAM 引擎默认是write Lock优先于read Lock的，也就是说如果一堆写请求和一堆读请求同时要一张表的锁，当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，以致于读请求只能在所有的写请求执行完成后才能获得执行机会

MyISAM 引擎读写并发支持较弱

InnoDB锁

InnoDB锁类型

InnoDB存储引擎支持表级锁、行级锁

InnoDB存储引擎默认使用行级锁

InnoDB行锁机制

InnoDB表级锁类-意向锁（解决表级锁在加锁时的互斥检测）

一个事务要对表A加表级共享锁（表级排它锁），必须保证:

当前没有其他事务持有 A 表的排他锁（共享锁和排它锁）
当前没有其他事务持有 A 表中任意一行的排他锁（共享锁和排它锁）

为了检测是否满足第二个条件，该事务必须去检测表中的每一行是否存在排他锁（共享锁和排它锁），很明显这是一个效率很差的做法；但是有了意向锁之后，只要检测表级意向排他锁（意向共享锁和意向排它锁）就可以了

意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享锁、排他锁之前，InnoDB会先获取该数据行所在数据表的对应意向锁

意向锁不会与行级的共享 / 排他锁互斥，只会与表级共享锁 / 排他锁互斥；表级锁包括基本表锁、自增锁

DQL和DML操作涉及到行级锁的互斥，由行级锁机制进行检测和处理；互斥则等待锁释放

InnoDB表级锁-自增锁（保证AUTO_INCREMENT列连续）

InnoDB表级锁-自增锁-主键冲突场景：

InnoDB行级锁-基本行锁

InnoDB行级锁-行锁（InnoDB行级锁按照影响范围区分）

InnoDB行级锁-临键锁（next-key locks）、间隙锁（gap locks）：

假如有个索引是：[1，2，3，7]

record lock 锁的是 1，2，3，7

gap lock 锁的是 (-，1)，（2，3），（3，7），（7，+）锁的就是区间，不是行

next-key lock锁的是 (-，1]，[2，3），[3，7），[ 7，+）既锁范围也锁行

乐观锁与悲观锁

锁机制

悲观锁、乐观锁，本身只是一个概念，不是某种特定的锁机制

有不同的方法可以实现，和数据库本身没有关系

本质上，不管基于哪种方法实现乐观锁，本质都是检验数据

悲观锁是基于数据库实现的，用到了数据库排它锁、共享锁

乐观锁应用例子

1、何谓数据版本，即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现

当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一


当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据


 例如：update table\_name set column1 = 'XXXXX', version = version+1 where version = 123;

2、使用时间戳（timestamp），这一种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似

也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突


例如：update table\_name set column1 = 'XXXXX', version\_time = unix\_timestamp() where version\_time = 1606459691;

3、使用状态值（status），这一种实现方式同样是在需要乐观锁控制的table中增加一个字段 status, 不需要提前获取数据，一般用于需要状态流转的流程控制中

 比如 存在状态值 1、2、3，需要控制状态值只可以从1变更为2，2变更为3，这样在更新提交的时候检查当前状态值和合法状态值进行对比，如果一致则OK，否则就是版本冲突


例如：update table\_name set stauts= 2 where status = 1;

死锁检测和恢复

死锁产生

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

当事务试图以不同的顺序锁定资源时，就可能产生死锁；多个事务同时锁定同一个资源时也可能会产生死锁

锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式

检测死锁

数据库系统实现了各种死锁检测和死锁超时的机制；InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误

死锁恢复

死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚；所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可

外部锁的死锁检测

发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务；但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

死锁影响性能

死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚

查看死锁

show engine innodb status \G

使用命令来确定最后一个死锁产生的原因；返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

锁使用与优化

优化方案

尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行；这样可以大大减少死锁的机会
给记录集显式加锁时，最好一次性请求足够级别的锁；比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；但是也不要申请超过实际需要的锁级别
除非必须，查询时不要显式加锁；MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
尽量使用较低的隔离级别；但是也需要考虑业务场景，选择合适的隔离级别，默认隔离级别 RR（可重复读）
选择合理的事务大小，小事务（事务中包含的sql语句较少、设计到的表的数据/容量较小）发生锁冲突的几率也更小，也会减少锁定资源量和时间长度
对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能