Bug #3565 HANDLER and FLUSH TABLE/TABLES deadlock
Submitted: 26 Apr 2004 11:46 Modified: 25 Jun 2004 14:46
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0 OS:
Assigned to: Ingo Strüwing CPU Architecture:Any

[26 Apr 2004 11:46] Dean Ellis
Description:
Tables opened by HANDLER cannot be FLUSHed, whether by FLUSH TABLE t1 or FLUSH TABLES, whether by the same thread or another thread.

The thread performing the FLUSH must be killed to continue before the thread using HANDLER closes the table.

HANDLER CLOSE t1; results in "ERROR 1109 (42S02): Unknown table 't1' in HANDLER" and the FLUSH completes.

How to repeat:
CREATE TABLE t1 ( a INT PRIMARY KEY );
HANDLER t1 OPEN;
FLUSH TABLES;

Suggested fix:
Alter behavior for four cases:

1) HANDLER and FLUSH TABLES in same thread
2) HANDLER and FLUSH TABLE in same thread
3) HANDLER and FLUSH TABLES in different threads
4) HANDLER and FLUSH TABLE in different threads
[3 Jun 2004 13:01] Ingo Strüwing
Most of the effects you described are as designed. Though the mentioned error message is somewhat confusing, it is correct. I'll try to describe what's going on: HANDLER t1 OPEN promises that the table will not be closed until HANDLER t1 CLOSE. But FLUSH TABLE[S] [t1] is more powerful and overrides this promise. However, if it is called by another thread, it cannot close that HANDLER table. These are thread-local. No thread can do anything with another threads HANDLER tables. But FLUSH TABLE[S] can mark all tables for close, even for other threads HANDLER tables. The marked table will be closed whenever the owning thread tries to do anything with it. For example a HANDLER t1 READ would close the table and then complain, that it doesn't exist. So does HANDLER CLOSE, in which case that message is especially confusing. The FLUSH completes when all tables have been closed, which is done in your example implicitly by HANDLER CLOSE despite of the confusing error message.

This all works perfectly as far as I tested it. I could not reproduce your scenario "The thread performing the FLUSH must be killed to continue before the thread using HANDLER closes the table." The thread performing the FLUSH just blocks until the other thread tries to do anything with its HANDLER table. Your next sentence describes this behaviour exactly: "HANDLER CLOSE t1; results in "ERROR 1109 (42S02): Unknown table 't1' in HANDLER" and the FLUSH completes." So this sentence conflicts with the first sentence. Either I misinterpreted the first sentence, or you accidentially mistyped something.

The only real problem I found was what your test case shows and what the bug title says: A deadlock happened when a thread with open HANDLER tables called FLUSH TABLE[S] [for one or more of these]. This is fixed by "bk commit - 5.0 tree (ingo:1.1690) BUG#3565" which has been sent to dev-bugs@ and which I ask you to review.
[21 Jun 2004 15:14] Ingo Strüwing
The after review changeset is in
bk commit - 4.0 tree (ingo:1.1866) BUG#3565
of Mon, 21 Jun 2004 15:04:55 +0200
[22 Jun 2004 9:42] Michael Widenius
Still some more work to do on patch
[24 Jun 2004 16:35] Ingo Strüwing
For the time being, only the deadlock problem is solved in:
bk commit - 4.0 tree (ingo:1.1866) BUG#3565
The general HANDLER/FLUSH behaviour will be fixd later.
[25 Jun 2004 14:46] Ingo Strüwing
The deadlock is solved with the push.
The HANDLER/FLUSH misbehavior is now reported in bug#4286.