浅析MySQL中的锁-高老四博客

锁的概述

数据库中的锁

不同于 Java 中多线程中的锁，数据库中的锁多半是针对于数据并发访问的一致性、有效性等，所以锁冲突是影响数据库并发访问性能的重要因素。MySQL 的 InnoDB 存储引擎涉及到各种「锁」的概念比较多，在笔面试中、企业级项目、平时工作中，我们会经常碰到相关的问题，所以相关的知识是必须要了解的。

之前老四在开源思维导图项目中分享过 MySQL 涉及到一些锁概念。

浅析 MySQL 中的锁的图片-高老四博客第1张

MySQL 锁按照特性和设计分类

表级锁

MyISAM 和 MEMORY 存储引擎采用的就是表级锁，不过 BDB 和 InnoDB 存储引擎也支持表级锁。

表级锁的特点就是开销小，加锁快；不会出现死锁；锁定粒度大（锁整张表），发生锁冲突概率高，并发度最低，基本可以理解为都是串行化了。

所以表级锁适合以查询为主，辅以少量按索引条件更新数据的应用。老四的博客站数据使用的 MyISAM 存储引擎，因为做存储和更新比较少，基本都是静态网页，所以采用，不过以 Java 为主的企业级互联网公司等基本使用的都是 InnoDB 存储引擎，所以本文简单讨论 MyISAM 的表级锁，着重讨论 InnoDB 的事务以及行级锁。

行级锁

InnoDB 存储引擎默认采用的就是行级锁，它的特点与 MyISAM 相反，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度自然就最高。

所以行级锁适合大量按索引条件并发更新少量数据不同数据，同时又有并发查询的应用。

页面锁

BDB 存储引擎采用的页面锁，不过 BDB 已经被 InnoDB 取代，即将成为历史。它的开销和加锁时间介于上面二者之间，也会出现死锁，粒度和并发度也介于上述二者之间。

MyISAM 存储引擎中的表级锁

表级锁的两种锁模式

表共享读锁（不影响其他线程的读，但是不能写）
表独占写锁（其他线程的读锁、写锁都被阻塞）

其实对于锁定一张表，它的锁模式相对就比较简单，也就是读写是串行的，读的时候不能写，写的时候不能读。

需要注意的是，我们平时对表加锁，一般不需要直接用「lLOCK TABLE」命令给 MyISAM 表显式加锁，这都是 MYISAM 默认就会根据 SQL 加对应的表级锁的；不过MyISAM 不支持锁升级，即当执行「LOCK TABLES」或者「LOCK TABLE」命令的时候只能访问显式加锁的这些表，不能访问没加锁的表，同时，你如果在 SQL 语句中对表名使用别名，别名也需要加锁，否则没效。

locl table tb_glorze as g1， tb_gls as g2;

1	locl table tb_glorze as g1， tb_gls as g2;

MyISAM 的并发插入

虽然 MyISAM 从锁模式来讲是读写串行的，不过 MyISAM 也是支持查询和插入并发进行的。在 MySQL 中有一个系统变量「concurrent\_insert」：

当 concurrent\_insert = 0，不允许并发插入
当 concurrent\_insert = 1，MySQL 设置的默认值，如果 MyISAM 表的中间被删除的行，允许在一个进程读表的同时，另一个进程从表尾插入记录。
当 concurrent\_insert = 2，无限制插入。

MyISAM 的锁调度

MySQL 认为写请求一般比读请求重要，所以 MyISAM 在遇到读写请求同时请求一张表的锁的时候，是默认写进程先获得锁。

这个特性说明了 MyISAM 不适合做数据大量更新的操作，一旦写请求进入会导致读请求阻塞，业务系统页面等也就瘫痪了。不过也可以通过相关参数的设定来调节 MyISAM 读进程优先获得锁。

通过指定启动参数「low-priority-updates」
通过执行「SET LOW\_PRIORITY\_UPDATES=1」命令
通过指定 INSERT、UPDATE、DELETE 语句的 LOW\_PRIORITY 属性，降低写进程的优先级

InnoDB 中的行级锁

InnoDB 作为现在互联网公司主用的存储引擎，是因为能够支持良好的并发事务，并且采用行级锁，这是 MyISAM 存储引擎没有的。数据库中事务的相关的概念也是必须掌握的内容，我们先从数据库事务、隔离级别等概念复习说起。

数据库事务及其 ACID 属性

原子性：事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全都不执行。
一致性：事务的开始和完成，所有相关的数据规则都必须应用于事务的修改，所有的内部数据结构（B树索引、双向链表等）都是正确的。
隔离性：事务处理过程中的中间状态对外部是不可见的，保证事务不受外部并发操作的影响。
持久性：事务完成之后，对于数据的修改是永久性的、持久化的。

并发事务的问题及其隔离级别

在并发事务中，由于多个线程同时对数据库进行读取或者更新操作，那么自然不可避免的就会带来并发中常见的一些问题，这些问题基本都是由于上述数据库事务特性中的一致性导致的，也就是并发操作带来数据总是出现不一致的问题，对应的解决办法就是根据并发严重程度设置不同程度的隔离级别。

并发事务带来的问题

更新丢失：事务彼此之间不知道自己都在同时更新一行记录，导致更新丢失。不过这种问题应该在应用程序中就避免掉而不是由数据库来解决。
脏读：说白了就是产生了脏数据，一个事务正在修改数据，没改完提交呢，就有另一个事务来读这条数据并使用了。
不可重复读：说白了就是读不到之前的数据了，一个事务刚刚读取了某条数据，再次读取的时候发现数据行没了或者关键值被修改了。
幻读：说白了就是查出来了多个满足条件的记录。一个事务按照某种条件查询记录，再次查询的时候不知道别的事务正在按照同等条件做更新或者插入，出现了新数据。

事务的隔离级别

解决数据库一致性的问题分为两种，一种就是加锁，给表加锁或者给记录加锁，但是并发的事务都依靠锁的话会带来极大的性能开销。于是就有了 MVCC(MultiVersion Concurrency Control，多版本数据库) 的概念，通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本。

这个隔离级别在 MySQL 中有四种：

未提交读：相当于对并发事务不进行控制，所以脏读、不可重复读、幻读的问题该隔离级别都无法解决。
已提交读：通过 SQL 语句来控制，就是只有当前事务提交了另外的事务才能读取，所以可以有效防止脏读问题，大多数程序采用此隔离级别。
可重复读：该隔离级别可以防止脏读和不可重复读，原因就是通过设置事务的行为使其能够在执行过程中进行多次查询，都能获取相同的返回记录结果。
可序列化、串行化：这个隔离级别就相当于上锁，当事务对数据进行操作的时候，其他事务相当于被阻塞直至上一个事务释放对当前记录的控制。

顺便复习 Spring 中事务隔离级别的设置

编程式事务管理

使用 TransactionTemplate 或者直接使用底层的 PlatformTransactionManager。对于编程式事务管理，Spring推荐使用 TransactionTemplate。

声明式事务管理

建立在 AOP 之上的。其本质是对方法前后进行拦截，然后在目标方法开始之前创建或者加入一个事务，在执行完目标方法之后根据执行情况提交或者回滚事务。

声明式事务最大的优点就是不需要在业务逻辑代码中掺杂事务管理的代码，只需在配置文件中做相关的事务规则声明(或通过基于 @Transactional 注解的方式)，便可以将事务规则应用到业务逻辑中。

声明式事务唯一不足地方是最细粒度只能作用到方法级别，无法做到像编程式事务那样可以作用到代码块级别。由于目前 Spring Boot 渐渐成文主流，更推荐基于 @Transactional 注解的方式来对事务进行管理。

Spring 事务特性

Spring所有的事务管理策略类都继承自 org.springframework.transaction.PlatformTransactionManager 接口，
其中的 TransactionDefinition 接口定义了事务隔离级别、事务传播行为、事务超时、事务只读属性等。

如果方法中存在事务属性，则使用方法上的属性，否则使用方法所在的类上的属性，如果方法所在类的属性上还是没有搜寻到对应的事务属性，那么再搜寻接口中的方法，再没有的话，最后尝试搜寻接口的类上面的声明。

隔离级别

隔离级别是指若干个并发的事务之间的隔离程度。TransactionDefinition 接口中定义了五个表示隔离级别的常量：

TransactionDefinition.ISOLATION_DEFAULT：这是默认值，表示使用底层数据库的默认隔离级别。对大部分数据库而言，通常这值就是TransactionDefinition.ISOLATION_READ_COMMITTED。
TransactionDefinition.ISOLATION_READ_UNCOMMITTED：该隔离级别表示一个事务可以读取另一个事务修改但还没有提交的数据。该级别不能防止脏读，不可重复读和幻读，因此很少使用该隔离级别。
TransactionDefinition.ISOLATION\_READ_COMMITTED：该隔离级别表示一个事务只能读取另一个事务已经提交的数据。该级别可以防止脏读，这也是大多数情况下的推荐值。
TransactionDefinition.ISOLATION_REPEATABLE_READ：该隔离级别表示一个事务在整个过程中可以多次重复执行某个查询，并且每次返回的记录都相同。该级别可以防止脏读和不可重复读。
TransactionDefinition.ISOLATION_SERIALIZABLE：所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。

事务传播行为

所谓事务的传播行为是指，如果在开始当前事务之前，一个事务上下文已经存在，此时有若干选项可以指定一个事务性方法的执行行为。在 TransactionDefinition 定义中包括了如下几个表示传播行为的常量：

TransactionDefinition.PROPAGATION_REQUIRED：如果当前存在事务，则加入该事务；如果当前没有事务，则创建一个新的事务。这是默认值。
TransactionDefinition.PROPAGATION_REQUIRES_NEW：创建一个新的事务，如果当前存在事务，则把当前事务挂起。
TransactionDefinition.PROPAGATION_SUPPORTS：如果当前存在事务，则加入该事务；如果当前没有事务，则以非事务的方式继续运行。
TransactionDefinition.PROPAGATION_NOT_SUPPORTED：以非事务方式运行，如果当前存在事务，则把当前事务挂起。
TransactionDefinition.PROPAGATION_NEVER：以非事务方式运行，如果当前存在事务，则抛出异常。
TransactionDefinition.PROPAGATION_MANDATORY：如果当前存在事务，则加入该事务；如果当前没有事务，则抛出异常。
TransactionDefinition.PROPAGATION_NESTED：如果当前存在事务，则创建一个事务作为当前事务的嵌套事务来运行；如果当前没有事务，则该取值等价于TransactionDefinition.PROPAGATION_REQUIRED。

事务超时

所谓事务超时，就是指一个事务所允许执行的最长时间，如果超过该时间限制但事务还没有完成，则自动回滚事务。在 TransactionDefinition 中以 int 的值来表示超时时间，其单位是秒。

默认设置为底层事务系统的超时值，如果底层数据库事务系统没有设置超时值，那么就是none，没有超时限制。

事务只读属性

只读事务用于客户代码只读但不修改数据的情形，只读事务用于特定情景下的优化，比如使用Hibernate的时候。

Spring 事务回滚规则

指示 Spring 事务管理器回滚一个事务的推荐方法是在当前事务的上下文内抛出异常。Spring 事务管理器会捕捉任何未处理的异常，然后依据规则决定是否回滚抛出异常的事务。

默认配置下，Spring只有在抛出的异常为运行时异常时才会回滚该事务，也就是抛出的异常为 RuntimeException 的子类（Error 也会导致事务回滚），而抛出 checked 异常则不会导致事务回滚。

可以明确的配置在抛出那些异常时回滚事务，包括 checked 异常。也可以明确定义哪些异常抛出时不回滚事务。还可以编程性的通过 setRollbackOnly() 方法来指示一个事务必须回滚，在调用完 setRollbackOnly() 后你所能执行的唯一操作就是回滚。

InnoDB 的行锁模式以及加锁方式

细分起来，InnoDB 上锁的模式分为以下四种：

共享锁（S）：允许一个事务去读一行，其他事务也可以读，但是不能写，即其他事务不能获得排他锁。
排他锁（X）：允许一个事务进行更新操作，其他事务既不能读取也不能更新数据，即其他事务不能获得共享读锁也不能获取排他写锁
意向共享锁（IS）
意向排他锁（IX）

其中共享锁和排他锁都需要我们手动添加，而意向锁它俩都是表锁，当一个事务想要给某一行数据加共享锁或者排他锁之前，他需要瞬间获得这张表的表锁，所以叫做意向锁。

意向锁由 InnoDB 自动添加，不需要人工干预。

不过在底层 MySQL 加锁的过程中，还涉及到一些间隙锁、Next-key 锁等的概念。InnoDB 行锁的实现是通过给索引上的索引项加锁来实现，如果没有索引，会对隐藏的聚簇索引加锁。如果不通过索引条件检索数据，那么 InnoDB 会获取表锁。

由于查询中多半都是范围查询，其中这些锁的概念其实主要目的就是避免幻读的现象，通过给索引项之间、记录之前、记录之后加锁，保证数据的一致性，从而解决范围查询请求共享或者排他锁。另外，使用相等条件请求给不存在的记录加锁也会使用间隙锁。

InnoDB 行锁实际使用注意事项

在不通过索引条件查询时，InnoDB会锁定表中的所有记录
由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁
即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB也会对所有记录加锁。

InnoDB 主动使用表锁的情景

事务需要更新大部分或全部数据，表又比较大
事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚
要将AUTOCOMMIT设为0，否则MySQL不会给表加锁

InnoDB 中的死锁

发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务（干预的外部锁和表锁的情况除外）。

避免死锁的常用的方法

表访问约定顺序：在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
线程排序：在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能
处理数据就申请排他锁：在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁
使用已提交读解决可重复读的死锁问题：在可重复读的隔离级别下，如果两个线程同时对相同条件记录用 SELECT…FOR UPDATE 加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成已提交读的隔离级别
通过主键主键不可重复的特点解决已提交读的死锁问题：当隔离级别为已提交读时，如果两个线程都先执行 SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行 ROLLBACK 释放获得的排他锁。

MySQL 锁相关命令

查询表级锁争用情况

SHOW STATUS LIKE 'table%';

1	SHOW STATUS LIKE 'table%';

浅析 MySQL 中的锁的图片-高老四博客第2张

Table_locks_waited 的值越大，就代表存在着较严重的表级锁争用情况。

获取 InnoDB 行锁争用情况

SHOW STATUS LIKE 'innodb_row_lock%';

1	SHOW STATUS LIKE 'innodb_row_lock%';

浅析 MySQL 中的锁的图片-高老四博客第3张

如果 Innodb_row_lock_waits 和 Innodb_row_lock_time_avg 值比较大，说明锁争用情况比较严重。

InnoDB 中查看数据表锁冲突、相应数据行的情况

通过查询 information_schema 数据库中的相关表

SELECT * FROM innodb_locks;

1	SELECT * FROM innodb_locks;

注意：这个「innodb\_locks」表从 MySQL 5.7.14 开始不推荐使用，并在 MySQL 8.0 中删除了，所以不推荐使用了。

通过启用 InnoDB 监视器

这个老四不多逼逼，看官方文档就可以了。就是可以设置监视器的参数打开，然后可以监控各种 InnoDB 相关参数的运行情况，包括活动交易持有的表和记录锁、锁冲突、争用的详细信息。此外还有如下相关信息的监控：

主后台线程完成的工作
信号量等待
有关最新外键和死锁错误的数据
待处理的I / O操作和相关统计信息
插入缓冲区和自适应哈希索引统计信息
重做日志数据
缓冲池统计
行操作数据

需要注意的一点就是，打开 InnoDB 监视器默认每 15 秒写入到错误日志，如果不及时关闭监视器回到日志文件巨大，所以问题查找到原因之后记得即时关闭监视器。

确定 InnoDB 最后一个死锁产生的原因

SHOW ENGINE INNODB STATUS;

1	SHOW ENGINE INNODB STATUS;

该条命令的查询结果是 InnoDB 过去一段时间内的运行状态统计，相当于监视器的一次快照，其中有一个「LATEST DETECTED DEADLOCK」字段就是统计最近的一次死锁信息，只有发生过死锁才会看到这个字段。此外还有如下字段：

BACKGROUND THREAD 后台主线程信息
SEMAPHORES 信号量信息
TRANSACTIONS 事务信息
FILE I/O 文件、输入输出流、阻塞信息
INSERT BUFFER AND ADAPTIVE HASH INDEX 插入缓冲和自适应哈希索引
LOG 日志信息
BUFFER POOL AND MEMORY 缓存池和内存信息
ROW OPERATIONS‍‍ 行操作统计信息