MySQL Bugs: #45225: Locking: hang if drop table with no timeout

Bug #45225	Locking: hang if drop table with no timeout
Submitted:	31 May 2009 22:41	Modified:	20 Nov 2010 22:56
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	6.0-codebase	OS:	Linux (SUSE 10 / 64-bit)
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any
Tags:	mdl

Description:
I try to DROP a table which was opened by another transaction.
Until recently this succeeded (which was bug#989).
Now it hangs.
That's fine.
But there's no timeout
(innodb_lock_wait_timeout "applies to ... row locks only")
(table_lock_wait_timeout is "currently unused").
That's not fine.
Hanging forever is as bad as succeeding,
so I believe it should be regarded as an unauthorized behaviour change
rather than as a fix for bug#989.

How to repeat:
Start two connections. Call them t1 and t2.

On t1, say:
use test
drop table t;
set @@autocommit=0;
create table t (s1 int primary key, s2 int, s3 int)
  engine=innodb partition by hash (s1);

On t2, say:
use test
set @@autocommit=0;
insert into t values (7,7,7);

On t1, say:
drop table t;

... It will hang, and ignore any timeouts.

Thank you for the report.

Verified as described.

No partitioning needed to repeat the problem.

Suggestion: use table_lock_wait_timeout which currently applies to MyISAM
only, and use timed_wait in mdl.cc.

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

2856 Jon Olav Hauglid	2009-08-14
      Bug #45225 Backup: RESTORE fails, thinking it finds an existing table
      
      This is the first commit of a patch to implement MDL lock timeouts.
      It changes acquire_exclusive_lock() and upgrade_shared_lock_to_exclusive()
      to wait for locks depending on the value of the table_lock_wait_timeout
      server system variable. The default value of the variable is 0 which 
      allows the statement to wait for locks indefintely. 
      
      A test case for both acquire (using DROP TABLE) and upgrade (ALTER TABLE)
      is in bug45225.test. This is a temporary file - the test case will be
      merged into an existing file before the final commit.

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

2803 Jon Olav Hauglid	2009-08-27
      Bug #45225 Locking: hang if drop table with no timeout
      
      This patch introduces metadata lock timeouts for exclusive locks
      (DDL operations). The timeout is specified in seconds using the 
      new server session variable "lock_wait_timeout". 
      
      The default setting of 0 causes no timeouts - i.e. infinite waiting.
      
      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/98493

3055 Jon Olav Hauglid	2010-01-28
      Bug #45225 Locking: hang if drop table with no timeout
      
      This patch introduces timeouts for metadata locks. 
      
      The timeout is specified in seconds using the new dynamic system 
      variable  "lock_wait_timeout" which has both GLOBAL and SESSION
      scopes. Allowed values range from 1 to 31536000 seconds (= 1 year). 
      The default value is 1 year.
      
      The new server parameter "lock-wait-timeout" can be used to set
      the default value parameter upon server startup.
      
      "lock_wait_timeout" applies to all statements that use metadata locks.
      These include DML and DDL operations on tables, views, stored procedures
      and stored functions. They also include LOCK TABLES, FLUSH TABLES WITH
      READ LOCK and HANDLER statements.
      
      The patch also changes MyISAM table locking code to use the same 
      system variable. InnoDB row locks are unaffected. 
      The now unused "table_lock_wait_timeout" system variable is removed.
      
      It's important to note that the timeout value is used for each lock
      acquired and that one statement can take more than one lock.
      A statement can therefore block for longer than the lock_wait_timeout 
      value before reporting a timeout error. When lock timeout occurs, 
      ER_LOCK_WAIT_TIMEOUT is reported.
      
      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/98657

3055 Jon Olav Hauglid	2010-01-29
      Bug #45225 Locking: hang if drop table with no timeout
      
      This patch introduces timeouts for metadata locks. 
      
      The timeout is specified in seconds using the new dynamic system 
      variable  "lock_wait_timeout" which has both GLOBAL and SESSION
      scopes. Allowed values range from 1 to 31536000 seconds (= 1 year). 
      The default value is 1 year.
      
      The new server parameter "lock-wait-timeout" can be used to set
      the default value parameter upon server startup.
      
      "lock_wait_timeout" applies to all statements that use metadata locks.
      These include DML and DDL operations on tables, views, stored procedures
      and stored functions. They also include LOCK TABLES, FLUSH TABLES WITH
      READ LOCK and HANDLER statements.
      
      The patch also changes MyISAM table locking code to use the same 
      system variable. InnoDB row locks are unaffected. 
      
      It's important to note that the timeout value is used for each lock
      acquired and that one statement can take more than one lock.
      A statement can therefore block for longer than the lock_wait_timeout 
      value before reporting a timeout error. When lock timeout occurs, 
      ER_LOCK_WAIT_TIMEOUT is reported.
      
      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/98835

3058 Jon Olav Hauglid	2010-02-01
      Bug #45225 Locking: hang if drop table with no timeout
      
      This patch introduces timeouts for metadata locks. 
      
      The timeout is specified in seconds using the new dynamic system 
      variable  "lock_wait_timeout" which has both GLOBAL and SESSION
      scopes. Allowed values range from 1 to 31536000 seconds (= 1 year). 
      The default value is 1 year.
      
      The new server parameter "lock-wait-timeout" can be used to set
      the default value parameter upon server startup.
      
      "lock_wait_timeout" applies to all statements that use metadata locks.
      These include DML and DDL operations on tables, views, stored procedures
      and stored functions. They also include LOCK TABLES, FLUSH TABLES WITH
      READ LOCK and HANDLER statements.
      
      The patch also changes MyISAM table locking code to use the same 
      system variable. InnoDB row locks are unaffected. 
      
      It's important to note that the timeout value is used for each lock
      acquired and that one statement can take more than one lock.
      A statement can therefore block for longer than the lock_wait_timeout 
      value before reporting a timeout error. When lock timeout occurs, 
      ER_LOCK_WAIT_TIMEOUT is reported.
      
      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/99657

3086 Jon Olav Hauglid	2010-02-09
      Bug #45225 Locking: hang if drop table with no timeout
      
      This patch introduces timeouts for metadata locks. 
      
      The timeout is specified in seconds using the new dynamic system 
      variable  "lock_wait_timeout" which has both GLOBAL and SESSION
      scopes. Allowed values range from 1 to 31536000 seconds (= 1 year). 
      The default value is 1 year.
      
      The new server parameter "lock-wait-timeout" can be used to set
      the default value parameter upon server startup.
      
      "lock_wait_timeout" applies to all statements that use metadata locks.
      These include DML and DDL operations on tables, views, stored procedures
      and stored functions. They also include LOCK TABLES, FLUSH TABLES WITH
      READ LOCK and HANDLER statements.
      
      The patch also changes MyISAM table locking code to use the same 
      system variable. InnoDB row locks are unaffected. 
      
      It's important to note that the timeout value is used for each lock
      acquired and that one statement can take more than one lock.
      A statement can therefore block for longer than the lock_wait_timeout 
      value before reporting a timeout error. When lock timeout occurs, 
      ER_LOCK_WAIT_TIMEOUT is reported.
      
      Test case added to mdl_sync.test.
     @ include/thr_lock.h
        Introduced timeouts for thr_lock.c locks.
     @ mysql-test/r/mysqld--help-notwin.result
        Updated result file with the new server variable.
     @ mysql-test/suite/sys_vars/r/lock_wait_timeout_basic.result
        Added basic test for the new server variable.
     @ mysql-test/suite/sys_vars/t/lock_wait_timeout_basic.test
        Added basic test for the new server variable.
     @ mysys/thr_lock.c
        Introduced timeouts for thr_lock.c locks.
     @ sql/mdl.cc
        Introduced timeouts for metadata locks.
     @ sql/mdl.h
        Introduced timeouts for metadata locks.
     @ sql/sql_base.cc
        Introduced timeouts in tdc_wait_for_old_versions().
     @ sql/sql_class.h
        Added new server variable lock_wait_timeout.
     @ sql/sys_vars.cc
        Added new server variable lock_wait_timeout.

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

3088 Jon Olav Hauglid	2010-02-10
      Bug #45225 Locking: hang if drop table with no timeout
      
      This patch introduces timeouts for metadata locks. 
      
      The timeout is specified in seconds using the new dynamic system 
      variable  "lock_wait_timeout" which has both GLOBAL and SESSION
      scopes. Allowed values range from 1 to 31536000 seconds (= 1 year). 
      The default value is 1 year.
      
      The new server parameter "lock-wait-timeout" can be used to set
      the default value parameter upon server startup.
      
      "lock_wait_timeout" applies to all statements that use metadata locks.
      These include DML and DDL operations on tables, views, stored procedures
      and stored functions. They also include LOCK TABLES, FLUSH TABLES WITH
      READ LOCK and HANDLER statements.
      
      The patch also changes thr_lock.c code (table data locks used by MyISAM
      and other simplistic engines) to use the same system variable.
      InnoDB row locks are unaffected.
      
      One exception to the handling of the "lock_wait_timeout" variable
      is delayed inserts. All delayed inserts are executed with a timeout
      of 1 year regardless of the setting for the global variable. As the
      connection issuing the delayed insert gets no notification of 
      delayed insert timeouts, we want to avoid unnecessary timeouts.
      
      It's important to note that the timeout value is used for each lock
      acquired and that one statement can take more than one lock.
      A statement can therefore block for longer than the lock_wait_timeout 
      value before reporting a timeout error. When lock timeout occurs, 
      ER_LOCK_WAIT_TIMEOUT is reported.
      
      Test case added to lock_multi.test.
     @ include/my_pthread.h
        Added macros for comparing two timespec structs.
     @ include/thr_lock.h
        Introduced timeouts for thr_lock.c locks.
     @ mysql-test/r/mysqld--help-notwin.result
        Updated result file with the new server variable.
     @ mysql-test/r/mysqld--help-win.result
        Updated result file with the new server variable.
     @ mysql-test/suite/sys_vars/r/lock_wait_timeout_basic.result
        Added basic test for the new server variable.
     @ mysql-test/suite/sys_vars/t/lock_wait_timeout_basic.test
        Added basic test for the new server variable.
     @ mysys/thr_lock.c
        Introduced timeouts for thr_lock.c locks.
     @ sql/mdl.cc
        Introduced timeouts for metadata locks.
     @ sql/mdl.h
        Introduced timeouts for metadata locks.
     @ sql/sql_base.cc
        Introduced timeouts in tdc_wait_for_old_versions().
     @ sql/sql_class.h
        Added new server variable lock_wait_timeout.
     @ sql/sys_vars.cc
        Added new server variable lock_wait_timeout.

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

3090 Jon Olav Hauglid	2010-02-11
      Bug #45225 Locking: hang if drop table with no timeout
      
      This patch introduces timeouts for metadata locks. 
      
      The timeout is specified in seconds using the new dynamic system 
      variable  "lock_wait_timeout" which has both GLOBAL and SESSION
      scopes. Allowed values range from 1 to 31536000 seconds (= 1 year). 
      The default value is 1 year.
      
      The new server parameter "lock-wait-timeout" can be used to set
      the default value parameter upon server startup.
      
      "lock_wait_timeout" applies to all statements that use metadata locks.
      These include DML and DDL operations on tables, views, stored procedures
      and stored functions. They also include LOCK TABLES, FLUSH TABLES WITH
      READ LOCK and HANDLER statements.
      
      The patch also changes thr_lock.c code (table data locks used by MyISAM
      and other simplistic engines) to use the same system variable.
      InnoDB row locks are unaffected.
      
      One exception to the handling of the "lock_wait_timeout" variable
      is delayed inserts. All delayed inserts are executed with a timeout
      of 1 year regardless of the setting for the global variable. As the
      connection issuing the delayed insert gets no notification of 
      delayed insert timeouts, we want to avoid unnecessary timeouts.
      
      It's important to note that the timeout value is used for each lock
      acquired and that one statement can take more than one lock.
      A statement can therefore block for longer than the lock_wait_timeout 
      value before reporting a timeout error. When lock timeout occurs, 
      ER_LOCK_WAIT_TIMEOUT is reported.
      
      Test case added to lock_multi.test.
     @ include/my_pthread.h
        Added macros for comparing two timespec structs.
     @ include/thr_lock.h
        Introduced timeouts for thr_lock.c locks.
     @ mysql-test/r/mysqld--help-notwin.result
        Updated result file with the new server variable.
     @ mysql-test/r/mysqld--help-win.result
        Updated result file with the new server variable.
     @ mysql-test/suite/sys_vars/r/lock_wait_timeout_basic.result
        Added basic test for the new server variable.
     @ mysql-test/suite/sys_vars/t/lock_wait_timeout_basic.test
        Added basic test for the new server variable.
     @ mysys/thr_lock.c
        Introduced timeouts for thr_lock.c locks.
     @ sql/mdl.cc
        Introduced timeouts for metadata locks.
     @ sql/mdl.h
        Introduced timeouts for metadata locks.
     @ sql/sql_base.cc
        Introduced timeouts in tdc_wait_for_old_versions().
     @ sql/sql_class.h
        Added new server variable lock_wait_timeout.
     @ sql/sys_vars.cc
        Added new server variable lock_wait_timeout.

Pushed to mysql-next-4284.

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

3096 Jon Olav Hauglid	2010-02-14
      Followup to Bug#45225 Locking: hang if drop table with no timeout
      
      This patch removes the unused server variable
      "table_lock_wait_timeout".
     @ mysql-test/r/variables.result
        Updated the test for Bug#28580 to reflect that 
        "table_lock_wait_timeout" no longer exists.
     @ mysql-test/t/variables.test
        Updated the test for Bug#28580 to reflect that 
        "table_lock_wait_timeout" no longer exists.

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

3098 Jon Olav Hauglid	2010-02-15
      Followup to Bug#45225 Locking: hang if drop table with no timeout
      
      This patch removes the unused server variable
      "table_lock_wait_timeout".
     @ mysql-test/r/variables.result
        Updated the test for Bug#28580 to reflect that 
        "table_lock_wait_timeout" no longer exists.
     @ mysql-test/t/variables.test
        Updated the test for Bug#28580 to reflect that 
        "table_lock_wait_timeout" no longer exists.

Followup pushed to mysql-next-4284 and merged to mysql-6.0-codebase-4284.

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

3101 Jon Olav Hauglid	2010-02-16
      Followup to Bug#45225 Locking: hang if drop table with no timeout
      
      This patch prevents system threads and system table accesses from
      using user-supplied values for "lock_wait_timeout". Instead all
      such accesses are done using the default value (1 year).
      
      This prevents background tasks (such as replication, events, 
      accessing stored function definitions, logging, reading time-zone
      information, etc.) from failing in cases where the global value
      of "lock_wait_timeout" is set very low.

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

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

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

3104 Jon Olav Hauglid	2010-02-19
      Followup to Bug#45225 Locking: hang if drop table with no timeout
      
      This patch prevents system threads and system table accesses from
      using user-supplied values for "lock_wait_timeout". Instead all
      such accesses are done using the default value (1 year).
      
      This prevents background tasks (such as replication, events, 
      accessing stored function definitions, logging, reading time-zone
      information, etc.) from failing in cases where the global value
      of "lock_wait_timeout" is set very low.
      
      The patch also includes some refactorings of open_and_lock_tables
      functions.
      
      No test case added.
     @ sql/mysql_priv.h
        Removed several convenience functions for 
        open_and_lock_tables_derived(). 
        Renamed open_and_lock_tables_derived() to open_and_lock_tables().
        Added MYSQL_LOCK_IGNORE_TIMEOUT flag which is used when opening
        and locking a table to use 1 year timeouts rather than the user-
        supplied timeout value (lock_wait_timeout).
     @ sql/sql_base.cc
        Added timeout value to Open_table_context.
        Removed several convenience functions for 
        open_and_lock_tables_derived(). 
        Renamed open_and_lock_tables_derived() to open_and_lock_tables().
     @ sql/sql_class.h
        Added timeout value to Open_table_context.

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

3106 Jon Olav Hauglid	2010-02-24
      Followup to Bug#45225 Locking: hang if drop table with no timeout
      
      This patch prevents system threads and system table accesses from
      using user-specified values for "lock_wait_timeout". Instead all
      such accesses are done using the default value (1 year).
      
      This prevents background tasks (such as replication, events, 
      accessing stored function definitions, logging, reading time-zone
      information, etc.) from failing in cases where the global value
      of "lock_wait_timeout" is set very low.
      
      The patch also simplifies the open tables API. Rather than adding
      another convenience function for opening and locking system tables,
      this patch removes most of the existing convenience functions for
      open_and_lock_tables_derived(). Before, open_and_lock_tables() was
      a convenience function that enforced derived tables handling, while
      open_and_lock_tables_derived() was the main function where derived
      tables handling was optional. Now, this convencience function is
      gone and the main function is renamed to open_and_lock_tables(). 
      
      No test case added as it would have required the use of --sleep to
      check that system threads and system tables have a different timeout
      value from the user-specified "lock_wait_timeout" system variable.

Second followup patch pushed to mysql-next-4284.

Not present in any released version. No changelog entry needed.

Setting report to Need Merge pending push of Celosia into release tree.

There's three separate changesets for this bug.
- http://lists.mysql.com/commits/99905
- http://lists.mysql.com/commits/100363
- http://lists.mysql.com/commits/101354

Have all three been considered for changelogs?
For example, the first introduces a new server variable "lock_wait_timeout"
while the second removes an old server variable "table_lock_wait_timeout".

Setting the bug back to Documenting for a double-check.

Noted in 6.0.14 changelog.

There was no timeout for attempts to acquire metadata locks (for
example, a DROP TABLE attempt for a table that was open in another
transaction would not time out).

To handle such situations, there is now a lock_wait_timeout system
variable that specifies the timeout in seconds for attempts to
acquire metadata locks. The allowed values range from 1 to 3153600 (1
year). The default is 3153600.
          
This timeout applies to all statements that use metadata locks. These
include DML and DDL operations on tables, views, stored procedures,
and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ
LOCK, and HANDLER statements.
          
The timeout value applies separately for each metadata lock attempt.
A given statement can require more than one lock, so it is possible
for the statement to block for longer than the lock_wait_timeout
value before reporting a timeout error. When lock timeout occurs,
ER_LOCK_WAIT_TIMEOUT is reported.
          
lock_wait_time does not apply to delayed inserts, which always
execute with a timeout of 1 year. This is done to avoid unnecessary
timeouts because a session that issues a delayed insert receives no
notification of delayed insert timeouts.

In addition, the unused table_lock_wait_timeout system variable was
removed. 

Setting report to Need Merge pending push of Celosia to release tree.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100306102742-yw9zzgw9ac5r65m5) (version source revid:bar@mysql.com-20100305074327-h09o5lw290s04lcf) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100306102638-qna09hbjb5gm940h) (version source revid:alik@sun.com-20100304153932-9hajxhhyanqbckmu) (pib:16)

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:alik@sun.com-20100304153932-9hajxhhyanqbckmu) (merge vers: 5.5.99-m3) (pib:16)

Noted in 5.5.3 changelog.

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

3021 Vasil Dimov	2010-04-14
      Disable parts of the innodb-index test that are not prepared for the
      metadata locks that were added at the MySQL level as part of the fix for
      Bug#45225 Locking: hang if drop table with no timeout

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

Already fixed in 5.5.x.

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:jimmy.yang@oracle.com-20100804103744-vbpeghipkz6pyc9z) (pib:21)