Details
-
Technical task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
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
|