MySQL Bugs: #45035: Altering table under LOCK TABLES results in "Error 1213 Deadlock found..."

Bug #45035	Altering table under LOCK TABLES results in "Error 1213 Deadlock found..."
Submitted:	22 May 2009 12:27	Modified:	7 Mar 2010 12:44
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	6.0.12-bzr, 5.4	OS:	Any
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
ALTER TABLE under LOCK TABLES can fail with "Error 1213 Deadlock found when trying to get lock; try restarting transaction" if the table being altered is both read and write locked. This is repeatable even for MyISAM tables.

This problem is not repeatable in 5.1. Preliminary analysis shows that this bug was introduced during re-factoring of reopen_tables() implementation which was done in 6.0 branch.

This bug was found while implementing support for new foreign keys in DDL statements under LOCK TABLES and makes execution of such statements quite
problematic.

How to repeat:
create table t1 (i int);
lock tables t1 write, t1 as a read, t1 as b read;
# The below statement emits "1213: Deadlock found when trying
# to get lock; try restarting transaction" error.
alter table t1 add column j int;

Thank you for the bug report. Verified as described:

c:\dbs>c:\dbs\6.0\bin\mysql -uroot --port=3600 --prompt="mysql 6.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.12-alpha-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 6.0 > use test
Database changed
mysql 6.0 > create table t1 (i int);
Query OK, 0 rows affected (0.15 sec)

mysql 6.0 > lock tables t1 write, t1 as a read, t1 as b read;
Query OK, 0 rows affected (0.00 sec)

mysql 6.0 > alter table t1 add column j int;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql 6.0 > exit
Bye

c:\dbs>51c

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.36-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >use test
Database changed
mysql 5.1 >create table t1 (i int);
Query OK, 0 rows affected (0.16 sec)

mysql 5.1 >lock tables t1 write, t1 as a read, t1 as b read;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >alter table t1 add column j int;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.1 >

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

2781 Konstantin Osipov	2009-05-24
      A fix and a test case for Bug#45035 "Altering table under LOCK TABLES 
      results in "Error 1213 Deadlock found...".
      
      If a user had atable locked with LOCK TABLES
      both for READ and for WRITE in the same connection, ALTER TABLE
      could fail.
      
      Root cause analysis:
      
      If a connection issues 
      
      LOCK TABLE t1 write, t1 a read, t1 b read;
      
      the new LOCK TABLES code in 6.0 (part of WL 3726) will create 
      the following list of TABLE_LIST (thd->locked_tables_list->m_locked_tables)
      objects:
      
      t1 b tl_read_no_insert, t1 a tl_read_no_insert, t1 tl_write
      
      Later on, when we try to ALTER table t1, mysql_alter_table()
      closes all TABLE instances and releases its thr_lock locks,
      keeping only an exclusive metadata lock on t1.
      
      But when ALTER is finished, Locked_table_list::reopen_tables()
      tries to restore the original list of open and locked tables.
      
      Before this patch, it used to do so one by one: 
      Open t1 b, get TL_READ_NO_INSERT lock,
      Open t1 a, get TL_READ_NO_INSERT lock
      Open t1, try to get TL_WRITE lock, deadlock.
      
      The cause of the deadlock is that thr_lock.c doesn't 
      resolve the situation when the read list only consists
      of locks taken by the same thread, and then this very
      thread tries to take a WRITE lock. Indeed, since
      thr_lock_multi always gets a sorted list of locks,
      WRITE locks always preced READ locks in the list
      to lock.
      
      Don't try to fix thr_lock.c defeciency, keep it simple.
      Instead, try to take all thr_lock locks at once
      in ::reopen_tables().
      modified:
        mysql-test/r/lock.result
        mysql-test/t/lock.test
        sql/sql_base.cc
        sql/sql_class.h

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

2781 Konstantin Osipov	2009-05-24
      A fix and a test case for Bug#45035 "Altering table under LOCK TABLES 
      results in "Error 1213 Deadlock found...".
      
      If a user had a table locked with LOCK TABLES
      for READ and for WRITE in the same connection, ALTER TABLE
      could fail.
      
      Root cause analysis:
      
      If a connection issues 
      
      LOCK TABLE t1 write, t1 a read, t1 b read;
      
      the new LOCK TABLES code in 6.0 (part of WL 3726) will create 
      the following list of TABLE_LIST (thd->locked_tables_list->m_locked_tables)
      objects:
      
      t1 b tl_read_no_insert, t1 a tl_read_no_insert, t1 tl_write
      
      Later on, when we try to ALTER table t1, mysql_alter_table()
      closes all TABLE instances and releases its thr_lock locks,
      keeping only an exclusive metadata lock on t1.
      
      But when ALTER is finished, Locked_table_list::reopen_tables()
      tries to restore the original list of open and locked tables.
      
      Before this patch, it used to do so one by one: 
      Open t1 b, get TL_READ_NO_INSERT lock,
      Open t1 a, get TL_READ_NO_INSERT lock
      Open t1, try to get TL_WRITE lock, deadlock.
      
      The cause of the deadlock is that thr_lock.c doesn't 
      resolve the situation when the read list only consists
      of locks taken by the same thread, followed by this very
      thread trying to take a WRITE lock. Indeed, since
      thr_lock_multi always gets a sorted list of locks,
      WRITE locks always precede READ locks in the list
      to lock.
      
      Don't try to fix thr_lock.c deficiency, keep it simple.
      Instead, try to take all thr_lock locks at once
      in ::reopen_tables().
      modified:
        mysql-test/r/lock.result
        mysql-test/t/lock.test
        sql/sql_base.cc
        sql/sql_class.h

Hello!

I have mentioned a couple of issues with the patch worth looking into in my review which was sent by e-mail.

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

2838 Konstantin Osipov	2009-08-03
      A fix and a test case for Bug#45035 "Altering table under LOCK TABLES
      results in "Error 1213 Deadlock found...".
      
      If a user had a table locked with LOCK TABLES
      for READ and for WRITE in the same connection, ALTER TABLE
      could fail.
                  
      Root cause analysis:
                            
      If a connection issues
                             
      LOCK TABLE t1 write, t1 a read, t1 b read;
                                                  
      the new LOCK TABLES code in 6.0 (part of WL 3726) will create
      the following list of TABLE_LIST objects 
      (thd->locked_tables_list->m_locked_tables):
                    
      {"t1" "b" tl_read_no_insert}, {"t1" "a" tl_read_no_insert}, 
      {"t1" "t1" tl_write }
                                                                       
      Later on, when we try to ALTER table t1, mysql_alter_table()
      closes all TABLE instances and releases its thr_lock locks,  
      keeping only an exclusive metadata lock on t1.                   
                                                          
      But when ALTER is finished, Locked_table_list::reopen_tables()
      tries to restore the original list of open and locked tables.  
                                                                         
      Before this patch, it used to do so one by one:                    
      Open t1 b, get TL_READ_NO_INSERT lock,               
      Open t1 a, get TL_READ_NO_INSERT lock  
      Open t1, try to get TL_WRITE lock, deadlock.
      
      The cause of the deadlock is that thr_lock.c doesn't
      resolve the situation when the read list only consists
      of locks taken by the same thread, followed by this very
      thread trying to take a WRITE lock. Indeed, since
      thr_lock_multi always gets a sorted list of locks,
      WRITE locks always precede READ locks in the list
      to lock.
      
      Don't try to fix thr_lock.c deficiency, keep this 
      code simple.
      Instead, try to take all thr_lock locks at once
      in ::reopen_tables().
     @ mysql-test/r/lock.result
        Update results: test case for Bug#45035
     @ mysql-test/t/lock.test
        Add a test case for Bug#45035
     @ sql/sql_base.cc
        Take all thr_lock locks at once in Locked_tables_list::reopen_tables().
     @ sql/sql_class.h
        Add a helper array to store tables for mysql_lock_tables()
        in reopen_tables()
     @ sql/sql_table.cc
        Update unlink_all_closed_tables() to the new signature.

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

2838 Konstantin Osipov	2009-08-03
      A fix and a test case for Bug#45035 "Altering table under LOCK TABLES
      results in "Error 1213 Deadlock found...".
      
      If a user had a table locked with LOCK TABLES
      for READ and for WRITE in the same connection, ALTER TABLE
      could fail.
                  
      Root cause analysis:
                            
      If a connection issues
                             
      LOCK TABLE t1 write, t1 a read, t1 b read;
                                                  
      the new LOCK TABLES code in 6.0 (part of WL 3726) will create
      the following list of TABLE_LIST objects 
      (thd->locked_tables_list->m_locked_tables):
                    
      {"t1" "b" tl_read_no_insert}, {"t1" "a" tl_read_no_insert}, 
      {"t1" "t1" tl_write }
                                                                       
      Later on, when we try to ALTER table t1, mysql_alter_table()
      closes all TABLE instances and releases its thr_lock locks,  
      keeping only an exclusive metadata lock on t1.                   
                                                          
      But when ALTER is finished, Locked_table_list::reopen_tables()
      tries to restore the original list of open and locked tables.  
                                                                         
      Before this patch, it used to do so one by one:                    
      Open t1 b, get TL_READ_NO_INSERT lock,               
      Open t1 a, get TL_READ_NO_INSERT lock  
      Open t1, try to get TL_WRITE lock, deadlock.
      
      The cause of the deadlock is that thr_lock.c doesn't
      resolve the situation when the read list only consists
      of locks taken by the same thread, followed by this very
      thread trying to take a WRITE lock. Indeed, since
      thr_lock_multi always gets a sorted list of locks,
      WRITE locks always precede READ locks in the list
      to lock.
      
      Don't try to fix thr_lock.c deficiency, keep this 
      code simple.
      Instead, try to take all thr_lock locks at once
      in ::reopen_tables().
     @ mysql-test/r/lock.result
        Update results: test case for Bug#45035
     @ mysql-test/t/lock.test
        Add a test case for Bug#45035
     @ sql/sql_base.cc
        Take all thr_lock locks at once in Locked_tables_list::reopen_tables().
     @ sql/sql_class.h
        Add a helper array to store tables for mysql_lock_tables()
        in reopen_tables().
     @ sql/sql_table.cc
        Update unlink_all_closed_tables() to the new signature.

Pushed into 5.4.4.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090824135126-2rngffvth14a8bpj) (version source revid:kostja@sun.com-20090803152604-apwh4116zit8boyu) (merge vers: 5.4.4-alpha) (pib:11)

Noted in 5.4.4 changelog.

If a session had a table locked with LOCK TABLES for both READ and
WRITE, ALTER TABLE could fail.

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

2999 Konstantin Osipov	2009-12-08
      Backport of:
      ----------------------------------------------------------
      revno: 2617.69.2
      committer: Konstantin Osipov <kostja@sun.com>
      branch nick: 5.4-azalea-bugfixing
      timestamp: Mon 2009-08-03 19:26:04 +0400
      message:
        A fix and a test case for Bug#45035 "Altering table under LOCK TABLES
        results in "Error 1213 Deadlock found...".
      
        If a user had a table locked with LOCK TABLES
        for READ and for WRITE in the same connection, ALTER TABLE
        could fail.
      
        Root cause analysis:
      
        If a connection issues
      
        LOCK TABLE t1 write, t1 a read, t1 b read;
      
        the new LOCK TABLES code in 6.0 (part of WL 3726) will create
        the following list of TABLE_LIST objects
        (thd->locked_tables_list->m_locked_tables):
      
        {"t1" "b" tl_read_no_insert}, {"t1" "a" tl_read_no_insert},
        {"t1" "t1" tl_write }
      
        Later on, when we try to ALTER table t1, mysql_alter_table()
        closes all TABLE instances and releases its thr_lock locks,
        keeping only an exclusive metadata lock on t1.
      
        But when ALTER is finished, Locked_table_list::reopen_tables()
        tries to restore the original list of open and locked tables.
      
        Before this patch, it used to do so one by one:
        Open t1 b, get TL_READ_NO_INSERT lock,
        Open t1 a, get TL_READ_NO_INSERT lock
        Open t1, try to get TL_WRITE lock, deadlock.
      
        The cause of the deadlock is that thr_lock.c doesn't
        resolve the situation when the read list only consists
        of locks taken by the same thread, followed by this very
        thread trying to take a WRITE lock. Indeed, since
        thr_lock_multi always gets a sorted list of locks,
        WRITE locks always precede READ locks in the list
        to lock.
      
        Don't try to fix thr_lock.c deficiency, keep this
        code simple.
        Instead, try to take all thr_lock locks at once
        in ::reopen_tables().
     @ mysql-test/r/lock.result
        Update results: test case for Bug#45035.
     @ mysql-test/t/lock.test
        Add a test case for Bug#45035.
     @ sql/sql_base.cc
        Take all thr_lock locks at once in Locked_tables_list::reopen_tables().
     @ sql/sql_class.h
        Add a helper array to store tables for mysql_lock_tables()
        in reopen_tables().
     @ sql/sql_table.cc
        Update unlink_all_closed_tables() to the new signature.

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-20091208083845-kzlpva8tonby909l) (pib:16)

Closing this bug as it is not repeatable in publicly available trees with versions < 6.0.

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.