|
If CREATE OR REPLACE, executed under lock, fails in the process, it keeps MDL_EXCLUSIVE lock on the table it attempted to re-create. It might happen both when the drop part fails, or when the create part fails (in the latter case, the connection holds a lock for a non-existing table).
After it happens, the connection itself does not realize it keeps the lock, and cannot use it any longer.
UNLOCK solves the problem, so it's not critical.
Example 1: failure upon creation.
We lock the table, attempt to re-create it, it gets dropped but not created; we still have MDL_EXCLUSIVE for it, but cannot create it until UNLOCK.
MariaDB [db]> create table t1 (i int);
|
Query OK, 0 rows affected (0.59 sec)
|
|
MariaDB [db]> lock table t1 write;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [db]> select * from information_schema.metadata_lock_info;
|
+-----------+--------------------------+---------------+----------------------+--------------+------------+
|
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
|
+-----------+--------------------------+---------------+----------------------+--------------+------------+
|
| 5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Global read lock | | |
|
| 5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Schema metadata lock | db | |
|
| 5 | MDL_SHARED_NO_READ_WRITE | MDL_EXPLICIT | Table metadata lock | db | t1 |
|
+-----------+--------------------------+---------------+----------------------+--------------+------------+
|
3 rows in set (0.00 sec)
|
|
MariaDB [db]> create or replace table t1;
|
ERROR 1113 (42000): A table must have at least 1 column
|
MariaDB [db]> show tables;
|
Empty set (0.00 sec)
|
|
MariaDB [db]> select * from information_schema.metadata_lock_info;
|
+-----------+-------------------------+---------------+----------------------+--------------+------------+
|
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
|
+-----------+-------------------------+---------------+----------------------+--------------+------------+
|
| 5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Global read lock | | |
|
| 5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Schema metadata lock | db | |
|
| 5 | MDL_EXCLUSIVE | MDL_EXPLICIT | Table metadata lock | db | t1 |
|
+-----------+-------------------------+---------------+----------------------+--------------+------------+
|
3 rows in set (0.00 sec)
|
|
MariaDB [db]> create table t1 (i int);
|
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES
|
MariaDB [db]> unlock tables;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [db]> select * from information_schema.metadata_lock_info;
|
Empty set (0.00 sec)
|
Example 2: failure upon drop.
We lock the table, attempt to re-create it, it does not get dropped; we still have MDL_EXCLUSIVE for it, but cannot read from it.
MariaDB [db]> create table t1 (a int, index(a)) engine=InnoDB;
|
Query OK, 0 rows affected (0.98 sec)
|
|
MariaDB [db]> create table t2 (b int, foreign key (b) references t1(a) on delete cascade) engine=InnoDB;
|
Query OK, 0 rows affected (0.79 sec)
|
|
MariaDB [db]> insert into t1 values (1),(2);
|
Query OK, 2 rows affected (0.07 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
MariaDB [db]> insert into t2 values (1),(2);
|
Query OK, 2 rows affected (0.09 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
MariaDB [db]> lock table t1 write;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [db]> create or replace table t1 (a int, index(a)) engine=InnoDB;
|
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
|
MariaDB [db]> show tables;
|
+--------------+
|
| Tables_in_db |
|
+--------------+
|
| t1 |
|
| t2 |
|
+--------------+
|
2 rows in set (0.00 sec)
|
|
MariaDB [db]> select * from information_schema.metadata_lock_info;
|
+-----------+-------------------------+---------------+----------------------+--------------+------------+
|
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
|
+-----------+-------------------------+---------------+----------------------+--------------+------------+
|
| 5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Global read lock | | |
|
| 5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | Schema metadata lock | db | |
|
| 5 | MDL_EXCLUSIVE | MDL_EXPLICIT | Table metadata lock | db | t1 |
|
+-----------+-------------------------+---------------+----------------------+--------------+------------+
|
3 rows in set (0.00 sec)
|
|
MariaDB [db]> select * from t1;
|
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES
|
revision-id: monty@askmonty.org-20140205193616-7vcabbq3nrpsvpjj
|
revno: 3981
|
branch-nick: 10.0-monty
|
|
[MDEV-5619]