死锁的原因

• 竞争资源：当多个事务同时请求和持有相同的资源（如行级锁、表级锁）时，可能会导致死锁。
• 事务执行顺序：当多个事务按不同的顺序请求和释放资源时，可能会产生死锁的可能性，这是因为事务的执行顺序无法保证一致性。

解决死锁问题的常用策略

死锁检测和处理

MySQL 提供了死锁检测机制，可以通过设置参数 innodb_deadlock_detect 来启用。当检测到死锁时，可以选择回滚某些事务以解除死锁。但这种方法不能完全避免死锁的发生，而且会增加系统的开销。

加锁顺序

通过约定事务对资源的访问顺序，使得所有事务按相同的顺序请求锁定，可以避免死锁的发生。这种方法需要根据具体的业务需求和数据访问模式来设计，且不适用于复杂的场景。

降低事务隔离级别

将事务的隔离级别降低至 READ COMMITTED ，可以减少死锁的机会。但这也会导致数据一致性的问题，需要在业务层做相应的处理。

超时机制

对于长时间持有锁资源的事务，可以设置超时时间，在超时后自动回滚事务，以避免死锁的发生。这种方法需要谨慎设置超时时间，避免正常事务被错误回滚。

使用数据库引擎的特性解决死锁问题

InnoDB 引擎

InnoDB 引擎提供了一些特性来解决死锁问题。InnoDB 引擎支持行级锁，可以减少锁冲突和死锁的可能性。InnoDB 引擎提供了自适应哈希索引和自适应隔离级别等特性，可以根据实际的负载和并发情况自动调整锁定策略和隔离级别。 死锁超时：InnoDB 引擎提供了死锁超时机制，可以通过设置参数 innodb_lock_wt_timeout 来指定超时时间。当事务请求锁资源超过指定的时间后，将自动回滚事务，以解除死锁。

优化数据库设计和查询操作

合理设计数据库表结构

通过合理的数据库表结构设计，减少不必要的锁冲突和死锁风险。例如，避免在高并发场景下对同一行数据进行频繁的更新操作。

优化查询语句

通过合理的索引设计和优化查询语句，减少查询的范围和耗时，降低锁定资源的时间，从而减少死锁的可能性。

定期监控和调优

监控死锁事件

定期监控数据库系统中的死锁事件，及时发现问题，并采取相应的措施解决。

性能调优

通过系统性能测试和分析，找出数据库系统中存在的性能瓶颈和潜在的死锁风险，进行针对性的调优，提高系统的并发性能。

结论

MySQL 事务中的死锁问题是一种常见的并发访问现象，对数据库系统的性能和可用性产生重大影响。通过合理的策略和技术手段，可以有效解决死锁问题。选择合适的死锁检测和处理机制，约定事务的加锁顺序，降低事务隔离级别等策略，以及利用数据库引擎的特性和优化数据库设计和查询操作等方法，都可以有效预防和解决死锁问题。在实际应用中，根据具体的业务需求和数据库负载情况，结合以上解决策略，可以达到更好的性能提升效果，提高数据库系统的并发性能和可靠性。

---

解决一次mysql死锁问题

多线程开启事务处理。每个事务有多个update操作和一个insert操作（都在同一张表）。

默认隔离级别：Repeatable Read

只有hotel_id=2和hotel_id=的数据

逻辑删除原有数据

插入新的数据

根据现有数据情况，update的时候没有数据被更新

报了非常多一样的错

发现居然有死锁。 根据常识考虑，我每个线程（事务）更新的数据都不冲突，为什么会产生死锁？ 带着这个问题，打印mysql最近一次的死锁信息show engine innodb status 显示如下

发现事务1在等待一个锁

事务2也在等待一个锁

而且事物2持有了事物1需要的锁

关于锁的描述，出现了 lock_mode ， gap before rec ， insert intention 等字眼，看不懂说明了什么？说明我关于mysql的锁相关的知识储备还不够。那就开始调查mysql的锁相关知识。 通过搜索引擎，

锁的持有兼容程度如下表

那么再回到死锁日志，可以知道 :

事务1正在获取插入意向锁 事务2正在获取插入意向锁，持有排他gap锁

再看我们上面的锁兼容表格，可以知道， gap lock和insert intention lock是不兼容的 那么就可以推断出： 事务1持有gap lock，等待事务2的insert intention lock释放；事务2持有gap lock，等待事务1的insert intention lock释放，从而导致死锁。 那么新的问题就来了，事务1的intention lock 为什么会和事务2的gap lock 有交集，或者说，事务1要插入的数据的位置为什么会被事务2给锁住？ 让我回顾一下gap lock的定义： 间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况 那为什么是gap lock，gap lock到底是基于什么逻辑锁的记录？发现自己相关的知识储备还不够。那就开始调查。 调查后发现，当当前索引是一个 普通索引 的时候，会加一个gap lock来防止幻读， 此gap lock 会锁住一个左开右闭的区间。 假设索引为xx_idx（xx_id），数据分布为1,4,6,8,12，当更新xx_id=9的时候，这个时候gap lock的锁定记录区间就是(8，12]，也就是锁住了xxid in (9,10,11,12)的数据，当有其他事务要插入xxid in (9,10,11,12)的数据时，就会处于等待获取锁的状态。 ps：当前索引不是普通索引，而且是唯一索引等其他情况，请参考下面资料MySQL 加锁处理分析

回到我自己的案例中，重新屡一下事务1的执行过程：

因为普通索引KEY hotel_date_idx ( hotel_id , rate_date ) 的关系 这段sql会获取一个gap lock，范围(2,]

这段sql会获取一个insert intention lock （waiting）

再看事务2的执行过程

因为普通索引KEY hotel_date_idx ( hotel_id , rate_date ) 的关系 这段sql也会获取一个gap lock，范围也是(2,]（根据前面的知识，gap lock之间会互相兼容，可以一起持有锁的）

这段sql也会获取一个insert intention lock （waiting）

看到这里，基本也就破案了。因为普通索引的关系，事务1和事务2的gap lock的覆盖范围太广，导致其他事务无法插入数据。 重新梳理一下：

所以从结果来看，一堆事务被回滚，只有数据被更新成功

gap lock 导致了并发处理的死锁

在mysql默认的事务隔离级别（repeatable read）下，无法避免这种情况。只能把并发处理改成同步处理。或者从业务层面做处理。

共享锁、排他锁、意向共享、意向排他 record lock、gap lock、next key lock、insert intention lock show engine innodb status

用sql语句，怎么解决mysql数据库死锁

MySQL死锁问题的相关知识是本文我们主要要介绍的内容，接下来我们就来一一介绍这部分内容，希望能够对您有所帮助。 1、MySQL常用存储引擎的锁机制MyISAM和MEMORY采用表级锁(table-level locking)BDB采用页面锁(page-level locking)或表级锁，默认为页面锁InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁2、各种锁特点表级锁：开销小，加锁快;不会出现死锁;锁定粒度大，发生锁冲突的概率最高，并发度最低行级锁：开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低，并发度也最高页面锁：开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般3、各种锁的适用场景表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用行级锁则更适合于有大量按索引条件并发更新数据，同时又有并发查询的应用，如一些在线事务处理系统4、死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。 表级锁不会产生死锁。 所以解决死锁主要还是针对于最常用的InnoDB。 5、死锁举例分析在MySQL中，行级锁并不是直接锁记录，而是锁索引。 索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。 在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。 例如，一个表db。 tab_test，结构如下：id：主键;state：状态;time：时间;索引：idx_1(state，time)出现死锁日志如下：?***(1) TRANSACTION:?TRANSACTION 0 , ACTIVE 0 sec, process no , OSthread id starting index read?mysql tables in use 1, locked 1?LOCK WAIT 3 lock struct(s), heap size 320?MySQL thread id 83, query id dcnet03 dcnet Searching rows for update?update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute) (任务1的sql语句)?***(1) WAITING FOR THIS LOCK TO BE GRANTED: (任务1等待的索引记录)?RECORD LOCKS space id 0 page no n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 _mode X locks rec but not gap waiting?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0?0: len 8; hex 7629c; asc b ;; 1: len 6; hex eaee; asc (f ;; 2: len 7; hex d; asc @ ;; 3: len 8; hex 050b2; asc P ;; 4: len 8; hex 0502a; asc P*;; 5: len 8; hex ; asc T&;; 6: len 8; hex c66d29c; asc A,f ;; 7: len 23; hex c6f52e636f6d2f6 8616e642e; asc /;; 8: len 8; hex 0042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 04e24; asc N$;;?*** (2) TRANSACTION:?TRANSACTION 0 , ACTIVE 0 sec, process no , OS thread id updating or deleting, thread declared inside InnoDB 499?mysql tables in use 1, locked 1?3 lock struct(s), heap size 320, undo log entries 1?MySQL thread id 84, query id dcnet03 dcnet Updating update tab_test set state=1067,time=now () where id in () (任务2的sql语句)?*** (2) HOLDS THE LOCK(S): (任务2已获得的锁)?RECORD LOCKS space id 0 page no n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 lock_mode X locks rec but not gap?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0?0: len 8; hex 7629c; asc b ;; 1: len 6; hex eaee; asc (f ;; 2: len 7; hex d; asc @ ;; 3: len 8; hex 050b2; asc P ;; 4: len 8; hex 0502a; asc P*;; 5: len 8; hex ; asc T&;; 6: len 8; hex c66d29c; asc A,f ;; 7: len 23; hex c6f52e636f6d2f6 8616e642e; asc /;; 8: len 8; hex 0042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 04e24; asc N$;;?*** (2) WAITING FOR THIS LOCK TO BE GRANTED: (任务2等待的锁)?RECORD LOCKS space id 0 page no n bits 600 index `idx_1` of table `db/tab_test` trx id 0 lock_mode X locks rec but not gap waiting?Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0?0: len 8; hex ; asc %;; 1: len 8; hex c66d29c; asc A,f ;; 2: len 8; hex 7629c; asc b ;;?*** WE ROLL BACK TRANSACTION (1)?(回滚了任务1，以解除死锁)原因分析：当“update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)”执行时，MySQL会使用idx_1索引，因此首先锁定相关的索引记录，因为idx_1是非主键索引，为执行该语句，MySQL还会锁定主键索引。 假设“update tab_test set state=1067,time=now () where id in ()”几乎同时执行时，本语句首先锁定主键索引，由于需要更新state的值，所以还需要锁定idx_1的某些索引记录。 这样第一条语句锁定了idx_1的记录，等待主键索引，而第二条语句则锁定了主键索引记录，而等待idx_1的记录，这样死锁就产生了。 6、解决办法拆分第一条sql，先查出符合条件的主键值，再按照主键更新记录：?select id from tab_test where state=1061 and time < date_sub(now(), INTERVAL 30 minute);?update tab_test state=1064,time=now() where id in(......);