Two years ago Ovais Tariq had explained in detail what kinds of problems existed before MySQL introduced metadata locks in 5.5.3 and how these locks help to prevent them. Still, some implications of metadata locking in MySQL remain unclear for users – DBAs and even software developers that target recent MySQL versions. I’ve decided to include a slide or two into the presentation about InnoDB locks and deadlocks I plan to make (with my colleague Nilnandan Joshi) on April 16 at Percona Live 2015.
I decided to do this as recently I’ve got an issue to work on where it was claimed that the behavior of SELECT blocking TRUNCATE TABLE is wrong, just because transaction isolation level was set to READ COMMITTED and thus there should be no locks set by SELECT and transaction should not even start no matter what the value of autocommit is (it was explicitly set to 0 by smart software).
The MySQL manual clearly says:
“To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table’s structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.”
So, the real challenge was to show these metadata locks still set in a transaction that started implicitly, by SELECT immediately following SET autocommit=0 in a session. It was a good chance to check how metadata locks are exposed in MySQL 5.7 via Performance Schema, so I’ve set up a simple test.
First of all, I’ve enabled instrumentation for metadata locks:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | [openxs@centos 5.7]$ bin/mysql --no-defaults -uroot -proot mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.6-m16 MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'global_instrumentation'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 |
Then I’ve set up a simple test based on the details from the issue (I’ve create the InnoDB table, t, and added a row to it before this):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> set session autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@autocommit, @@tx_isolation; +--------------+----------------+ | @@autocommit | @@tx_isolation | +--------------+----------------+ | 0 | READ-COMMITTED | +--------------+----------------+ 1 row in set (0.00 sec) mysql> select * from t limit 1; +----+------+ | id | val | +----+------+ | 1 | 1 | +----+------+ 1 row in set (0.00 sec) |
Now, from another session I tried to TRUNCATE the table before the fist session got a chance to do explicit or implicit COMMIT (In the issue I mentioned software used just had not cared to do this, assuming transaction had not started. It worked with MySQL 5.1 really well that way.)
1 | mysql> truncate table t; |
I was not surprised that TRUNCATE hung. Manual clearly says that until transaction is committed we do not release metadata locks. But let’s check them in Performance Schema (from the first session, where we executed SELECT):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | mysql> select * from performance_schema.metadata_locksG *************************** 1. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: test OBJECT_NAME: t OBJECT_INSTANCE_BEGIN: 140450128308592 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_parse.cc:5585 OWNER_THREAD_ID: 27 OWNER_EVENT_ID: 17 *************************** 2. row *************************** OBJECT_TYPE: GLOBAL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: 140450195436144 LOCK_TYPE: INTENTION_EXCLUSIVE LOCK_DURATION: STATEMENT LOCK_STATUS: GRANTED SOURCE: sql_base.cc:5224 OWNER_THREAD_ID: 30 OWNER_EVENT_ID: 8 *************************** 3. row *************************** OBJECT_TYPE: SCHEMA OBJECT_SCHEMA: test OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: 140450195434272 LOCK_TYPE: INTENTION_EXCLUSIVE LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_base.cc:5209 OWNER_THREAD_ID: 30 OWNER_EVENT_ID: 8 *************************** 4. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: test OBJECT_NAME: t OBJECT_INSTANCE_BEGIN: 140450195434368 LOCK_TYPE: EXCLUSIVE LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: sql_parse.cc:5585 OWNER_THREAD_ID: 30 OWNER_EVENT_ID: 8 *************************** 5. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: performance_schema OBJECT_NAME: metadata_locks OBJECT_INSTANCE_BEGIN: 140450128262384 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_parse.cc:5585 OWNER_THREAD_ID: 27 OWNER_EVENT_ID: 18 5 rows in set (0.00 sec) |
Note SHARED_READ lock set on table t and EXCLUSIVE lock is pending on the same table t above. TRUNCATE is blocked (as DDL).
Note also locks related to out SELECT from the metadata_locks table in the output. Yes, access to Performance Schema is also protected with metadata locks!
We can get a nice view of all metadata locks from other sessions, excluding our current one, and check also all we could get about them before MySQL 5.7 (just a thread state in the SHOW PROCESSLIST output):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO FROM performance_schema.metadata_locks INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID WHERE PROCESSLIST_ID <> CONNECTION_ID(); +-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_INFO | +-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------+ | GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE | GRANTED | 30 | 8 | truncate table t | | SCHEMA | test | NULL | INTENTION_EXCLUSIVE | GRANTED | 30 | 8 | truncate table t | | TABLE | test | t | EXCLUSIVE | PENDING | 30 | 8 | truncate table t | +-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------+ 3 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+---------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+---------------------------------+------------------+ | 5 | root | localhost | test | Query | 0 | starting | show processlist | | 8 | root | localhost | test | Query | 50 | Waiting for table metadata lock | truncate table t | +----+------+-----------+------+---------+------+---------------------------------+------------------+ 2 rows in set (0.00 sec) |
As soon as I complete transaction where SELECT was executed, TRUNCATE completes and we see no pending metadata locks:
1 2 3 4 5 6 7 8 | mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO FROM performance_schema.metadata_locks INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID WHERE PROCESSLIST_ID <> CONNECTION_ID(); Empty set (0.01 sec) mysql> select * from t; Empty set (0.00 sec) |
To summarize, MySQL 5.7 allows you to study all metadata locks in detail. They are set for both transactional and non-transactional tables, but remember that if you use autocommit=0 or start transaction explicitly they are released only when commit happens, implicit or explicit. If you want single statement SELECT to not block any DDL after it is completed, make sure to COMMIT immediately or use autocommit=1.
We can surely call the behavior of metadata locks for this case a “bug” or file a “feature request” to change it, but for now any software that is supposed to work with MySQL 5.5.3+ should just take all implications of metadata locks into account.
Thanks a lot for that post ! This explains a trouble we got on a production server. To be sure that I understand well, does that mean that it occurs only in REPEATABLE-READ mode ? If I switch my MySQL client to e.g. READ-COMMITED, there is no metadata lock ?