科普文：软件架构数据库系列之【详解MySQL死锁】

概叙

科普文：软件架构数据库系列之【MySQL锁梳理】_一个sql语句相当于一个事务、-CSDN博客

科普文：软件架构数据库系列之【MySQL 执行DQL/DML/DDL必加的自动表级锁：MDL元数据锁Meta Data Lock】-CSDN博客

科普文：软件架构数据库系列之【MySQL5.7的系统表梳理】_mysql 5.7系统表-CSDN博客

科普文：软件架构数据库系列之【Innodb的锁和MVCC】-CSDN博客

锁的由来

锁（LOCKING）是最常用的并发控制机构。

锁是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段。

锁是事务对某个数据库中的资源（如表和记录）存取前，先向系统提出请求，封锁该资源，事务获得锁后，即取得对数据的控制权，在事务释放它的锁之前，其他事务不能更新此数据。当事务撤消后，释放被锁定的资源。
当一个用户锁住数据库中的某个对象时，其他用户就不能再访问该对象。

锁就是并发控制。

科普文：软件架构数据库系列之【MySQL 是怎么做并发控制的？】马国庆（翊云）-CSDN博客

CPU、操作系统、编程语言、数据库对锁的定义都是一样的，都是为了应对并发、保护临界资源。

科普文：深入理解Java 中的锁-CSDN博客

科普文：JUC系列之Java 中15种锁简介_juc锁-CSDN博客

科普文：JUC系列之Java中7种阻塞队列BlockingQueue的双锁源码解读_blockingqueue 两把锁-CSDN博客

科普文：图解并发下的AQS原理及其实现的锁_aqs实现了哪些锁-CSDN博客

科普文：Java基础系列之Java对象结构与synchronized对象锁的升级-CSDN博客

科普文：jvm对锁的优化-CSDN博客

科普文：软件架构数据库系列之【InnoDB 事务锁源码分析】作者|宋昭_innodb 锁 源码-CSDN博客

科普文：软件架构数据库系列之【 InnoDB隐式锁功能解析】作者|杭枫-CSDN博客

什么是临界区？

答：每个进程中访问临界资源的那段程序称为临界区（临界资源是一次仅允许一个进程使用的共享资源）。每次只准许一个进程进入临界区，进入后不允许其他进程进入。

进程进入临界区的调度原则

①如果有若干进程要求进入空闲的临界区，一次仅允许一个进程进入。

②任何时候，处于临界区内的进程不可多于一个。如已有进程进入自己的临界区，则其它所有试图进入临界区的进程必须等待。

③进入临界区的进程要在有限时间内退出，以便其它进程能及时进入自己的临界区。

④如果进程不能进入自己的临界区，则应让出CPU，避免进程出现“忙等”现象。

互斥对象是一种最简单的内核对象，用它可以方便的实现对某一资源的互斥访问。因为它是内核对象，因此可以产生信号，实际上，程序中就是利用这一点实现互斥的。
如果没记错的话，临界区并不是内核对象，而是系统提供的一种数据结构，程序中可以声明一个该类型变量，之后用它来实现对资源的互斥访问。当欲访问某一临界资源时，先将该临界区加锁（如果临界区不空闲，等待），用完该资源后，将临界区释放。
一般，将他们用于线程间的同步，而且通常可以互换使用。
如果要实现复杂互斥，应使用其它方法，如信号量内核对象等。临界区对象不能跨越进程，是线程间共享数据区的同步对象；互斥对象可以作为进程间共享数据区的同步对象。

MySQL锁的由来

客户端发往MySQL的一条条SQL语句，实际上都可以理解成一个个单独的事务（一条sql语句默认就是一个事务）。而事务是基于数据库连接的，每个数据库连接在MySQL中，又会用一条工作线程来维护，也意味着一个事务的执行，本质上就是一条工作线程在执行，当出现多个事务同时执行时，这种情况则被称之为并发事务，所谓的并发事务也就是指多条线程并发执行。

多线程并发执行自然就会出问题，也就是科普文：数据库事务、隔离级别和并发问题（MySQL）_mysql 事务并发-CSDN博客中提到的脏写、脏读、不可重复读及幻读问题。

而对于这些问题又可以通过调整事务的隔离级别来避免，那为什么调整事务的隔离级别后能避免这些问题产生呢？

这是因为不同的隔离级别中，工作线程执行SQL语句时，用的锁粒度、类型不同。

MySQL锁分类

MySQL的锁机制与索引机制类似，都是由存储引擎负责实现的，这也就意味着不同的存储引擎，支持的锁也并不同，这里是指不同的引擎实现的锁粒度不同。

但总归说来说去其实就共享锁（读锁）、排他锁（写锁）两种，只是加的方式不同、加的地方不同，因此就演化出了这么多锁的称呼。

而表锁：MDL元数据锁Meta Data Lock，是“共享排他锁（读写锁）”，因为在5.6引入OnlineDDL后，MDL可以从排他锁降（写锁）级到共享锁（读锁）。

​

首先要知道 MySQL 有哪些锁，如上图所示，至少有 12 类锁（其中自增锁是事务向包含了 AUTO_INCREMENT 列的表中新增数据时会持有，predicate locks for spatial index 为空间索引专用，本文不讨论这 2 类锁）。

详细参考：科普文：软件架构数据库系列之【MySQL锁梳理】_一个sql语句相当于一个事务、-CSDN博客

死锁deadlocks

所谓死锁<DeadLock>: 是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。

由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。
一种情形，此时执行程序中两个或多个线程发生永久堵塞（等待），每个线程都在等待被其他线程占用并堵塞了的资源。例如，如果线程A锁住了记录1并等待记录2，而线程B锁住了记录2并等待记录1，这样两个线程就发生了死锁现象。
计算机系统中,如果系统的资源分配策略不当，更常见的可能是程序员写的程序有错误等，则会导致进程因竞争资源不当而产生死锁的现象。

产生死锁的主要原因

（1） 因为系统资源不足。
（2） 进程运行推进的顺序不合适。
（3） 资源分配不当等。
如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则
就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。

产生死锁的四个必要条件

（1） 互斥条件：一个资源每次只能被一个进程使用。
（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
（3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
（4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之
一不满足，就不会发生死锁。

MySQL死锁是什么？

mysql死锁是指两个或多个事务在执行过程中，因争夺锁资源而造成的相互等待的现象，若无外力干涉它们都将无法继续执行。

通俗来说，就是两个或多个事务在等待对方释放锁，从而造成僵持不下，使得整个系统陷入停滞状态。

科普文：软件架构数据库系列之【MySQL锁梳理】_一个sql语句相当于一个事务、-CSDN博客

从前面文章可知，innodb锁主要是：全局锁、表锁、行锁。

MySQL有三种锁的级别：页级、表级、行级。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

 

为什么表锁不会产生死锁？

‌这是因为表级锁在每次操作时都会获得所有的锁，下一个操作只能等待所有锁释放后才能执行，因此只可能出现等待超时，而不会出现锁竞争的情况‌。

表级锁的特点包括：开销小，加锁快，锁定粒度大，发生锁冲突的概率最高，并发度最低。

由于表级锁在每次操作时都会锁定整张表，其他事务需要等待所有锁释放后才能访问表中的数据，因此不会出现多个事务同时竞争同一资源的情况，从而避免了死锁的发生‌。

相比之下，行锁虽然并发度高，但由于锁的获取是逐步进行的，且事务隔离级别越高，加锁越严格，越容易出现锁竞争，因此更容易发生死锁。

页面锁的锁定粒度和并发度则介于表级锁和行级锁之间‌。

行锁、页面锁、表锁加锁过程，可参考：科普文：软件架构数据库系列之【MySQL 是怎么做并发控制的？】马国庆（翊云）-CSDN博客

MySQL死锁产生的原因

1事务的顺序执行

在多用户并发访问数据库时，如果每个用户都执行了一条需要等待其他事务释放资源的SQL语句，就可能引发死锁。

例如，有两个用户同时对表A和表B进行操作，用户1先锁定表A，用户2先锁定表B。此时，用户1需要等待用户2释放表B的锁才能继续操作，反之亦然。如果两个用户都一直等待对方释放锁，就会产生死锁。

2事务的竞争条件

当多个事务对同一数据进行操作时，如果它们的操作顺序不一致，也可能会产生死锁。

例如，有两个事务T1和T2分别对数据A和B进行修改操作。初始时，T1获取了A的锁并等待B的锁释放，而T2获取了B的锁并等待A的锁释放。这时，也会产生死锁。

MySQL死锁的影响

1. 事务无法继续执行，所有参与死锁的事务都会被回滚。

2. 可能会对数据库的性能产生负面影响，因为死锁可能会导致更多的事务失败。

3. 如果是在高并发环境下，死锁可能会导致系统稳定性问题。

MySQL数据库死锁会对性能产生负面影响，导致数据库操作被阻塞，从而影响系统的响应速度和并发能力。当发生死锁时，系统会自动进行死锁检测和解锁，这会消耗额外的系统资源和时间，导致数据库性能下降。

死锁的发生会造成数据库操作被阻塞，如果死锁发生频繁，会导致数据库长时间处于阻塞状态，严重影响系统的正常运行。此外，死锁的处理也会增加系统的负担，降低系统的稳定性和可靠性。

因此，为了避免死锁对性能的影响，需要通过合理的数据库设计和优化操作，减少死锁的发生概率，同时可以通过监控系统性能和实时处理死锁事件，及时解决问题，以保证系统的正常运行和性能表现。

MySQL如何避免死锁

1. 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；避免事务长时间运行，尽可能缩短事务的执行时间。

2. 保持事务简短并在一个批次中。

3. 使用更低的隔离级别，如READ COMMITTED。

4. 使用索引来减少死锁的可能性。

5. 避免事务的顺序执行：确保应用程序中的事务以相同的顺序访问对象。在并发访问数据库时，应尽量避免事务的顺序执行。具体做法包括：使用适当的索引、避免在事务中执行不必要的操作、尽可能使用行级锁而不是表级锁等。这样可以减少事务等待其他事务释放锁的时间，从而降低死锁的风险。

6. 避免事务的竞争条件：为了避免事务的竞争条件，可以采用以下措施：使用乐观锁、使用悲观锁、使用分布式锁等。乐观锁假设多个事务不会同时对同一数据进行操作，因此它们可以同时进行而不会相互干扰。悲观锁则假设多个事务会同时对同一数据进行操作，因此需要使用锁来保证数据的独占性。分布式锁则可以在多个数据库节点之间实现同步和协调操作。

7. 优化数据库结构和工作负载：针对可能产生死锁的场景，可以优化数据库结构和工作负载来降低死锁的风险。例如，针对频繁进行转账操作的账户可以使用单独的表来存储转账记录以减少对主表的锁定范围、使用适当的隔离级别和超时参数等。同时要确保数据库的性能参数（如缓冲区大小、连接数等）设置合理以避免资源争用和过度的并发访问。

8. 使用锁等待超时，通过innodb_lock_wait_timeout设置。设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；

9. 实现重试逻辑，在事务失败时自动重新尝试。

10. 使用锁的粒度控制，尽量只锁定必要的资源。

11.  优化程序，检查并避免死锁现象出现；

        1）合理安排表访问顺序
        2）在事务中尽量避免用户干预，尽量使一个事务处理的任务少些。
        3）采用脏读技术。脏读由于不对被访问的表加锁，而避免了锁冲突。在客户机/服务器应用环境中，有些事务往往不允许读脏数据，但在特定的条件下，我们可以用脏读。
        4）数据访问时域离散法。数据访问时域离散法是指在客户机/服务器结构中，采取各种控制手段控制对数据库或数据库中的对象访问时间段。主要通过以下方式实现: 合理安排后台事务的执行时间，采用工作流对后台事务进行统一管理。工作流在管理任务时，一方面限制同一类任务的线程数（往往限制为1个），防止资源过多占用; 另一方面合理安排不同任务执行时序、时间，尽量避免多个后台任务同时执行，另外，避免在前台交易高峰时间运行后台任务
        5）数据存储空间离散法。数据存储空间离散法是指采取各种手段，将逻辑上在一个表中的数据分散到若干离散的空间上去，以便改善对表的访问性能。主要通过以下方法实现: 第一，将大表按行或列分解为若干小表; 第二，按不同的用户群分解。
        6）使用尽可能低的隔离性级别。隔离性级别是指为保证数据库数据的完整性和一致性而使多用户事务隔离的程度，SQL92定义了4种隔离性级别：未提交读、提交读、可重复读和可串行。如果选择过高的隔离性级别，如可串行，虽然系统可以因实现更好隔离性而更大程度上保证数据的完整性和一致性，但各事务间冲突而死锁的机会大大增加，大大影响了系统性能。
        7）使用Bound Connections。Bound connections 允许两个或多个事务连接共享事务和锁，而且任何一个事务连接要申请锁如同另外一个事务要申请锁一样，因此可以允许这些事务共享数据而不会有加锁的冲突。
        8）考虑使用乐观锁定或使事务首先获得一个独占锁定。 

MySQL检测和解决死锁的步骤

1. 开启MySQL的慢查询日志，查找可能产生死锁的慢查询。

2. 使用SHOW ENGINE INNODB STATUS;查看死锁信息。

3. 使用SHOW PROCESSLIST;查看当前的事务和锁信息。

4. 查看锁状态变量： show status like ‘innodb_row_lock%’ 命令检查状态变量，分析系统中的行锁的争夺情况。

5. 分析死锁日志，确定导致死锁的具体原因。

6. 根据分析结果采取上述措施来解决死锁问题。

MySQL死锁检测：SHOW ENGINE INNODB STATUS

查看死锁日志：
通过 show engine innodb status \G 命令查看近期死锁日志信息，主要关注日志中的 LATEST DETECTED DEADLOCK 部分;

使用方法：

（1）查看近期死锁日志信息；
（2）使用 explain 查看下 SQL 执行计划。

MySQL死锁检测：事务和锁

参考：科普文：软件架构数据库系列之【MySQL5.7的系统表梳理】_mysql 5.7系统表-CSDN博客

Mysql事务和锁的一些系统表：​‌performance_schema‌库

INNODB_LOCKS:现在获取的锁，但是不含没有获取的锁，而且只是针对INNODB的。

INNODB_LOCK_WAITS：系统锁等待相关信息，包含了阻塞的一行或者多行的记录，而且还有锁请求和被阻塞改请求的锁信息等。

INNODB_TRX：包含了所有正在执行的的事物相关信息（INNODB），而且包含了事物是否被阻塞或者请求锁。

processlist（SHOW PROCESSLIST;查看当前的事务和锁信息）：processlist表为MySQL的核心表之一。MySQL processlist 表示当前由服务器内执行的线程集执行的操作。 进程列表表是进程信息的来源之一。

MySQL :: MySQL 8.0 Reference Manual :: 29.12.21.7 The processlist Table

COMMAND字段说明参考：MySQL :: MySQL 8.0 Reference Manual :: 10.14.2 Thread Command Values

STATE 字段说明参考：MySQL :: MySQL 8.0 Reference Manual :: 10.14.3 General Thread States

INFORMATION_SCHEMA 库中的 PROCESSLIST在8.0以后的版本被标记为弃用，并将在未来的 MySQL 版本中被移除。因此，使用此表的 SHOW PROCESSLIST 的实现也已被弃用。建议改用 performance_schema 实现的 processlist。

在结构上，两张表的结构基本相同，高版本中performance_schema下的表比infomation_schema中表多一个execution_engine(执行引擎)字段

processlist表可以直接被访问。如果用户拥有 PROCESS权限，则可以访问所有线程的信息（包含其他用户的线程信息）。非匿名用户可以访问自己的线程信息、但不能访问其他用户的线程信息，匿名用户无法访问processlist中的线程信息。

processlist表结构和字段说明

1. 使用方法：
mysql> select * from performance_schema.processlist\G;
*************************** 1. row ***************************
     ID: 5
   USER: event_scheduler
   HOST: localhost
     DB: NULL
COMMAND: Daemon
   TIME: 21411
  STATE: Waiting on empty queue
   INFO: NULL
*************************** 2. row ***************************
     ID: 714
   USER: root
   HOST: localhost
     DB: performance_schema
COMMAND: Query
   TIME: 0
  STATE: executing
   INFO: select * from performance_schema.processlist
2 rows in set (0.00 sec)
2. 表结构：
mysql> desc processlist;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| ID      | bigint unsigned | NO   | PRI | NULL    |       |
| USER    | varchar(32)     | YES  |     | NULL    |       |
| HOST    | varchar(261)    | YES  |     | NULL    |       |
| DB      | varchar(64)     | YES  |     | NULL    |       |
| COMMAND | varchar(16)     | YES  |     | NULL    |       |
| TIME    | bigint          | YES  |     | NULL    |       |
| STATE   | varchar(64)     | YES  |     | NULL    |       |
| INFO    | longtext        | YES  |     | NULL    |       |
+---------+-----------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

3.字段说明
ID
连接标识符。这是在 SHOW PROCESSLIST 语句的 Id 列中显示的相同值，显示在 Performance Schema 线程表的 PROCESSLIST_ID 列中，并在线程内通过 CONNECTION_ID() 函数返回的值相同。

USER
发出该语句的 MySQL 用户。system user 的值指的是由服务器生成的非客户端线程，用于在内部处理任务，例如延迟行处理线程或在复制主机上用于处理 I/O 或 SQL 的线程。对于 system user，Host 列中没有指定主机。unauthenticated user 指的是已与客户端连接关联但尚未对客户端用户进行身份验证的线程。event_scheduler 指的是监视预定事件的线程。

HOST
发出该语句的客户端的主机名（对于 system user 则没有主机）。对于 TCP/IP 连接，主机名以 host:port(主机地址:端口) 的格式显示，以便更容易确定哪个客户端在执行什么操作。

DB
线程的默认数据库，如果未选择任何数据库，则为 NULL。

COMMAND
线程代表客户端执行的命令类型，如果会话处于空闲状态，则为 Sleep。此列的值对应于客户端/服务器协议的 COM_xxx 命令和 Com_xxx 状态变量。

TIME
线程在当前状态下已经经过的时间，以秒为单位。对于复制 SQL 线程，该值是上一个复制事件的时间戳与复制主机的实际时间之间的秒数。

STATE
一个动作、事件或状态，表示线程正在进行的操作。大多数状态对应非常快速的操作。如果一个线程在特定状态停留了很多秒，可能存在需要调查的问题。

INFO
线程正在执行的语句，如果没有执行语句则为 NULL。该语句可以是发送给服务器的语句，或者如果该语句执行其他语句，则为最内层的语句。

EXECUTION_ENGINE
查询执行引擎。该值可以是 PRIMARY 或 SECONDARY。在 MySQL HeatWave Service 和 HeatWave 中使用，其中 PRIMARY 引擎是 InnoDB，而 SECONDARY 引擎是 HeatWave（RAPID）。对于社区版本、企业版（本地部署）以及没有 HeatWave 的 MySQL HeatWave Service，该值始终为 PRIMARY。

4. COMMAND和STATE字段值说明
COMMAND字段说明：
Binlog Dump 表示这是一个在复制源上的线程，负责将二进制日志内容发送到副本
Change user 表示该线程正在执行一个改变用户操作
Close stmt 该线程正在关闭一个 prepared statement.
Connect 被连接到源的复制接收线程和复制工作线程使用。
Connect Out 一个副本正在连接到其源。
Create DB 正在执行一个创建数据库操作
Daemon 该线程是服务器内部的线程，而不是为客户端连接提供服务的线程
Debug 线程正在生成调试信息
Delayed insert 该线程是一个延迟写入处理程序
Drop DB 该线程正在执行删除数据库操作
Error 错误
Execute 线程正在执行 prepared statement
Fetch 线程正在获取 prepared statement 的结果
Field List 线程正在检索表列的信息
Init DB 该线程正在选择默认数据库
Kill 正在杀死另外一个线程
Long Data 线程正在检索执行准备语句的结果中的长数据
Ping 该线程正在处理服务器 ping 请求
Prepare
Processlist
Query Employed for user clients while executing queries by single-threaded replication applier threads, as well as by the replication coordinator thread.
Quit The thread is terminating.
Refresh The thread is flushing table, logs, or caches, or resetting status variable or replication server information
Register Slave The thread is registering a replica server
Reset stmt The thread is resetting a prepared statement.
Set option The thread is setting or resetting a client statement execution option.
Shutdown The thread is shutting down the server.
Sleep The thread is waiting for the client to send a new statement to it.
Statistics The thread is producing server status information.
Time （目前未使用该状态）
STATE字段状态说明
Sleep：连接处于空闲状态，没有正在执行的命令。
Query：连接正在执行一个查询。
Locked：连接正在等待锁定资源。
Copying to tmp table：连接正在将结果复制到临时表中。
Sending data：连接正在发送查询结果给客户端。
Sorting result：连接正在对结果进行排序。
Waiting for table flush：连接正在等待表的刷新。
Repair by sorting：连接正在执行表的修复操作。
Creating sort index：连接正在创建排序索引。

MySQL死锁检测：锁状态 show status like ‘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：系统启动后到现在总共等待的次数。频繁的锁等待可能指出需要优化查询或数据库结构。

如果Innodb_row_lock_current_waits等待次数高，而且Innodb_row_lock_time_avg每次等待时间长，需要分析系统中为什么会有如此多的等待，然后着手定制优化。

MySQL死锁检测：实操

5.0后，增加了3个关于锁的表
MySQL5.7版本
INFORMATION_SCHEMA.innodb_trx 当前运行的所有事务
INFORMATION_SCHEMA.innodb_locks 当前出现的锁
INFORMATION_SCHEMA.innodb_lock_waits 锁等待的对应关系
MySQL8.0版本
8.0后，performance_schema.data_locks代替了INFORMATION_SCHEMA.innodb_locks ，performance_schema.data_lock_waits代替了INFORMATION_SCHEMA.innodb_lock_waits



# 确认有没有锁等待:
show status like 'innodb_row_lock%';
​
select * from information_schema.innodb_trx;
​
# 查询锁等待详细信息 blocking_pid(锁源的连接线程)
select * from sys.innodb_lock_waits; 
​
# 通过连接线程ID找SQL线程语句
select * from performance_schema.threads;
​
# 通过SQL线程找到SQL语句
select * from performance_schema.events_statements_history;
# 查看锁状态
select trx_id,trx_state,trx_started,trx_tables_locked,trx_rows_locked from innodb_trx;
​
select * from sys.innodb_lock_waits;
​
# 查看进程信息
show full processlist;
​
# 查看进程ID为2008的线程信息
select thread_id,processlist_id from performance_schema.threads where processlist_id=146;
​
# 根据线程ID，找到真正执行的SQL语句select thread_id,sql_text from performance_schema.events_statements_history where thread_id=846;​



# 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; -- 5.7
SELECT * FROM performance_schema.data_locks; -- 8.0

# 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; -- 5.7
SELECT * FROM performance_schema.data_lock_waits; -- 8.0

# 查看行锁情况
show status like 'InnoDB_row_lock%';

# 查看表锁情况
show status like 'table%';

MySQL死锁检测：8步死锁定位思路

出现死锁问题时，可以查看系统设置的隔离级别，RR与RC在加锁粒度上存在很大区别，RR隔离级别因为加锁粒度更大，在并发更新表的场景下容易产生死锁，通常是因为UPDATE语句的where条件所带的字段是普通索引，通过变更成唯一索引可以解决死锁问题。也可以评估业务系统是否兼容RC隔离级别，直接切换隔离级别。

在分析innodb中锁阻塞时，几种方法的对比情况：

• （1）使用show processlist查看不靠谱；
• （2）直接使用show engine innodb status查看，无法判断到问题的根因；
• （3）使用mysqladmin debug查看，能看到所有产生锁的线程，但无法判断哪个才是根因；
• （4）开启innodb_lock_monitor后，再使用show engine innodb status查看，能够找到锁阻塞的根因。SET GLOBAL innodb_status_output = 'ON';   SET GLOBAL innodb_status_output_locks = 'ON';设置完成后，当你执行SHOW ENGINE INNODB STATUS;命令时，你会看到关于锁的额外信息。为了关闭监控，使用以下命令SET GLOBAL innodb_status_output = 'OFF';  SET GLOBAL innodb_status_output_locks = 'OFF';请记得，频繁开启和关闭这些设置可能会影响数据库性能。通常建议只在需要时才开启这些监控选项。

一般思路：

（1）查看当前有无锁等待
mysql> show status like 'innodb_row_lock%';
（2）查看哪个事务在等待(被阻塞了)
mysql> select * from information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT'\G
（3）查询该事务被哪个事务给阻塞了 从innodb_trx获取到被阻塞的trx_id是3934，阻塞该事务的事务id是3933
mysql> SELECT * FROM performance_schema.threads WHERE processlist_id=970\G
（4）根据trx_id，从innodb_trx表可查询到trx_mysql_thread_id线程id为970
mysql> select * from information_schema.innodb_trx where trx_id=3933 \G
（5）根据线程id，查询表拿到thread_id为995
mysql> SELECT * FROM performance_schema.threads WHERE processlist_id=970\G
（6）根据thread_id，查询当前锁源的sql
mysql> SELECT * FROM performance_schema.events_statements_current WHERE thread_id=995\G
（7）分析SQL、分析锁表原因：根据thread_id，查询当前锁源的sql
（8）解决死锁：锁等待超时或者kill被锁表的进程
查进程，主要是查找被锁表的那个进程的ID：SHOW PROCESSLIST;
kill掉锁表的进程ID：KILL 10866;后面的数字即时进程的ID
-- 筛选报错
show processlist where user = 'root';
-- 筛选可用
select * from information_schema.processlist where user = 'root';

1.如何知道系统有没有发生过死锁，如何去查看发生过的锁

show status like ‘innodb_row_lock%'; 从系统启动到现在的数据

Innodb_row_lock_current_waits：当前正在等待锁的数量；

Innodb_row_lock_time ：锁定的总时间长度，单位ms；

Innodb_row_lock_time_avg ：每次等待所花平均时间；

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

Innodb_row_lock_waits ：从系统启动到现在总共等待的次数。

平均时间和锁等待次数比较大的话，说明可能会存在锁争用情况

2. show engine innodb status

展示innodb存储引擎的运行状态

通过这个命令显示的内容比较多，其中有一项lasted detected deadlock 显示最近发生的死锁。

图中红色线条标注的是执行的SQL，以及加了什么锁，可以看出是在这行记录上加了X锁，没有gap锁。

开启和关闭锁监控

开启监控后，可以看到锁的具体信息以及加锁顺序等，更容易分析死锁。

方式一：创建监控表，监控某个数据库
开启：
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB; 
关闭：
DROP TABLE innodb_lock_monitor; 
注意：在未拥有设置全局属性权限下也可以开启该功能。


方式二，开启全局监控
开启：
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON; 
关闭：
set GLOBAL innodb_status_output=OFF;
set GLOBAL innodb_status_output_locks=OFF;

查看数据库状态
show engine innodb status \G;

3. 错误日志中查看历史发生过的死锁

set global innodb_print_all_deadlocks=1;

上一个命令，只能看到最近发生的锁，如果我想看历史发生的锁怎么办？ 执行这一句，更改innodb 的一个配置，innodb_print_all_deadlocks，打印所有的死锁。会将死锁的信息输出到mysql的错误日志中，默认是不输出，格式和show engine innodb status 是差不多的。

4. 锁信息information_schema.innodb_locks

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; -- 5.7
SELECT * FROM performance_schema.data_locks; -- 8.0

information_schema 是mysql自带的系统库，保存着关于MySQL所维护的所有其他数据库的信息。其中innodb_locks表，记录了事务请求但是还没获得的锁，即等待获得的锁。

lock_id：锁的id，由锁住的空间id编号、页编号、行编号组成

lock_trx_id：锁的事务id。

lock_mode：锁的模式。S[,GAP], X[,GAP], IS[,GAP], IX[,GAP]

lock_type：锁的类型，表锁还是行锁

lock_table：要加锁的表。

lock_index：锁住的索引。

lock_space：innodb存储引擎表空间的id号码

lock_page：被锁住的页的数量，如果是表锁，则为null值。

lock_rec：被锁住的行的数量，如果表锁，则为null值。

lock_data：被锁住的行的主键值，如果表锁，则为null值。

5. 锁等待信息information_schema.innodb_lock_waits

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; -- 5.7
SELECT * FROM performance_schema.data_lock_waits; -- 8.0

查看等待中的锁

requesting_trx_id：申请锁资源的事务id。

requested_lock_id：申请的锁的id。

blocking_trx_id：阻塞的事务id，当前拥有锁的事务ID。

blocking_lock_id：阻塞的锁的id，当前拥有锁的锁ID

6. 事务信息information_schema.innodb_trx

查看已开启的事务

trx_id：innodb存储引擎内部事务唯一的事务id。

trx_state：当前事务的状态。

trx_started：事务开始的时间。

trx_requested_lock_id：等待事务的锁id，如trx_state的状态为LOCK WAIT，那么该值代表当前事务之前占用锁资源的id，如果trx_state不是LOCK WAIT的话，这个值为null。

trx_wait_started：事务等待开始的时间。

trx_weight：事务的权重，反映了一个事务修改和锁住的行数。在innodb的存储引擎中，当发生死锁需要回滚时，innodb存储引擎会选择该值最小的事务进行回滚。

trx_mysql_thread_id：正在运行的mysql中的线程id，show full processlist显示的记录中的thread_id。

trx_query：事务运行的sql语句

7.分析锁表的SQL

通过sql日志，分析相应SQL，给表加索引，常用字段加索引，表关联字段加索引等方式对sql进行优化。

8.死锁解决：锁等待超时或者kill被缩表的那个进程

锁表怎么解决？MySQL锁表怎么解锁：

1. 查进程，主要是查找被锁表的那个进程的ID：SHOW PROCESSLIST;
2. kill掉锁表的进程ID：KILL 10866;后面的数字即时进程的ID

注意：筛选报错

-- 筛选报错
show processlist where user = 'root';
-- 筛选可用
select * from information_schema.processlist where user = 'root';

当发生死锁时，MySQL 会自动回滚其中一个事务，开发人员需要捕获并处理这种异常。

在代码中，你可以使用如下方式处理死锁：

try {
    // 执行事务
    ...
} catch (SQLException e) {
    if (e.getErrorCode() == 1213) { // 1213 代表死锁错误代码
        // 死锁检测，进行重试
        retryTransaction();
    } else {
        // 其他异常处理
        throw e;
    }
}

 通过捕获死锁异常并进行适当的重试，系统可以在发生死锁后继续执行，从而提升系统的健壮性。

MySQL的锁参数配置

在 MySQL中，可以通过以下配置来管理和调整死锁的处理：

• innodb_deadlock_detect：默认情况下，该选项启用了InnoDB存储引擎的死锁检测机制。可以通过设置该选项为0来禁用死锁检测，但这会增加发生死锁的风险。
  SET GLOBAL innodb_deadlock_detect = 0;
• innodb_deadlock_detect_interval：该选项用于配置死锁检测的时间间隔，默认为1秒。可以通过增加或减少该值来调整死锁检测的频率。
  SET GLOBAL innodb_deadlock_detect_interval = <interval>;
• innodb_lock_wait_timeout：该选项配置了InnoDB存储引擎的锁等待超时时间，默认为50秒。如果一个事务在等待锁定的时间超过该值，将会被自动回滚。
  SET GLOBAL innodb_lock_wait_timeout = <timeout>;
• innodb_print_all_deadlocks：该选项配置了是否将所有死锁信息都输出到错误日志中，默认为OFF。可以将其设置为ON来启用输出所有死锁信息。
  SET GLOBAL innodb_print_all_deadlocks = ON;
• show engine innodb status：通过执行该语句，可以查看InnoDB存储引擎的状态信息，包括死锁相关的信息。
  SHOW ENGINE INNODB STATUS;