MySQL死锁是什么，如何解决？

转载：

https://mp.weixin.qq.com/s?__biz=MzI2OTEzNzU3Nw==&mid=2247484763&idx=1&sn=8aa243a7c74259f830496f19cf74b490&chksm=eae5a69ddd922f8ba5281ae2e097382a13ff01cee42b561a8c43ff93d9561432ecf47fa84b38&mpshare=1&scene=1&srcid=0531MmftWyT3jaQPNqS0RWNC&sharer_shareinfo=4cf1a1a3727247e31ca4c23f261ffcc9&sharer_shareinfo_first=4cf1a1a3727247e31ca4c23f261ffcc9&exportkey=n_ChQIAhIQBp11Ubj7CinbVYeOisUSNhL0AQIE97dBBAEAAAAAAA1nObtisgEAAAAOpnltbLcz9gKNyK89dVj09eV889aFTEx1MfLRBVgpcmfzemgCSKNU7U1gN5SRgM%2FKtteXMjfJY%2FdZjXVs4FOk53NaxcOg3M6L2nWPMccOGRYVWY5tD4znmqCijFBU6a%2BRCW46gjDx9mt1gHaz8ECUeXTjuxrAkNn8hA1fQwmqGvdnVGgtv1Dd2XWCYTeUkz7JvYOSkLvJzok82zzjr1Ez7265t4yZniezmSyFjJ2%2BepIDl2NRZaPblBowrvJCwT2HggXLs2x%2B1xRT1x%2Bi0EJdtstCYHF%2F93eaIS9FLAw%3D&acctmode=0&pass_ticket=FrL1VUfMouY39ZS7HPsQ%2FfmCzit1NrwRTJuigZsOvnKuM1zV5EKJjBhbJM1jy5jc&wx_header=0#rd

1. 什么是MySQL死锁？

MySQL中的死锁是指两个或多个操作相互等待对方释放资源，结果导致他们都无法继续执行的情况。

可以想象成两个人各自拿着对方需要的东西，却都僵持不下，谁也不愿意先放手。

在数据库操作中，这通常发生在多个事务同时试图锁定对方已锁定的资源时。

2. MySQL锁的分类

MySQL的锁可以从操作的粒度进行分类，主要包括表级锁、行级锁和页级锁。

不同类型的锁在数据库操作中的应用会影响数据库的性能和并发处理能力。

表级锁：

表级锁是最简单的锁类型，当操作涉及到整个表时使用。

这种锁会锁定整个表，使得任何其他需要访问这个表的操作都必须等待锁被释放。

例如：

--手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
--查看表上加过的锁
show open tables;
--删除表锁
unlock tables;

优点是简单易管理，但缺点是并发能力较低，尤其在处理大量事务时，效率会大大降低。

行级锁：

行级锁是最精细的锁，只锁定数据表中的特定行。

行级锁的特点：

每次操作锁住一行数据。开销大，加锁慢；
会出现死锁；
锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB相对于MYISAM的最大不同有两点：

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

注意，InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁。(RR级别会升级为表锁，RC级别不会升级为表锁)

比如我们在RR级别执行如下sql:

--where条件里的name字段无索引
select * from account where name = 'lilei' for update;

则其它Session对该表任意一行记录做修改操作都会被阻塞住

页级锁：

页级锁介于表级锁和行级锁之间，不是MySQL默认使用的锁类型。

它锁定数据库页（即一组行），粒度比表级锁细，但比行级锁粗。页

级锁可以减少锁的数量，从而降低锁竞争，但可能会引起更多的锁冲突，尤其是当多个事务频繁修改相邻的数据时。

3 从操作的类型进行的mysql锁的分类

读锁（S锁）：

性能特点：

读锁也被称为共享锁，它允许一个事务去读取一行数据，并防止其他事务对这行数据进行写操作，直到读锁被释放。

但是，多个事务可以同时持有对同一资源的读锁，这意味着多个用户可以同时读取数据，而不会相互影响。

例如：

select * from T where id=1 lock in share mode

适用场景：

适用于那些读取操作远多于写入操作的场景。

因为读锁可以共享，它支持高并发的读取操作，使得多个用户可以同时查询数据而不会阻塞彼此。

写锁（X锁）：

性能特点：

写锁又称为排他锁，当一个事务对数据进行修改时，它会加上写锁。这个锁会阻止其他任何事务读取或修改同一数据，直到写锁被释放。

写锁保证了数据在修改期间的完整性和一致性。

例如：

select * from T where id=1 for update

适用场景：

适用于需要修改数据的操作。

由于写锁会阻塞其他所有试图访问相同数据的操作，所以在写入量大或者数据频繁更新的应用中，写锁可能会成为性能瓶颈。

意向锁：

性能特点：

意向锁是一种较为特殊的锁，它不直接锁定数据，而是表明一个事务意图对数据行进行加锁（读锁或写锁）。

意向锁主要分为：

意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。
意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。

这主要用于支持多级锁定协议，通过在表级别设置意向锁，来预告即将在行级别上加的锁，这样就能在不同粒度的锁之间提供一种兼容性检查。

适用场景：

意向锁主要用于提高并发性能，特别是在存在多粒度锁定（即同时使用表级锁和行级锁）的情况下。

它允许数据库系统更有效地管理锁，避免不必要的锁冲突，提高操作效率。

4 从操作的性能进行的mysql锁的分类

从操作的性能可分为乐观锁和悲观锁

乐观锁：

性能特点：

乐观锁基于这样一个假设，即冲突发生的概率较低，大多数时间不会有冲突。

因此，它在操作过程中不会立即锁定资源。通常，乐观锁会通过检查数据在读取和更新期间是否被修改来实现，常用的方法是使用版本号或时间戳。只有在数据提交更新时，才会检查版本信息是否一致，如果不一致，表示数据在此期间已被其他操作更改，此时操作会失败。

适用场景：

适用于读多写少的场景，比如在线阅读平台的用户数据处理，因为在这种场景中，数据冲突的可能性较低，使用乐观锁可以减少锁的开销，提高系统的整体性能。

悲观锁：

性能特点：

悲观锁恰恰相反，它假设冲突很常见，因此在整个数据处理过程中会保持数据锁定，直到事务完成。

这意味着当一个事务在操作数据时，其他事务必须等待直到锁被释放。悲观锁可以通过数据库自带的锁机制实现，比如行锁或表锁。

适用场景：

适合写操作多的场景，比如银行账户处理，在这种场景下，数据安全和一致性比性能更为重要。悲观锁通过锁定数据来避免数据在多个事务间的冲突，确保数据操作的安全性。

5 InnoDB存储引擎三种行锁模式

当我们谈论InnoDB存储引擎中的行锁模式时，可以把它想象成一种确保数据库中数据安全和准确的机制。这里有三种主要的行锁模式，分别是记录锁、间隙锁和临键锁。

5.1、记录锁（Record Locks）：

记录锁是最直接的一种锁。它就像是给数据库中某条具体数据上了一把锁。

这种锁定的目的是为了防止其他用户在事务处理期间修改这条数据。

比如说，如果你正在更新一条记录，系统就会对这条记录加锁，确保没有其他人能够同时修改这条记录，直到你的事务完成。

5.2、间隙锁（Gap Locks）：

间隙锁不是锁定单个记录，而是锁定一个范围，但这个范围不包括实际的记录。

想象一下，你想在两条已有记录之间插入一条新记录，间隙锁就会防止其他事务插入到这两条记录之间的空隙里。

Mysql默认级别是repeatable-read，有幻读问题，间隙锁是可以解决幻读问题的。

假设account表里数据如下：

那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，在Session_1下面执行如下sql：

select * from account where id = 18 for update;

则其他Session没法在这个(10,20)这个间隙范围里插入任何数据。
如果执行下面这条sql：

select * from account where id = 25 for update;

则其他Session没法在这个(20,正无穷)这个间隙范围里插入任何数据。

也就是说，只要在间隙范围内锁了一条不存在的记录会锁住整个间隙范围，不锁边界记录，这样就能防止其它Session在这个间隙范围内插入数据，就解决了可重复读隔离级别的幻读问题。

5.3、临键锁（Next-Key Locks）：

临键锁是记录锁和间隙锁的结合体。它同时锁定一条记录和它前面的空隙。

这种锁的作用是，它不仅防止其他用户修改当前的记录，还防止他们在前面的空隙中添加新记录。

这种锁模式是InnoDB用来保持数据完整性和避免幻读（即在一个事务内读取到之前不存在的数据行）的一种方式。

6 事务隔离级别和锁的关系

6.1 数据库事务的隔离级别

据库事务的隔离级别，目前数据库事务的隔离级别一共有 4 种，由低到高分别为：

事务的四个隔离级别：

未提交读（READ UNCOMMITTED）：
所有事务都可以看到其他事务未提交的修改。一般很少使用；
读已提交（READ COMMITTED）：
Oracle默认隔离级别，事务之间只能看到彼此已提交的变更修改；
可重复读（REPEATABLE READ）：
MySQL默认隔离级别，同一事务中的多次查询会看到相同的数据行；可以解决不可重复读，但可能出现幻读；
可串行化（SERIALIZABLE）：
最高的隔离级别，事务串行的执行，前一个事务执行完，后面的事务会执行。读取每条数据都会加锁，会导致大量的超时和锁争用问题；

数据库一般默认的隔离级别为读已提交 RC ，比如 Oracle，

也有一些数据的默认隔离级别为可重复读 RR，比如 Mysql。

“可重复读”（Repeatable Read）这个级别确保了对同一字段的多次读取结果是一致的，除非数据是被本身事务自己所修改。

RR它能够防止脏读、不可重复读，但可能会遇到幻读的情况。

6.2 深入探讨事务隔离级别与锁之间的关联

事务隔离级别，源于SQL92标准，为数据库事务的并发控制提供了一种全面解决方案。

它实质上是将锁机制和MVCC（多版本并发控制）技术进行了封装，从而简化了底层实现的复杂性，为用户提供了一个更加友好的接口。

锁，作为数据库实现并发访问控制的核心机制，确保了事务的隔离性。

通过为不同的数据操作施加相应的锁，数据库能够防止多个事务同时读写同一数据，从而维护数据的一致性和完整性。

对于开发者而言，他们通常首先选择一个合适的事务隔离级别。仅当所选的隔离级别无法满足特定的并发需求或解决问题时，才需要考虑在代码中显式地使用锁进行更精细的控制。

值得注意的是，MySQL的默认事务隔离级别是“可重复读”，但根据实际的应用需求，“读已提交”隔离级别也常被推荐，因为它在并发控制和性能之间达到了较好的平衡。

相比之下，Oracle和SQLServer的默认隔离级别则是“读已提交”。

7 死锁产生原因和解决方案

InnoDB与MyISAM的最大不同有两点

支持事务
采用行锁行级锁和表级锁本来就有许多不同之处，另外，事务的引入也带来了一些问题，比如死锁。

7.1、表级锁死锁

假设有两个事务，分别在操作两个表：表A和表B。
事务1试图先锁定表A，然后锁定表B。
而事务2试图先锁定表B，然后锁定表A。
用户A和用户B加锁的顺序如下：

用户A–》表1（表锁）–》表2（表锁）
用户B–》表2（表锁）–》表1（表锁）

如果这两个事务同时执行，它们将会相互等待对方释放锁，从而导致死锁。

解决方案：

这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。

仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，
尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理，必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

7.2、行级锁死锁

产生原因1

如果在事务中执行了一条没有索引条件的查询，引发全表扫描，行锁膨胀为表锁（或者等价于表级锁）

多个这样的锁表事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。

解决方案：

SQL语句中不要使用太复杂的关联多表的查询；
使用explain“执行计划”对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化。

产生原因2

两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁例如：模拟死锁场景

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;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁，这种情况我们可以通过日志分析找到对应事务线程id，可以通过kill杀掉

7.3、预防死锁优化实践

1. 维持一致的锁定顺序

确保所有事务都以相同的顺序获取锁。这可以减少锁定冲突的可能性，因为事务不会因为等待其他事务释放锁而相互阻塞。

例如，如果有多个表或资源需要锁定，总是按照相同的顺序（如字典顺序）锁定这些资源。

2. 使用最小的锁粒度

尽量使用行级锁而不是表级锁。

行级锁允许更高的并发，因为它仅锁定需要修改或查询的数据行，而不是整个表。这样，即使多个事务操作同一表，它们也可能操作不同的行，从而减少死锁的风险。

3. 减少事务持续时间

尽量缩短事务的执行时间。

长事务占用锁的时间越长，与其他事务发生冲突的可能性就越大。你可以通过优化查询语句和减少事务中的操作数来减少事务持续时间。

4. 使用锁超时

在某些数据库管理系统中，可以设置锁的超时时间。

这意味着事务在等待锁超过设定的时间后将自动回滚。这不仅可以防止死锁，还可以避免一个事务无限期地等待资源。

5. 死锁检测和回滚

启用数据库的死锁检测功能，让数据库管理系统能够自动检测死锁并回滚某个事务来解锁。

这通常是最后的手段，因为它可能导致数据不一致的问题。应当只在其他方法都无法实现时使用。

6. 避免不必要的锁

审查和优化事务逻辑，确保只锁定必要的资源。

例如，如果事务只读取数据而不进行修改，可以考虑使用非锁定读（例如，在MySQL中使用SELECT … WITH (NOLOCK)）。

7. 使用乐观并发控制

在一些场景中，使用乐观并发控制（OCC）而不是悲观锁定可能更合适。OCC通过在事务提交时检查数据是否已被其他事务修改来避免锁定，适用于读多写少的场景。

8. 避免无索引行锁升级为表锁

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

9. 监控和日志记录

实施监控和日志记录来跟踪死锁和性能瓶颈。这可以帮助识别导致死锁的具体事务和操作，从而进行针对性的优化。

总结

认真看完本文的小伙伴，看到这里应该对MySQL死锁有一个清晰的认知了，在日常生产环境中，如果遇到死锁了，该怎么办呢？

首先我们想到是要如何解决死锁（通过日志分析，找到它，然后kill掉它）
第二步才是我们要分析死锁产生的原因，尽快优化掉它。

92IT

Just love IT.