科普文：软件架构数据库系列之【MySQL死锁bug梳理ERROR 1213 (40001): Deadlock】

本文链接：https://blog.csdn.net/Rookie_CEO/article/details/143087921

概叙

关于死锁，确切的说是innodb引擎表的死锁，在我们梳理的场景下，基本都是“行锁”导致的，而且隔离级别大多都是RR。

今天我们去MySQL官网上搜一下关于死锁的bug。有兴趣的可以去看看这些bug是不是在我们日常操作中常见的问题，也可以看看是否被修复。

其实我们前面整理的死锁案例，都可以在mysql 的bug中找到对应的bug场景。

MySQL Bugs: Search

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

死锁bug

‌Bug状态的含义如下‌：

‌Open（创建）‌：当发现bug并在缺陷管理工具上提交后，bug的状态为open。这是bug的最初状态，表示bug已被发现并记录下来‌。
‌Patch（打补丁）‌：在开发人员修复bug后，可能会将修复的代码作为一个补丁提交。此时，bug的状态可能会标记为patch，表示已经有一个修复方案，但还需要经过测试验证‌。
‌Analyzing（分析中）‌：开发人员接收到bug报告后，会开始分析问题的原因和解决方案。此时，bug的状态标记为analyzing，表示正在进行分析‌。
‌Verified（验证）‌：在开发人员修复bug并提交后，测试人员会对修复进行验证。如果验证通过，bug的状态会变为verified，表示bug已经被成功修复‌

‌详细解释每个状态的含义和流程‌：

‌Open‌：这是bug的最初状态，表示测试人员发现了问题并提交给缺陷管理工具。开发人员看到这个状态时，知道需要处理一个新的bug‌。
‌Patch‌：当开发人员找到修复方案后，可能会将代码作为一个补丁提交。这个状态表示有一个解决方案，但还需要经过测试验证‌。
‌Analyzing‌：开发人员开始分析bug的原因和可能的解决方案。这个状态表示问题正在被调查和处理中‌。
‌Verified‌：测试人员对开发人员提交的修复进行验证。如果验证通过，表示bug已经被成功修复‌。

通过这些状态，开发团队可以跟踪bug的处理进度，确保每个bug都能得到妥善处理和验证。

Bug状态Active

可以看到总共有65个bug，其中超过10年的老bug不少，而且mysql8的bug也不少；其中open的2个、analyzing的2个、open的1个，总计还有5个bug带修复确认。

open的2个

Analyzing的 2个

Patch的1个

Verified的60个

总共65个bug，去除上面的5个，有60个Verified的。

open的2个：

MySQL Bugs: #84607: Print more information about deadlocks

[23 Jan 2017 11:41] Sveta Smirnova
Description:
With option innodb_print_all_deadlocks we can have all deadlocks logged. But only last statements which participate in deadlock are logged while they mostly happen in multiple statement transactions

How to repeat:
Try to uncover what exactly caused this deadlock:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-01-23 14:40:21 0x7f7924f99700
*** (1) TRANSACTION:
TRANSACTION 2828, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1160, 1 row lock(s)
MySQL thread id 5, OS thread handle 140158287841024, query id 14 localhost 127.0.0.1 root updating
DELETE FROM t WHERE i = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 326 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 2828 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000000b0b; asc       ;;
 2: len 7; hex aa0000014a0110; asc     J  ;;
 3: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2829, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1160, 3 row lock(s)
MySQL thread id 4, OS thread handle 140158288107264, query id 15 localhost 127.0.0.1 root updating
DELETE FROM t WHERE i = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 326 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 2829 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000000b0b; asc       ;;
 2: len 7; hex aa0000014a0110; asc     J  ;;
 3: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 326 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 2829 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000000b0b; asc       ;;
 2: len 7; hex aa0000014a0110; asc     J  ;;
 3: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

(For answer see https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlock-example.html)

Suggested fix:
Print previous statements. At least, when P_S statements instrumentation is enabled.

MySQL :: MySQL 8.4 Reference Manual :: MySQL Glossary

‌使用innodb_print_all_deadlocks选项，我们可以记录所有死锁。‌ 这个选项默认是关闭的，开启后可以将死锁信息自动记录到MySQL的错误日志中‌。

要开启innodb_print_all_deadlocks选项，可以通过以下两种方法：

通过SQL命令设置：执行命令SET GLOBAL innodb_print_all_deadlocks=ON;，但这种方法在服务重启后会失效‌。
通过修改配置文件：在/etc/my.cnf文件中添加或修改配置innodb_print_all_deadlocks=ON，这样设置在MySQL服务重启后依然有效‌4。

开启innodb_print_all_deadlocks选项后，死锁信息会被记录在MySQL的错误日志中，而不是通过SHOW ENGINE INNODB STATUS命令只能获取到最近一次的死锁信息‌。

MySQL Bugs: #72756: Downgrade AUTO-INC lock from simple INSERT when possible

Description:
With the new auto increment handling, simple INSERT statements do not require table level auto-inc lock unless there is another transaction holding the auto-inc lock. The issue is that a long running bulk or mix-mode INSERT would make subsequent simple INSERTs to upgrade to auto-inc lock and when the bulk or mix-mode INSERT finishes, the awaiting simple INSERTs continue to acquire auto-inc lock in order, which reduced concurrency and increased chances of deadlock. 

How to repeat:
This example produces a deadlock: 

Prepare the data:
-------------------------
use test;
create table parent (id int, name varchar(20), primary key (id)) engine = innodb;
create table child (id int auto_increment, p_id int, primary key (id), key idx_p_id (p_id), constraint fk_p_id foreign key (p_id) references parent (id) ) engine = innodb;
insert into parent values (1, "foo"), (2,"blah");
insert into child (p_id) values (1);

for i in {1..20}; do echo $i; echo "insert into child (p_id) select t.p_id from child t" | mysql test; done

Run the test:
-------------------
session1: 
echo “insert into child (p_id) select t.p_id from child t” | mysql test ;

session2: while session1 running
mysql> insert into child (p_id) value (2);

session3, while session1 and session2 running
mysql> begin;
mysql> update parent set name = “aha” where id = 2;
mysql> insert into child (p_id) value (1);

Session2 and session3 would wait for auto-inc lock while session1 is running. When session1 finishes, session2 and 3 would result in deadlock. 

Without session1, the concurrent execution of session2 and session3 would not result in deadlock, which is a desired behavior. 

Suggested fix:
This feature request is about the feasibility of downgrade the auto-inc lock for the awaiting simple INSERT after the bulk or mixed-mode INSERT finishes.

大意就是自增锁和外键约束导致的死锁。整体看下来和gap间隙锁导致的死锁差不多。

问题不大，在MySQL中我们一般都是禁用外键约束。看来不仅仅是阻塞同步、导致主从数据不一致；还能导致死锁。

Analyzing的 2个

MySQL Bugs: #95934: innodb_flush_log_at_trx_commit = 0 get performance regression on 8 core machine

[23 Jun 2019 17:13] Zongzhi Chen
Description:
In MySQL8.0.16, innodb_flush_log_at_trx_commit = 1 has a better performance than innodb_flush_log_at_trx_commit = 0 on 8 core environment.  This is really strange.

This is the log from the sysbench oltp_write_only case

[ 1350s ] thds: 512 tps: 3097.59 qps: 18586.74 (r/w/o: 0.00/12391.56/6195.18) lat (ms,95%): 530.08 err/s: 0.00 reconn/s: 0.00
[ 1360s ] thds: 512 tps: 2692.71 qps: 16162.08 (r/w/o: 0.00/10776.75/5385.33) lat (ms,95%): 549.52 err/s: 0.00 reconn/s: 0.00
[ 1370s ] thds: 512 tps: 3038.29 qps: 18223.25 (r/w/o: 0.00/12146.57/6076.68) lat (ms,95%): 458.96 err/s: 0.00 reconn/s: 0.00
[ 1380s ] thds: 512 tps: 3254.50 qps: 19521.99 (r/w/o: 0.00/13012.99/6509.00) lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00
[ 1390s ] thds: 512 tps: 3054.39 qps: 18325.65 (r/w/o: 0.00/12216.87/6108.78) lat (ms,95%): 539.71 err/s: 0.00 reconn/s: 0.00
[ 1400s ] thds: 512 tps: 3115.11 qps: 18689.79 (r/w/o: 0.00/12459.56/6230.23) lat (ms,95%): 484.44 err/s: 0.00 reconn/s: 0.00
[ 1410s ] thds: 512 tps: 2930.10 qps: 17586.11 (r/w/o: 0.00/11725.91/5860.20) lat (ms,95%): 520.62 err/s: 0.10 reconn/s: 0.00
[ 1420s ] thds: 512 tps: 2869.80 qps: 17217.22 (r/w/o: 0.00/11477.51/5739.71) lat (ms,95%): 549.52 err/s: 0.00 reconn/s: 0.00

This is the time we set the innodb_flush_log_at_trx_commit from 0 to 1.

[ 1430s ] thds: 512 tps: 3991.46 qps: 24055.68 (r/w/o: 0.00/16073.35/7982.33) lat (ms,95%): 484.44 err/s: 0.00 reconn/s: 0.00
[ 1440s ] thds: 512 tps: 11285.96 qps: 67733.48 (r/w/o: 0.00/45161.05/22572.43) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00
[ 1450s ] thds: 512 tps: 11734.96 qps: 70404.68 (r/w/o: 0.00/46935.15/23469.53) lat (ms,95%): 77.19 err/s: 0.00 reconn/s: 0.00
[ 1460s ] thds: 512 tps: 11563.32 qps: 69378.01 (r/w/o: 0.00/46251.17/23126.84) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00

We find that when the system is busy, I find that the recent_write buffer, however the log_writer thread have no chance to get the cpu time. It can't consume the data in recent_write buffer, and even the user thread get the cpu time, it can't write the data to recent_writter buffer since it is full. It seem the system get into a "deadlock"...

How to repeat:
Running the oltp_write_only sysbench 

Suggested fix:
I would suggest to give the log_writer thread a high priority..
[24 Jun 2019 13:02] MySQL Verification Team
Hello Mr. Zongzhi,

Thank you for your bug report, which is actually pointing to the performance regression.

Can you let us know how exactly did you run sysbench .... meaning all the arguments that you have used. Separate for the `prepare` and `run`.  Also, I do hope that I would manage to observe the same phenomena on my 6-core i7.

Last, but not least, can you provide us with a configuration for your server ???
[24 Jun 2019 15:54] Zongzhi Chen
Yes, below is the sysbench command I used, and I always use the configure the same is dimitric in cpu-bound test case

build/bin/sysbench oltp_write_only --mysql-host=xxxxxxxxxx --mysql-port=2250 --mysql-password=xxxxxxxx --mysql-user=replicator --tables=48 --table_size=500000 --threads=512 --report-interval=10 --rand-type=uniform prepare
build/bin/sysbench oltp_write_only --mysql-host=xxxxxxxxxx --mysql-port=2250 --mysql-password=xxxxxxxx --mysql-user=replicator --tables=48 --table_size=500000 --threads=512  --max-time=1800 --report-interval=10 --rand-type=uniform run
[25 Jun 2019 16:11] MySQL Verification Team
Hi,

I have tested your sysbench performance measurement with flush at trx commit both set to 0 and then to 1.

I have gathered all statistics and the average number of transactions per second and queries per second is practically the same. Difference is less than 1 %. Hence, it is inconclusive .....

I used my machine with 6 core i9, with SSD, and as I wrote, I have got exactly the same performance with both values for that system variable.

Hence, seems that your machine is even more powerful than mine.

Therefore, it seems to me that your recommendation of increasing thread priority can not be recommended for all setups. Hence, this looks to me just as a tuning tip for the high end machine under very large load.

Would you agree with me or would you like to expand on your recommendation on the manner in which our server would self-tune itself ......
[2 Jul 2019 20:45] Zongzhi Chen
Actually, I am simulate 8 core machine by using taskset the mysql pid to 8 cpus, and running the sysbench. This is very general way to sell 8 core machine in cloud environment.
Could you try to running the sysbench with taskset or using cgroup to limit the cpu.
[3 Jul 2019 13:01] MySQL Verification Team
Hi,

I truly do not understand what is it that you want me to do ...

You want me to test MySQL in cloud .... I am not connected to any cloud .....

Also, as I wrote, I tested MySQL on my iMac with i9 with 6 cores with SSD ....... 

I consider this good enough.
[3 Jul 2019 13:45] XIAOFEI WU
I got the similar result with @chen zongzhi. @Sinisa Milivojevic, can you show me the config files and sysbench comands you used? Thank you!
[3 Jul 2019 13:48] Zongzhi Chen
you mean you running sysbench in your iMac in MacOS?  not in the linux?
can you running the sysbench in linux, I think most of the MySQL user running in linux..
[4 Jul 2019 12:31] MySQL Verification Team
Hi,

I have forwarded your request to the group that is dealing with this matter. They will schedule the testing and when done, they will report back.
[5 Jul 2019 8:17] Zongzhi Chen
Thank you.
Waiting for your reply

Zongzhi Chen (OCA) 应该是个中国人（杭州、阿里的大佬），这个大佬总共提交了37个bug。

https://github.com/baotiao

MySQL Bugs: Search

用sysbench在8核机器下压测，系统很忙时，发现recent_write缓冲区上 log_writer线程没有机会获得cpu时间。它无法使用recent_write缓冲区中的数据，即使用户线程获得了cpu时间，它也无法将数据写入recent_witter缓冲区，因为它已满。看起来好像进入了死锁状态。（大佬就是大佬，不仅仅发现bug，还能给出修复建议，强悍）

后面也有大佬复现了这个场景。官方问了大佬的sysbench版本，估计要自测。

MySQL Bugs: #49456: replication out of sync

[4 Dec 2009 12:04] Matthias Leich
Description:
When running a RQG test I get a difference between
master and slave.

My command line
---------------
runall.pl \
--basedir=/work2/5.1/mysql-5.1-rep+3 \
--vardir=/dev/shm/var_290 \
--debug \
--duration=600 \
--rpl_mode=default \
--mysqld=--table-lock-wait-timeout=1 \
--mysqld=--loose-innodb-lock-wait-timeout=5 \
--mysqld=--innodb_log_file_size=16M \
--mysqld=--log-output=table \
--mysqld=--skip-safemalloc \
--mysqld=--plugin-dir=/work2/5.1/mysql-5.1-rep+3/plugin/semisync/.libs \
--mysqld=--plugin-load=rpl_semi_sync_master=libsemisync_master.so:rpl_semi_sync_slave=libsemisync_slave.so \
--mysqld=--rpl_semi_sync_master_enabled=1 \
--mysqld=--rpl_semi_sync_slave_enabled=1 \
--gendata=conf/replication-dml_data.zz \
--grammar=conf/replication-dml_sql.yy \
--reporter=Deadlock,Backtrace,ErrorLog \
--validator= \
--threads=1 \
--queries=100000 \
--seed=1

The result (heavy edited) on
mysql-5.1-rep+3 revno: 3124 2009-12-02
--------------------------------------
...
# 12:41:54 Test completed successfully.
# 12:41:54 gentest.pl exited with exit status 0
# 12:41:54 Waiting for slave to catch up..., file master-bin.005506, pos 107 .
# 12:41:54 Dumping server on port 12900...
# 12:42:03 Dumping server on port 12902...
# 12:42:12 Comparing SQL dumps...
--- /tmp//server_25834_0.dump ...
+++ /tmp//server_25834_1.dump ...
@@ -593,8 +593,8 @@
    The column is defined as col_mediumtext_latin1 mediumtext
-INSERT INTO `table1_innodb_int_autoinc` VALUES (....,'2009-12-04 14:41:51',....
+INSERT INTO `table1_innodb_int_autoinc` VALUES (....,'2009-12-04 14:41:52',....
-INSERT INTO `table1_innodb_int_autoinc` VALUES (....,'2009-12-04 14:41:51',....
+INSERT INTO `table1_innodb_int_autoinc` VALUES (....,'2009-12-04 14:41:52',....
...
... runall.pl will exit with exit status 1

I will try to generate a testcase for mysqltest.

How to repeat:
See above
[4 Dec 2009 12:05] Matthias Leich
Grammar for creation of objects

Attachment: replication-dml_data.zz (application/octet-stream, text), 2.27 KiB.
[4 Dec 2009 12:06] Matthias Leich
Grammar for testing

Attachment: replication-dml_sql.yy (application/octet-stream, text), 29.49 KiB.
[21 Dec 2009 18:35] Matthias Leich
It is rather likely that this bug is a duplicate of
some bugs I reported later.

RQG（Random Query Generator）是一个强大的工具，用于弥补MySQL Test在重现某些bug时的不足。它基于用户定义的词法生成SQL语句执行，适用于并发测试场景，帮助MySQL内核开发者更好地进行测试。

RQG测试工具bug，不是MySQL本身的bug。

Patch的1个

MySQL Bugs: #50846: BACKPORT: Locking tests failing in pushbuild tree

[2 Feb 2010 18:44] Chuck Bell
Description:
There are two tests failing in the pushbuild tree. These tests did not fail before we merged in the new MDL locking code. Both are timeouts on the debug sync points. 

The tests are backup_myisam_sync and backup_restore_locking. Fails consistently on Ubuntu, Mac, Windows local builds but not in all PB machines.

Sample failure results:

backup.backup_myisam_sync                [ fail ]
        Test ended at 2010-02-02 02:37:54

CURRENT_TEST: backup.backup_myisam_sync
'diff' is not recognized as an internal or external command,
operable program or batch file.
--- H:/pb2/test/sb_2-1278083-1265068782.34/mysql-5.6.0-beta-win-x86-test/mysql-test/suite/backup/r/backup_myisam_sync.result	2010-02-02 02:15:45.000000000 +0300
+++ H:\pb2\test\sb_2-1278083-1265068782.34\mysql-5.6.0-beta-win-x86-test\mysql-test\suite\backup\r\backup_myisam_sync.reject	2010-02-02 04:37:54.431206600 +0300
@@ -120,6 +120,7 @@
 backup_id
 ###
 Warnings:
+###	1639	debug sync point wait timed out
 ###	1036	Table 't1' is read only
 ###	1213	Deadlock found when trying to get lock; try restarting transaction
 #

mysqltest: Result length mismatch

backup.backup_restore_locking            [ fail ]  timeout after 900 seconds
        Test ended at 2010-02-02 01:58:51

Test case timeout after 900 seconds

== H:/pb2/test/sb_2-1278083-1265068782.34/mysql-5.6.0-beta-win-x86-test/mysql-test/var-n_mix/log/backup_restore_locking.log == 
                 SIGNAL waiting WAIT_FOR continue';
RESTORE FROM 'bup_reslock_db1.bak';
#
# connection con1
SET DEBUG_SYNC= 'now WAIT_FOR waiting';
SET DEBUG_SYNC= 'wait_for_lock SIGNAL lock_wait';
INSERT INTO bup_reslock_db1.t1 VALUES(1,"after_lock_tables");
#
# connection con2
SET DEBUG_SYNC= 'now WAIT_FOR lock_wait';
Warnings:
Warning	1639	debug sync point wait timed out
SET DEBUG_SYNC= 'now SIGNAL continue WAIT_FOR waiting';
SET DEBUG_SYNC= 'wait_for_lock SIGNAL lock_wait';
INSERT INTO bup_reslock_db1.t1 VALUES(2,"after_truncate_tables");
#
# connection con3
SET DEBUG_SYNC= 'now WAIT_FOR lock_wait';
Warnings:
Warning	1639	debug sync point wait timed out

 == H:/pb2/test/sb_2-1278083-1265068782.34/mysql-5.6.0-beta-win-x86-test/mysql-test/var-n_mix/tmp/analyze-timeout-mysqld.1.err ==
SHOW PROCESSLIST;
Id	User	Host	db	Command	Time	State	Info
66	root	localhost:55159	test	Query	299	debug sync point: now	SET DEBUG_SYNC= 'now SIGNAL continue WAIT_FOR waiting'
67	root	localhost:55234	NULL	Query	0	NULL	SHOW PROCESSLIST

 == H:/pb2/test/sb_2-1278083-1265068782.34/mysql-5.6.0-beta-win-x86-test/mysql-test/var-n_mix/tmp/analyze-timeout-mysqld.2.err ==
SHOW PROCESSLIST;
Id	User	Host	db	Command	Time	State	Info
24	root	localhost:55235	NULL	Query	0	NULL	SHOW PROCESSLIST

How to repeat:
./mysql-test-run.pl backup_myisam_sync backup_restore_locking

Suggested fix:
Unknown.
[5 Feb 2010 14:54] Chuck Bell
Chuck assumes the bug since Rafal is sick -- get well soon, Rafal!
[5 Feb 2010 15:31] Chuck Bell
These tests share a common sync point which is not being reached: wait_for_lock. I will ask the runtime team to confirm that this sequence is no longer reached and ask for a equivalent wait point.
[5 Feb 2010 15:47] Chuck Bell
It has been confirmed the wait_for_lock() code is no longer called when locking tables for write. Thus, we must remove this sync point from the tests.
[5 Feb 2010 16:36] Chuck Bell
Removing the wait_for_lock sync point works, but results in non-deterministic values for the IS.PROCESSLIST since the lock is not in a separate thread.
[9 Feb 2010 16:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/99751

3086 Chuck Bell	2010-02-09
      BUG#50846 : BACKPORT: Locking tests failing in pushbuild tree
      
      The locking tests that use the wait_for_lock sync point in the old
      server locking code fail because the method wait_for_lock() has been
      replaced with the new MDL code.
      
      This patch introduces a new sync point in the new MDL code for
      synchronizing the locking tests. 
     @ mysql-test/suite/backup/r/backup_myisam_sync.result
        Corrected result file.
     @ mysql-test/suite/backup/r/backup_restore_locking.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_myisam_sync.test
        Changed test to use new sync point in mdl code.
     @ mysql-test/suite/backup/t/backup_restore_locking.test
        Changed test to use new sync point in mdl code.
        Commented out non-deterministic show processlist call -- this
        was due to change in how locking is done in the server with the
        new MDL locking code.
     @ sql/mdl.cc
        Added sync point for restore locking tests.
[10 Feb 2010 13:22] Thava Alagu
Good to push.
[10 Feb 2010 17:09] Ritheesh Vedire
Patch Approved
[10 Feb 2010 20:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/99874

3087 Chuck Bell	2010-02-10
      BUG#50846 : BACKPORT: Locking tests failing in pushbuild tree
        
      The locking tests that use the wait_for_lock sync point in the old
      server locking code fail because the method wait_for_lock() has been
      replaced with the new MDL code.
        
      This patch introduces a new sync point in the new MDL code for
      synchronizing the locking tests. 
     @ mysql-test/suite/backup/r/backup_myisam_sync.result
        Corrected result file.
     @ mysql-test/suite/backup/r/backup_restore_locking.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_myisam_sync.test
        Changed test to use new sync point in mdl code.
     @ mysql-test/suite/backup/t/backup_restore_locking.test
        Changed test to use new sync point in mdl code.
        Commented out non-deterministic show processlist call -- this
        was due to change in how locking is done in the server with the
        new MDL locking code.
     @ sql/mdl.cc
        Added sync point for restore locking tests.
[10 Feb 2010 20:23] Chuck Bell
Patch queued to mysql-next-mr-backup and mysql-backup-backport.

backup.backup_myisam_sync   myisam表的备份同步工具导致的死锁，暂时可以不用关心。

MySQL Bugs: #42895: Implement a locking scheme for RESTORE

科普文：软件架构数据库系列之【MySQL死锁bug梳理ERROR 1213 (40001): Deadlock】

概叙

index merge导致的死锁

Record Lock行锁的循环等待导致的死锁

Gap Lock间隙锁的“delete/update空行”导致的死锁

死锁bug

Bug状态Active

open的2个：

Analyzing的 2个

Patch的1个