MYSQL并发删除数据造成死锁.docx

《MYSQL并发删除数据造成死锁.docx》由会员分享，可在线阅读，更多相关《MYSQL并发删除数据造成死锁.docx（5页珍藏版）》请在第一文库网上搜索。

1、MYSQL并发删除数据造成死锁在第2()章中讲解了MySQL中加锁的分析，我们还需要将这些理论知识运用到实际中。从本章开始将介绍几个死锁案例，下面我们先介绍如何查看MySQL中的死锁日志。问题现象相信大家都遇到过发生死锁的情况，但是却无法说清楚为什么会发生死锁，有时给客户的答复也是模棱两可的，其中很大的原因是不去查看死锁日志，或者不会阅读死锁日志。很多人都说MySQL中的死锁日志的可读性不是很好，其实只要细心分析死锁日志并结合第20章中的知识，就会发现死锁日志也并不是很难阅读的。本章的案例是客户在夜间批量执行数据处理时发生了死锁，这是由不同的会话并发删除数据引起的。这个问题的原因比较简单，但是

2、想通过这个案例让大家熟悉如何排查死锁问题、如何阅读死锁日志才是目的。通过模拟死锁现象，得到死锁日志如下：(1) TRANSACTION:TRANSACTION 39474, ACTIVE 58 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1200, 4 row lock(s), undo log entries 3MySQL thread id 9, OS thread handle 12314 5525800960/ query id 77 localh

3、ost root updatingDELETE FROM tl WHERE id 4(1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 114 page no 4 n bits 80 index PRIMARY of tabletrx id39474 lock_mode X locks rec but not gap waitingRecord lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 320:len4；hex0000000

4、4; asc;1:len6；hex000000009a33; asc3;2:len7；hex02000001471399; ascG3:len2;hex6464; asc dd; *(2)TRANSACTION:TRANSACTION 39475, ACTIVE 46 sec starting index readmysql tables in use 1, locked 13 lock struct(s)r heap size 1200, 4 row lock(s)z undo log entries 3MySQL thread id 10, OS thread handle 1231455

5、26104064/ query id 78 localhost root updatingDELETE FROM tl WHERE id - 3 (2) HOLDS THE LOCK(S):RECORD LOCKS space id 114 page no 4 n bits 80 index PRIMARY of tabletrx id39475 lock_mode X locks rec but not gapRecord lock, heap no 5 PHYSICAL RECORD: n fields 4; compact format; info bits 320:len4; hex0

6、0000004; asc;1:len6; hex000000009a33; asc3;2:len7; hex02000001471399; ascG ;3:len2; hex6464; asc dd;Recordlock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 320:len4; hex00000005; asc;1:len6; hex000000009a33; asc3;2:len7； hex02000001471375; ascG u;3:len2; hex6565; asc ee;Record l

7、ock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 4;hex 00000006; asc;1: len 6;hex 000000009a33; asc3;2: len 7;hex 02000001471351; ascG Q;3: len 2;hex 6666; asc ff;(2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 114 page no 4 n bits 80 index PRIMARY of tabl

8、etrx id39475 lockmode X locks rec but not gap waitingRecord lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 4; hex 00000003; asc2;1: len 6; hex 000000009a32; asc2: len 7; hex 01000001462elf; asc3: len 2; hex 6363; asc cc;* WE ROLL BACK TRANSACTION (2)如何阅读死锁日志要排查死锁问题，首

9、先要学会如何阅读死锁日志。MySQL死锁日志看起来并不是很直观，需要我们一步一步地耐心分析。我们将上面的死锁日志拆分阅读，可以得出以下信息。(1)两个事务的事务IDTRANSACTION 39474TRANSACTION 39475(2)事务39474在执行DELETE语句时发生了锁等待mysql DELETE FROM tl WHERE id = 4 (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 114 page no 4 n bits 80 index PRIMARY of table dhy . tl trx

10、id39474 lock_mode X locks rec but not gap waitingRecord lock, heap no 5 PHYSICAL RECORD: n fields 4; compact format; info bits 320:len4;hex1:len6;hex2:len7;hex3:len2;hex00000004; asc ;000000009a33; asc 3;02000001471399; asc G6464; asc dd;/聚集索引的值事务ID/undo回浪段指针非主键字段的值通过以上信息可以看出，事务39474在执行DELETE语句时，在申请

11、id=4这条记录上的X锁时发生锁等待：lock_mode X locks rec but not g叩 waitingo(3)事务39475持有锁的信息* (2) HOLDS THE LOCK(S):RECORD LOCKS space id 114 page no 4 n bits 80 index PRIMARY of table dhy . * tl trx id39475 lock_mode X locks rec but not gapRecord lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bi

12、ts 320: len 4; hex 00000004; asc ;1: len 6;hex 000000009a33; asc3;2: len 7;hex 02000001471399; ascG ;3: len 2; hex 6464; asc dd;info bits 32Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format;0: len 4; hex 00000005; asc1: len 6; hex 000000009a33; asc3;2: len 7; hex 02000001471375; asc

13、G u;3: len 2; hex 6565; asc ee;Recordlock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len4; hex00000006; asc;1: len6; hex000000009a33; asc3;2: len7; hex02000001471351; ascG Q;3: len2; hex6666; asc ff;事务39475持有id=4,5,6记录上的X锁。(4)事务39475在执行DELETE语句时同样发生了锁等待 (2) TRANSACTION:TR

14、ANSACTION 39475, ACTIVE 46 sec starting index readmysql tables in use 1, locked 13 lock struct(s)r heap size 1200, 4 row lock(s)r undo log entries 3MySQL thread id 10, OS thread handle 1231455261040649 query id 78 localhost root updatingDELETE FROM tl WHERE id = 3* (2) WAITING FOR THIS LOCK TO BE GR

15、ANTED:RECORD LOCKS space id 114 page no 4 n bits 80 index PRIMARY of tabletrx id39475 lock_mode X locks rec but not gap waitingRecord lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 4; hex 00000003; asc ;1: len 6; hex 000000009a32; asc 2;2: len 7; hex 01000001462elf; as