MySQL Bugs: #46602: FLUSH TABLES WITH READ LOCK deadlocks against concurrent CREATE VIEW

Bug #46602	FLUSH TABLES WITH READ LOCK deadlocks against concurrent CREATE VIEW
Submitted:	7 Aug 2009 14:08	Modified:	10 Nov 2009 13:20
Reporter:	Matthias Leich	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.4	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	deadlock, flush

Description:
Found when running RQG based tests with several threads.
The only SQL commands used are:
FLUSH TABLES WITH READ LOCK;
CREATE VIEW .....

I will add a grammar file and other information soon.

My environment:
- MySQL azalea (5.4) 2009-08-06
  ./BUILD/compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo

How to repeat:
I will add a grammar file and other information soon.

Non final grammars:

Test grammar:
-------------
database_name:
        ;

ddl:
        CREATE ALGORITHM = UNDEFINED VIEW  . { $view_table_name_n = "t1_" . $view_piece . $prng->int(0,$name_space_width) . $normal_piece ; $view_table_name = $view_table_name_n } { $view_table_item_n = $database_name . " . " . $view_table_name_n ; $view_table_item = $view_table_item_n ; return undef } AS SELECT _field_list FROM A;

query:
        CREATE ALGORITHM = UNDEFINED VIEW  . { $view_table_name_n = "t1_" . $view_piece . $prng->int(0,$name_space_width) . $normal_piece ; $view_table_name = $view_table_name_n } { $view_table_item_n = $database_name . " . " . $view_table_name_n ; $view_table_item = $view_table_item_n ; return undef } AS SELECT _field_list FROM A |
        FLUSH TABLES WITH READ LOCK;

Object creation grammar:
------------------------
$tables = {
   rows => [0, 1, 10 ]
};

$fields = {
   types => [ 'int', 'char', 'enum', 'set' ],
   indexes => [undef, 'key' ],
   null => [undef, 'not null'],
   default => [undef, 'default null'],
   sign => [undef, 'unsigned'],
   charsets => ['utf8', 'latin1']
};

$data = {
   numbers => [ 'digit', 'null', undef ],
   strings => [ 'letter', 'english' ],
   blobs => [ 'data' ],
   temporals => ['date', 'year', 'null', undef ]
}

The grammars above look very ugly and I am trying
to simplify them, but till now all attempts caused
that the deadlock disappeared.

The problem was:
1. Two or more sessions run a random mixup of
   - FLUSH TABLES WITH READ LOCK and
   - CREATE VIEW ...
     (high likelihood that this VIEW already exists)
2. They end up after some time with deadlock.
   Some sessions are waiting for
   - release of readlock        or
   - Flushing tables'
   All other sessions observe the PROCESSLIST or
   are inactive. The deadlock will not disappear if you
   remove these sessions.
   Such a deadlock is a bug.

I rechecked today on
mysql-6.0-codebase-bugfixing revno: 3654 2009-10-15
and was unable to reproduce the problem.
So I assume that some of the various fixes of other
locking related bugs removed the problem.