Bug #43685 Lock table affects other non-related tables
Submitted: 17 Mar 2009 2:41 Modified: 7 Mar 2010 12:49
Reporter: Adam Dixon Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.0.78,5.1.32 OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any

[17 Mar 2009 2:41] Adam Dixon 
Description:
Below is the SQL log of a test case, where the server seems to take some sort of global lock on the database, but only specific tables have been specified in the lock tables statement.

14,15,16 are test perl script (attachment), connection 8 is another window which attempts to take a write lock on a non-related table, and then flush that table.

The connection 8 hangs on attempting to flush the table, until connection 14 issues the unlock tables command. (connection 8 does successfully return at the lock tables write statement too..)

090317 12:38:20	     14 Connect     root@localhost on test
		     14 Query       set autocommit=1
		     15 Connect     root@localhost on test
		     15 Query       set autocommit=1
		     16 Connect     root@localhost on test
		     16 Query       set autocommit=1
		     15 Query       select * from jd_read_0
		     16 Query       select * from jd_read_1
		     14 Query       lock table jd_write_0 write , jd_write_1 write
		     14 Query       delete from jd_write_0
		     14 Query       delete from jd_write_1
		     14 Query       insert ignore into jd_write_0 values ( 1016 , 10000015 , 3 )
		     14 Query       insert ignore into jd_write_1 values ( 1016 , 10000015 , 3 )
		     14 Query       analyze table jd_write_0
		     14 Query       analyze table jd_write_1
090317 12:38:21	     14 Query       insert ignore into jd_write_0 values ( 1079 , 10000015 , 47 )
		     14 Query       insert ignore into jd_write_1 values ( 1079 , 10000015 , 47 )
090317 12:38:22	     14 Query       insert ignore into jd_write_0 values ( 1072 , 10000043 , 46 )
		     14 Query       insert ignore into jd_write_1 values ( 1072 , 10000043 , 46 )
090317 12:38:23	     14 Query       insert ignore into jd_write_0 values ( 1079 , 10000043 , 47 )
		     14 Query       insert ignore into jd_write_1 values ( 1079 , 10000043 , 47 )
		      8 Query       lock table jd_pack_0 write
090317 12:38:24	     14 Query       insert ignore into jd_write_0 values ( 1881 , 10000043 , 556 )
		     14 Query       insert ignore into jd_write_1 values ( 1881 , 10000043 , 556 )
090317 12:38:25	      8 Query       flush table jd_pack_0
		     14 Query       insert ignore into jd_write_0 values ( 1016 , 10000058 , 3 )
		     14 Query       insert ignore into jd_write_1 values ( 1016 , 10000058 , 3 )
090317 12:38:26	     14 Query       insert ignore into jd_write_0 values ( 1053 , 10000058 , 30 )
		     14 Query       insert ignore into jd_write_1 values ( 1053 , 10000058 , 30 )
090317 12:38:27	     14 Query       insert ignore into jd_write_0 values ( 1796 , 10000058 , 495 )
		     14 Query       insert ignore into jd_write_1 values ( 1796 , 10000058 , 495 )
090317 12:38:28	     14 Query       insert ignore into jd_write_0 values ( 1955 , 10000058 , 594 )
		     14 Query       insert ignore into jd_write_1 values ( 1955 , 10000058 , 594 )
090317 12:38:29	     14 Query       insert ignore into jd_write_0 values ( 1955 , 10000059 , 594 )
		     14 Query       insert ignore into jd_write_1 values ( 1955 , 10000059 , 594 )
090317 12:38:30	     14 Query       insert ignore into jd_write_0 values ( 1079 , 10000060 , 47 )
		     14 Query       insert ignore into jd_write_1 values ( 1079 , 10000060 , 47 )
090317 12:38:31	     14 Query       insert ignore into jd_write_0 values ( 1086 , 10000068 , 57 )
		     14 Query       insert ignore into jd_write_1 values ( 1086 , 10000068 , 57 )
090317 12:38:32	     14 Query       insert ignore into jd_write_0 values ( 1955 , 10000068 , 594 )
		     14 Query       insert ignore into jd_write_1 values ( 1955 , 10000068 , 594 )
090317 12:38:33	     14 Query       insert ignore into jd_write_0 values ( 1086 , 10000073 , 57 )
		     14 Query       insert ignore into jd_write_1 values ( 1086 , 10000073 , 57 )
090317 12:38:34	     14 Query       insert ignore into jd_write_0 values ( 1783 , 10000073 , 493 )
		     14 Query       insert ignore into jd_write_1 values ( 1783 , 10000073 , 493 )
090317 12:38:35	     14 Query       insert ignore into jd_write_0 values ( 1881 , 10000073 , 556 )
		     14 Query       insert ignore into jd_write_1 values ( 1881 , 10000073 , 556 )
090317 12:38:36	     14 Query       insert ignore into jd_write_0 values ( 1955 , 10000073 , 594 )
		     14 Query       insert ignore into jd_write_1 values ( 1955 , 10000073 , 594 )
090317 12:38:37	     14 Query       insert ignore into jd_write_0 values ( 1072 , 10000083 , 46 )
		     14 Query       insert ignore into jd_write_1 values ( 1072 , 10000083 , 46 )
090317 12:38:38	     14 Query       insert ignore into jd_write_0 values ( 1955 , 10000086 , 594 )
		     14 Query       insert ignore into jd_write_1 values ( 1955 , 10000086 , 594 )
090317 12:38:39	     14 Query       insert ignore into jd_write_0 values ( 1955 , 10000101 , 594 )
		     14 Query       insert ignore into jd_write_1 values ( 1955 , 10000101 , 594 )
090317 12:38:40	     14 Query       insert ignore into jd_write_0 values ( 1645 , 10000107 , 784 )
		     14 Query       insert ignore into jd_write_1 values ( 1645 , 10000107 , 784 )
090317 12:38:41	     14 Query       insert ignore into jd_write_0 values ( 1955 , 10000109 , 594 )
		     14 Query       insert ignore into jd_write_1 values ( 1955 , 10000109 , 594 )
090317 12:38:42	     14 Query       insert ignore into jd_write_0 values ( 2067 , 10000119 , 759 )
		     14 Query       insert ignore into jd_write_1 values ( 2067 , 10000119 , 759 )
090317 12:38:43	     14 Query       insert ignore into jd_write_0 values ( 1645 , 10000123 , 784 )
		     14 Query       insert ignore into jd_write_1 values ( 1645 , 10000123 , 784 )
090317 12:38:44	     14 Query       insert ignore into jd_write_0 values ( 1955 , 10000139 , 594 )
		     14 Query       insert ignore into jd_write_1 values ( 1955 , 10000139 , 594 )
090317 12:38:45	     14 Query       insert ignore into jd_write_0 values ( 1951 , 10000140 , 590 )
		     14 Query       insert ignore into jd_write_1 values ( 1951 , 10000140 , 590 )
090317 12:38:46	     14 Query       insert ignore into jd_write_0 values ( 1645 , 10000146 , 784 )
		     14 Query       insert ignore into jd_write_1 values ( 1645 , 10000146 , 784 )
090317 12:38:47	     14 Query       insert ignore into jd_write_0 values ( 2067 , 10000146 , 759 )
		     14 Query       insert ignore into jd_write_1 values ( 2067 , 10000146 , 759 )
090317 12:38:48	     14 Query       insert ignore into jd_write_0 values ( 1075 , 10000148 , 49 )
		     14 Query       insert ignore into jd_write_1 values ( 1075 , 10000148 , 49 )
090317 12:38:49	     14 Query       insert ignore into jd_write_0 values ( 1079 , 10000148 , 47 )
		     14 Query       insert ignore into jd_write_1 values ( 1079 , 10000148 , 47 )
090317 12:38:50	     14 Query       unlock tables
		     15 Quit       
		     14 Quit       
		     16 Quit       

How to repeat:
You can also repeat this by running the perl script, then attempting to open the database via 'mysql -u root test' it hangs on obtaining field list.

Simple to repeat;
mysql -u root test < create_tables.txt
Window 1: ./test.pl host=127.0.0.1 port=3306 database=test user=root password=
Window 2: mysql -u root test
<hang>

OR

Window 1: ./test.pl host=127.0.0.1 port=3306 database=test user=root password=
Window 2: mysql> lock table jd_pack_0 write ;
Window 2: mysql> flush table jd_pack_0 ;
[17 Mar 2009 2:54] Adam Dixon 
The same can be repeated by simply this;

Window 1: lock tables t1 write;
Window 1: analyze table t1;

Window 2: lock table t2 write;
Window 2: flush table t2; (hang until Window 1 unlock tables).
[19 Apr 2009 12:27] Konstantin Osipov 
A member of the bug verification team: please verify the bug.
[27 May 2009 11:56] Bugs System 
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/75044

2912 Konstantin Osipov	2009-05-27
      A tentative fix for Bug#43685 "Lock table affects other non-related
      tables"
      
      FLUSH TABLE <table_list> in 5.1 would wait for all tables with 
      old versions to go away from the table definition cache, 
      rather than only waiting for tables in the <table_list>.
      
      The fix is to back-port a more elaborate FLUSH TABLES
      implementation from 6.0.
      modified:
        sql/sql_base.cc
[3 Jun 2009 20:33] Konstantin Osipov 
The patch can lead to deadlocks if we have LOCK TABLE t1 READ; FLUSH TABLES
in a concurrent connection.
Something gotta give, in 6.0 we prohibited FLUSH TABLES with LOCK TABLES.. READ, this is how this solution, that I backported from 6.0, became possible.

But then again in 6.0 Bug#42465.
The patch, however, should be good enough as a solution for an individual customer.
[22 Aug 2009 9:09] Konstantin Osipov 
See also Bug#33278.
[14 Jan 2010 10:30] Konstantin Osipov 
Hello,
This should be gone in next-4284.
Please try to repeat, if not repeatable, add a test case, and close this bug (set to patch queued).
[15 Jan 2010 11:47] Bugs System 
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/97048

3052 Jon Olav Hauglid	2010-01-15
      Bug #43685 Lock table affects other non-related tables
      
      The problem was that FLUSH TABLE <table_list> would block, 
      waiting for all tables with old versions to be removed from 
      the table definition cache, rather than waiting for only 
      the tables in <table_list>. This could happen if FLUSH TABLE
      was used in combination with LOCK TABLES.
      
      With the new MDL code, this problem is no longer repeatable.
      Regression test case added to lock.test. This commit contains
      no code changes.
[15 Jan 2010 11:50] Jon Olav Hauglid 
Test case pushed to mysql-next-4284 and merged to mysql-6.0-codebase-4284.
[16 Feb 2010 16:49] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:jon.hauglid@sun.com-20100115114912-d7w1m5oeb6mktu86) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 16:59] Bugs System 
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:jon.hauglid@sun.com-20100115114722-q1df7xd2o3fvg0v3) (pib:16)
[3 Mar 2010 2:04] Paul DuBois 
Noted in 6.0.14 changelog.

When used in conjunction with LOCK TABLES, FLUSH TABLE tbl_list
waited for all tables with old versions to clear from the table
definition list, rather than only the named tables.

Setting report to Need Merge pending push of Celosia to release tree.
[6 Mar 2010 10:59] Bugs System 
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)
[7 Mar 2010 12:49] Paul DuBois 
Noted in 5.5.3 changelog.