MySQL死锁

欢迎订阅关注公众号：赵KK日常技术记录

MySQL死锁
MySQL死锁场景
查看最近一次死锁的日志
MySQL八种锁
1，行锁(Record Locks)
2，间隙锁(Gap Locks)
3，临键锁(Next-key Locks)
4，共享锁/排他锁(Shared and Exclusive Locks)
5，意向共享锁/意向排他锁(Intention Shared and Exclusive Locks)
6，插入意向锁(Insert Intention Locks)
7，自增锁(Auto-inc Locks)
死锁检测和回滚
如何处理死锁

官网：https://www.mysqlzh.com/doc/215/427.html#innodb-deadlock-detection

MySQL死锁

概念：mysql死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。
线程死锁：死锁是指两个或两个以上的进程（线程）在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程（线程）称为死锁进程（线程）。

MySQL死锁场景

1.在循环中删除
2.大事务等待
3.唯一索引冲突

[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

查看最近一次死锁的日志

SHOW ENGINE INNODB STATUS;

执行此命令，出现type、name、status三列信息
由于satus列信息较多，则至展示部分信息

=====================================
2023-01-11 14:37:11 0x7fda4238f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 19 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 18476361 srv_active, 0 srv_shutdown, 281390 srv_idle
srv_master_thread log flush and writes: 18757594
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 34266326
OS WAIT ARRAY INFO: signal count 1461380308
RW-shared spins 0, rounds 938476348, OS waits 24858303
RW-excl spins 0, rounds 1272221568, OS waits 2060918
RW-sx spins 25186792, rounds 204022245, OS waits 262983
Spin rounds per wait: 938476348.00 RW-shared, 1272221568.00 RW-excl, 8.10 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2022-12-24 11:44:18 0x7fda32f3d700  Cannot drop table `csx_b2b_scm`.`emp`
because it is referenced by `csx_b2b_scm`.`work`
------------------------
LATEST DETECTED DEADLOCK
------------------------

MySQL八种锁

行锁(Record Locks)

共享的(S)锁允许一个事务去读一行（tuple）。

独占的锁(X)允许一个事务更新或删除一行。

间隙锁(Gap Locks)

gap锁，又称为间隙锁。存在的主要目的就是为了防止在可重复读的事务级别下，出现幻读问题

临键锁(Next-key Locks)

innodb默认的锁就是Next-Key locks。
间隙锁在innoDB中的唯一作用就是在一定的“间隙”内防止其他事务的插入操作，以此防止幻读的发生：
防止间隙内有新数据被插入。
防止已存在的数据，更新成间隙内的数据。

共享锁/排他锁(Shared and Exclusive Locks)

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

意向共享锁/意向排他锁(Intention Shared and Exclusive Locks)

意图共享(IS)：事务T 意图给表T上单独的tuple设置S 锁定。

意图独占(IX)：事务T 意图给这些tuple设置X 锁定。

插入意向锁(Insert Intention Locks)

普通的Gap Lock 不允许 在 （上一条记录，本记录） 范围内插入数据
插入意向锁Gap Lock 允许 在 （上一条记录，本记录） 范围内插入数据
插入意向锁的作用是为了提高并发插入的性能， 多个事务 同时写入 不同数据 至同一索引范围（区间）内，并不需要等待其他事务完成，不会发生锁等待

自增锁(Auto-inc Locks)

在InnoDB中，每个含有自增列的表都有一个自增长计数器。当对含有自增长计数器的表进行插入时，首先会执行select max(auto_inc_col) from t for update来得到计数器的值，然后再将这个值加1赋予自增长列。我们将这种方式称之为AUTO_INC Lock。

Next-Key锁定：避免幽灵问题
在行级锁定中，InnoDB 使用一个名为next-key locking的算法。InnoDB以这样一种方式执行行级锁定：当它搜索或扫描表的索引之时，它对遇到的索引记录设置共享或独占锁定。因此，行级锁定事实上是索引记录锁定。

InnoDB对索引记录设置的锁定也映像索引记录之前的“间隙”。如果一个用户对一个索引上的记录R有共享或独占的锁定，另一个用户 不能紧接在R之前以索引的顺序插入一个新索引记录。这个间隙的锁定被执行来防止所谓的“幽灵问题”。假设你想要从有一个标识符值大于100的子表读并锁定所有子记录，并想着随后在选定行中更新一些 列：

SELECT * FROM child WHERE id > 100 FOR UPDATE;
假设在id列有一个索引。查询从id大于100的第一个记录开始扫描。如果设置在索引记录上的锁定不把在间隙生成的插入排除在外，一个新行可能与此同时被插进表中。如果你在同一事务内执行同样的SELECT，你可能会在该查询返回的结果包里看到一个新行。这与事务的隔离原则是相反的：一个事务应该能够运行，以便它已经读的数据在事务过程中不改变。如果我们把一套行视为数据项，新的“幽灵”子记录可能会违反这一隔离原则。

当InnoDB扫描一个索引之时，它也锁定所以记录中最后一个记录之后的间隙。刚在前一个例子中发生：InnoDB设置的锁定防止任何插入到id可能大过100的表。

你可以用next-key锁定在你的应用程序上实现一个唯一性检查：如果你以共享模式读数据， 并且没有看到你将要插入的行的重复，则你可以安全地插入你的行，并且知道在读过程中对你的行的继承者设置的next-key锁定与此同时阻止任何人对你的 行插入一个重复。因此，the next-key锁定允许你锁住在你的表中并不存在的一些东西。

死锁日志读取

异常标题：数据库出现死锁
异常归属：小分队
异常产生：sys-2023-01-07 16:04:29
异常来源：DBA
异常等级：通知
异常内容：2023-01-07T16:04:29.645882+08:00 5462835 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2023-01-07T16:04:29.645898+08:00 5462835 [Note] InnoDB: 
*** (1) TRANSACTION:

TRANSACTION 798960649, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5462836, OS thread handle 139666471368448, query id 2121361009 10.0.xx.xxx otmst_app updating
DELETE FROM xxxx
 WHERE  xxx
2023-01-07T16:04:29.645925+08:00 5462835 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1182 page no 862042 n bits 256 index uniq_order_interface of table xxx trx id 798960649 lock_mode X locks rec but not gap waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 14; hex 5a43323330313033303030353436; asc xxx;;
 1: len 30; hex 544d535f4d515f4c495354454e45525f434f4e5441494e45525f494e5445; asc xxx; (total 35 bytes);
 2: len 8; hex 8000000000517624; asc      Qv$;;

2023-01-07T16:04:29.646140+08:00 5462835 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 798960647, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5462835, OS thread handle 139666346735360, query id 2121360071 10.0.xx.xxx otmst_app updating
DELETE FROM xxxx
 WHERE  xxx
2023-01-07T16:04:29.646162+08:00 5462835 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 1182 page no 862042 n bits 256 index uniq_order_interface of table xxx trx id 798960647 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 14; hex 5a43323330313033303030353436; asc xxx;;
 1: len 30; hex 544d535f4d515f4c495354454e45525f434f4e5441494e45525f494e5445; asc xxx; (total 35 bytes);
 2: len 8; hex 8000000000517624; asc      Qv$;;

2023-01-07T16:04:29.646349+08:00 5462835 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1182 page no 862042 n bits 256 index uniq_order_interface of table xxx trx id 798960647 lock_mode X waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 14; hex 5a43323330313033303030353436; asc xxx;;
 1: len 30; hex 544d535f4d515f4c495354454e45525f434f4e5441494e45525f494e5445; asc xxx; (total 35 bytes);
 2: len 8; hex 8000000000517624; asc      Qv$;;

2023-01-07T16:04:29.646526+08:00 5462835 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)

事务一信息：
TRANSACTION 798960649:事务id
ACTIVE 0 sec: 活跃时间
starting index read：事务当前正在根据索引读取数据
mysql tables in use 1, locked 1:表示此事务修改了一个表，锁了一行数据。
MySQL thread id 5462836:线程id
query id 2121361009：查询id
10.0.xx.xxx otmst_app updating：数据库地址
RECORD LOCKS：行锁
index uniq_order_interface：使用唯一索引
lock_mode X locks，锁模式：排它锁。（X：排他锁，S：共享锁）
but not gap：非间隙锁
其中的0-2标识锁具体哪一行

事务二信息：
3 lock struct(s)：表示该事务的锁链表的长度为3，每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及 autoinc 锁等。heap size 1136 为事务分配的锁堆内存大小。
RECORD LOCKS：共享记录锁
WE ROLL BACK TRANSACTION (1)
事务1回滚

两个事务对同一条记录进行修改,两条事务均为X锁，故只允许一个事务更新，但此时事务1并未执行完成，X锁未释放，事务2进行更新需要等待事务1释放
死锁是事务型数据库典型的问题，但是除非它们频繁出现以至于你更本不能运行某个事务，它们一般是不危险的。正常地，你必须编写你的应用程序使得它们总是准备如果因为死锁而 回滚一个事务就重新发出一个事务。

InnoDB使用自动行级锁定。即使在只插入或删除单个行的事务的情况下，你可以遇到死锁。这是因为这些操作不是真正的“极小的”，它们自动对插入或删除的行的（可能是数个）索引记录设置锁定。

你可以用下列技术对付死锁减少它们发生的可能性：

· 用Use SHOW INNODB STATUS来确定最后一个死锁的原因。这样可以帮助你调节应用程序来避免死锁。

· 总是准备着重新发出事务，如果它因为死锁而失败了。死锁不危险，再试一次。

· 经常提交你的事务。小事务更少地倾向于冲突。

· 如果你正使用锁定读，（SELECT ... FOR UPDATE或 ... LOCK IN SHARE MODE），试着用更低的隔离级别，比如READ COMMITTED。

· 以固定的顺序访问你的表和行。则事务形成良好定义的查询并且没有死锁。

· 添加精心选定的索引到你的表。则你的查询需要扫描更少的索引记录并且因此设置更少的锁定。使用EXPLAIN SELECT来确定对于你的查询,MySQL认为哪个索引是最适当的。

· 使用更少的锁定。如果你可以接受允许一个SELECT从一个旧的快照返回数据，不要给它添加FOR UPDATE或LOCK IN SHARE MODE子句。这里使用READ COMMITTED隔离级别是比较好的，因为每个在同一事务里的持续读从它自己新鲜的快照里读取。

· 如果没有别的有帮助的了，用表级锁定系列化你的事务。用LOCK TABLES对事务型表（如InnoDB)的正确方法是设置AUTOCOMMIT = 0 并且不调用UNLOCK TABLES直到你明确地提交了事务。例如，如果你需要写表t1并从表t读，你可以按如下做：

· SET AUTOCOMMIT=0;
· LOCK TABLES t1 WRITE, t2 READ, ...;
· [do something with tables t1 and t2 here];
· COMMIT;
· UNLOCK TABLES;
表级锁定使得你的事务很好地排队，并且死锁被避免了。

· 领一个系列化事务的方法是创建一个辅助的“semaphore” 表，它只包含一个单行。让每个事务在访问其它表之前更新那个行。以这种方式，所有事务以序列的方式发生。注意，InnoDB即时死锁检测算法也能在这种情况下起租用，因为系列化锁定是行级锁定。超时方法，用MySQL表级锁定，必须被用来解决死锁。

· 在应用程序中使用LOCK TABLES命令，如果AUTOCOMMIT=1,MySQL不设定InnoDB表锁定。