MySQL Bugs: #35049: Logging to table escalates the impact of deadlocks

Bug #35049	Logging to table escalates the impact of deadlocks
Submitted:	4 Mar 2008 19:15	Modified:	4 Feb 2011 16:05
Reporter:	Philip Stoev	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S1 (Critical)
Version:	5.1.24, 5.1 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If a (Falcon) deadlock occurs with logging to TABLE, the entire server locks up and no new connections are accepted. If logging is set to FILE, then the root can log into the server and clean up the deadlock. It appears that this happens because the log table is locked and when a deadlock occurs, it is never unlocked. Thus, the new connection attempt can not be logged and the connection handshake does not complete.

How to repeat:
Please use the test case from bug #34567 with --mysqld=--log-output=file and --mysqld=--log-output=table to see the difference.

Suggested fix:
In my humble opinion, the log table should be logged with a relaxed locking metod and/or for shorter periods of time in order to enable operation even in the face of deadlocks. If this is not possible, at least new connection attempts and the KILL command should be exempt from logging if the table is not available.

Philip,

is this only happens with Falcon?
All works fine with InnoDB, Memory, MyISAM or whatelse storage engine?

Please, if possible test with newest bk tree.

I am setting this to verified because it also happens with Innodb and in a scenario which only involves DML statements.

A test case will hopefully follow shortly, however it appears that a big range of deadlocks are affected.

GDB output for bug 35049

Attachment: bug35049-gdb.txt (text/plain), 136.86 KiB.

I have uploaded a "thread apply all bt" output for a servder that exhibits that issue.

Another method to reproduce it would be to run the iuds6 test as described at:

https://inside.mysql.com/wiki/SystemQATestPlan

Please let me know if I can be of any other service, e.g. replicate the issue on a developer machine, or run gdb commands on a deadlocked server. Narrowing down the test case to a more manageable form would require many hours, so let me know if this is required.

5.1 is also affected, however there is no straightforward test case. The server does reproducibly lock up, however on a set of more complex queries, whereas Falcon provides some straightforward scenarios.

Both 6.0 and 5.1 show the same output from "thread apply all bt", that is, a locked log table.

Bug #37433 is an example of a deadlock that does not involve Falcon. If executed with --log-output=table, then the server will be completely locked out.

The replication slave is also unable to connect in case the master is deadlocked as described in this bug. This results in replication breakdown.

See related bug http://bugs.mysql.com/bug.php?id=41641

A fix for Bug#30414 will impact this problem. It is likely to go away or change its shape when Bug#30414 is in.

We're no more building Falcon.
It's unclear how this bug can be verified now.
Please provide a way to repeat the bug in 5.5.

Sveta,
the locking scheme has significantly changed in 5.5.
We introduced a graph-based deadlock detector.
Without being able to verify it, I have no clue whether the problem is solved or not, and if it is not solved, where to look for it.

I won't reject the bug just because it involves Falcon, but without a good test case I'd rather work on bugs that are well defined. 

My feeling is that the bug is no longer there, at least not in the original shape.

Bug still repeatable in 5.1 with test case from bug #37433, but log-output=table (all queries hung, no way to kill them), but not repeatable in 5.5 series.

Philip, if you still see bug in 5.5 please provide actual test case: I tried different ways of locks for InnoDB table, test case from bug #37433, but before patch without luck.

Bug is not reproducible in 5.5 and fix will not be back ported to 5.1