MySQL Bugs: #47249: assert in MDL_global_lock::is_lock_type

Bug #47249	assert in MDL_global_lock::is_lock_type_compatible
Submitted:	10 Sep 2009 13:15	Modified:	7 Mar 2010 1:39
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.4	OS:	Any
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any
Tags:	locking, mdl

Description:
My script:
----------
--disable_warnings
DROP TABLE IF EXISTS t1;
DROP VIEW  IF EXISTS v1;
--enable_warnings

CREATE TABLE t1 ( f1 integer );
CREATE VIEW v1 AS SELECT f1 FROM t1 ;
connect (con_1,localhost,root,,test);
LOCK TABLES v1 AS tab1 IN EXCLUSIVE MODE , t1  AS tab2 READ;
FLUSH TABLE t1;
disconnect con_1;
--source include/wait_until_disconnected.inc

connection default;
# The next statement seems to be essential for the problem.
LOCK TABLES t1 WRITE;
# Here I get the assertion
FLUSH TABLE t1;

# Cleanup
DROP TABLE t1;
DROP VIEW v1;

Backtrace from mysql-next-bugfixing:
------------------------------------
Thread 1 (process 27830):
#0  0x00007fccf7a65ce6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b58068 in my_write_core (sig=6) at stacktrace.c:309
#2  0x00000000006efb31 in handle_segfault (sig=6) at mysqld.cc:2738
#3  <signal handler called>
#4  0x00007fccf69615c5 in raise () from /lib64/libc.so.6
#5  0x00007fccf6962bb3 in abort () from /lib64/libc.so.6
#6  0x00007fccf695a1e9 in __assert_fail () from /lib64/libc.so.6
#7  0x0000000000920c32 in MDL_global_lock::is_lock_type_compatible (this=0x125e6e0, type=MDL_EXCLUSIVE, is_upgrade=true) at mdl.cc:519
#8  0x0000000000922479 in MDL_ticket::upgrade_shared_lock_to_exclusive (this=0x16b45d0) at mdl.cc:1016
#9  0x0000000000759cbd in wait_while_table_is_used (thd=0x166e808, table=0x16754d8, function=HA_EXTRA_FORCE_REOPEN) at sql_base.cc:2105
#10 0x000000000075ecb3 in close_cached_tables (thd=0x166e808, tables=0x160ba68, have_lock=false, wait_for_refresh=true) at sql_base.cc:1003
#11 0x00000000006fc573 in reload_acl_and_cache (thd=0x166e808, options=4, tables=0x160ba68, write_to_binlog=0x414602af) at sql_parse.cc:6927
#12 0x00000000007078f5 in mysql_execute_command (thd=0x166e808) at sql_parse.cc:4049
#13 0x000000000070a36f in mysql_parse (thd=0x166e808, inBuf=0x160b9a0 "FLUSH TABLE t1", length=14, found_semicolon=0x41460f20) at sql_parse.cc:5941
#14 0x000000000070af8d in dispatch_command (command=COM_QUERY, thd=0x166e808, packet=0x1607949 "FLUSH TABLE t1", packet_length=14) at sql_parse.cc:1062
#15 0x000000000070c431 in do_command (thd=0x166e808) at sql_parse.cc:744
#16 0x00000000006f99bf in handle_one_connection (arg=0x166e808) at sql_connect.cc:1163
#17 0x00007fccf7a61040 in start_thread () from /lib64/libpthread.so.0
#18 0x00007fccf6a0208d in clone () from /lib64/libc.so.6
#19 0x0000000000000000 in ?? ()

The testcase could be not applied to MySQL 5.1,5.0
because the syntax piece "IN EXCLUSIVE MODE" is there
not supported.

My environment:
---------------
- mysql-next-bugfixing 2009-09-09
- ./BUILD/compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo

How to repeat:
See above

I've tried to reproduce the bug with the current version of mysql-next-bugfixing. I've found that the assertion is triggered depending on the LOCK TABLES statement issued in connection "con_1".

Of the possible combinations of READ and WRITE locks and different locking order, only the following result in the assert:
LOCK TABLES t1 WRITE, v1 READ;
LOCK TABLES v1 WRITE, t1 READ;

So, for example, 
LOCK TABLES t1 READ, v1 WRITE;
LOCK TABLES v1 READ, t1 WRITE;
both work fine.

The use of transactional or non-transactional locks make no difference. So 
LOCK TABLES v1 IN EXCLUSIVE MODE, t1 IN SHARE MODE;
cause an assert while 
LOCK TABLES t1 IN SHARE MODE, v1 IN EXCLUSIVE MODE;
does not. This makes sense as the transactional locks are converted to non-transactional.

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/83712

2824 Jon Olav Hauglid	2009-09-18
      Bug #47249 assert in MDL_global_lock::is_lock_type_compatible
      
      This assert could be triggered if LOCK TABLES were used to lock
      both a table and a view that used the same table. The table would have
      to be first WRITE locked and then READ locked. So "LOCK TABLES v1
      WRITE, t1 READ" would eventually trigger the assert, "LOCK TABLES
      v1 READ, t1 WRITE" would not. The reason is that the ordering of locks
      in the interal representation made a difference when executing 
      FLUSH TABLE on the table.
      
      During FLUSH TABLE, a lock was upgraded to exclusive. If this lock
      is of type MDL_SHARED and not MDL_SHARED_UPGRADABLE, an internal
      counter in the MDL subsystem would get out of sync. This would happen
      if the *last* mention of the table in LOCK TABLES was a READ lock.
      
      The counter in question is the number exclusive locks (active or intention). 
      This is used to make sure a global metadata lock is only taken when the 
      counter is zero (= no conflicts). The counter was increased when a
      MDL_EXCLUSIVE or MDL_SHARED_UPGRADABLE lock is taken, but not when 
      upgrade_shared_lock_to_exclusive() was used to upgrade directly
      from MDL_SHARED to MDL_EXCLUSIVE. This patch fixed the problem by increasing 
      the counter if a MDL_SHARED lock is upgraded to MDL_EXCLUSIVE.
      
      Test case added to mdl_sync.test.

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/84241

2834 Jon Olav Hauglid	2009-09-23
      Bug #47249 assert in MDL_global_lock::is_lock_type_compatible
      
      This assert could be triggered if LOCK TABLES were used to lock
      both a table and a view that used the same table. The table would have
      to be first WRITE locked and then READ locked. So "LOCK TABLES v1
      WRITE, t1 READ" would eventually trigger the assert, "LOCK TABLES
      v1 READ, t1 WRITE" would not. The reason is that the ordering of locks
      in the interal representation made a difference when executing 
      FLUSH TABLE on the table.
      
      During FLUSH TABLE, a lock was upgraded to exclusive. If this lock
      was of type MDL_SHARED and not MDL_SHARED_UPGRADABLE, an internal
      counter in the MDL subsystem would get out of sync. This would happen
      if the *last* mention of the table in LOCK TABLES was a READ lock.
      
      The counter in question is the number exclusive locks (active or intention). 
      This is used to make sure a global metadata lock is only taken when the 
      counter is zero (= no conflicts). The counter is increased when a
      MDL_EXCLUSIVE or MDL_SHARED_UPGRADABLE lock is taken, but not when 
      upgrade_shared_lock_to_exclusive() is used to upgrade directly
      from MDL_SHARED to MDL_EXCLUSIVE. 
      
      This patch fixes the problem by searching for a TABLE instance locked
      with MDL_SHARED_UPGRADABLE or MDL_EXCLUSIVE before calling 
      upgrade_shared_lock_to_exclusive(). The patch also adds an assert checking
      that only MDL_SHARED_UPGRADABLE locks are upgraded to exclusive.
      
      Test case added to lock_multi.test.

Pushed to mysql-6.0-codebase-bugfixing (version 6.0.14-alpha)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:alik@sun.com-20090923103200-kyo2bakdo6tfb2fb) (merge vers: 6.0.14-alpha) (pib:11)

Noted in 6.0.14 changelog.

Using LOCK TABLES to lock a table and a view with the same name could
raise an assertion. 

Setting report to NDI pending push into 5.4.x.

Pushed to mysql-next-4284 (5.6.0-beta)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091209094613-abuml0vrrr90nvxt) (pib:16)

Since this bug is not repeatable in any publicly available tree with version < 6.0 there is nothing to document. So I am simply closing this bug.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)

No changelog entry needed.