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.

1 files changed, 37 insertions, 0 deletions

diff --git a/mysql-test/r/lock.result b/mysql-test/r/lock.result
index c60ec4bc1bd..46ce618b99b 100644
--- a/mysql-test/r/lock.result
+++ b/mysql-test/r/lock.result
@@ -282,5 +282,42 @@ insert into t1 values (1);
 # Ensure that metadata locks held by the transaction are released.
 drop table t1;
 #
+# Bug#45035 " Altering table under LOCK TABLES results in 
+# "Error 1213 Deadlock found..."
+#
+# When reopening tables under LOCK TABLES after ALTER TABLE,
+# 6.0 used to be taking thr_lock locks one by one, and
+# that would lead to a lock conflict. 
+# Check that taking all locks at once works.
+#
+drop table if exists t1;
+create table t1 (i int);
+lock tables t1 write, t1 as a read, t1 as b read;
+alter table t1 add column j int;
+unlock tables;
+drop table t1;
+create temporary table t1 (i int);
+#
+# This is just for test coverage purposes, 
+# when this is allowed, remove the --error.
+#
+lock tables t1 write, t1 as a read, t1 as b read;
+ERROR HY000: Can't reopen table: 't1'
+alter table t1 add column j int;
+unlock tables;
+drop table t1;
+#
+# Separate case for partitioned tables is important
+# because each partition has an own thr_lock object.
+#
+create table t1 (i int) partition by list (i)
+(partition p0 values in (1),
+partition p1 values in (2,3),
+partition p2 values in (4,5));
+lock tables t1 write, t1 as a read, t1 as b read;
+alter table t1 add column j int;
+unlock tables;
+drop table t1;
+#
 # End of 6.0 tests.
 #