[MDEV-5619] CREATE OR REPLACE does not release MDL_EXCLUSIVE upon failure - JIRA

Details

Type: Technical task
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: None
Fix Version/s: 10.0.10
Component/s: None
Labels:
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

Gliffy Diagrams

Attachments

Activity

Hide

Permalink

Michael Widenius added a comment - 2014-03-12 11:31

Fixed by releasing meta data locks and doing automatic UNLOCK TABLE if there are no more locked tables after table was dropped.

Show

Michael Widenius added a comment - 2014-03-12 11:31 Fixed by releasing meta data locks and doing automatic UNLOCK TABLE if there are no more locked tables after table was dropped.

CREATE OR REPLACE does not release MDL_EXCLUSIVE upon failure

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile