Bug #108050 LOCK TABLE and then RENAME TABLE operation would releases all lock

• Submitted: 2 Aug 2022 12:29
• Modified: 4 Aug 2022 11:39
• Reporter: Shun Yi
• Status: Analyzing
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.7.37
• OS: Any
• CPU Architecture: Any

[2 Aug 2022 12:29] Shun Yi

Description:
Hi, guys

When I executed  LOCK TABLE test01 WRITE  command, the table of PERFORMANCE_SCHEMA.metadata_locks displayed the MDL lock duration as TRANSACTION,  But actually it should be EXPLICIT. 

And, after I executed  ALTER TABLE test01 RENAME test02, there was no MDL locks held by the test02.

At this point, another thread can insert or update data in the test02. However, version 8.0 cannot perform this operation.

How to repeat:
MySQL [test]> create table test01 (id int);
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> insert into test01 values (1);
Query OK, 1 row affected (0.00 sec)

MySQL [test]> lock table test01 write;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+----------------------+---------------+-------------+--------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE            | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+----------------------+---------------+-------------+--------+-----------------+----------------+
| GLOBAL      | NULL               | NULL           |       140204644129344 | INTENTION_EXCLUSIVE  | STATEMENT     | GRANTED     |        |              38 |             20 |
| SCHEMA      | test               | NULL           |       140204644112592 | INTENTION_EXCLUSIVE  | TRANSACTION   | GRANTED     |        |              38 |             20 |
| TABLE       | test               | test01         |       140204644038224 | SHARED_NO_READ_WRITE | TRANSACTION   | GRANTED     |        |              38 |             20 |
| BACKUP      | NULL               | NULL           |       140204644128992 | INTENTION_EXCLUSIVE  | STATEMENT     | GRANTED     |        |              38 |             20 |
| TABLE       | performance_schema | metadata_locks |       140204644145808 | SHARED_READ          | TRANSACTION   | GRANTED     |        |              38 |             21 |
+-------------+--------------------+----------------+-----------------------+----------------------+---------------+-------------+--------+-----------------+----------------+
5 rows in set (0.00 sec)

MySQL [test]> alter table test01 rename test02;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| GLOBAL      | NULL               | NULL           |       140204644129344 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |              38 |             20 |
| SCHEMA      | test               | NULL           |       140204644112592 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |        |              38 |             20 |
| BACKUP      | NULL               | NULL           |       140204644128992 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |              38 |             20 |
| TABLE       | performance_schema | metadata_locks |       140204644145808 | SHARED_READ         | TRANSACTION   | GRANTED     |        |              38 |             23 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
4 rows in set (0.01 sec)

------  Another thread  -------
MySQL [test]> insert into test02 values (2);
Query OK, 1 row affected (0.00 sec)

[3 Aug 2022 5:41] Shun Yi

Besides PFS incorrectly showing the lock status, what bothers me more is that on MySQL 5.7 when I execute LOCK TABLE and then RENAME TABLE, the table does not hold any MDL locks.
But MySQL 8.0 will hold EXPLICT lock on the renamed table. This might be a bug in MySQL 5.7.

[3 Aug 2022 13:01] MySQL Verification Team

Hi Mr. Yi,

Thank you for your bug report.

First of all, have you tested our latest 5.7 release, 5.7.39. It might not have the same behaviour.

Next, unlike 5.7, 8.0 has data dictionary, so it can behave much more consistently. What we are trying to communicate to you is that, even if this is a bug, it is a good question whether it is fixable in 5.7. Version 8.0 behaves correctly since it has under gone major restructuring regarding MDLs, the kind of restructuring that is not possible within 5.7.

We will first wait for your feedback on 5.7.39 and then we shall see whether a bug is fixable in 5.7.

[4 Aug 2022 8:32] Shun Yi

Hi, guys

After the same test, the results on MySQL 5.7.39 are the same as MySQL 5.7.37.

[4 Aug 2022 11:39] MySQL Verification Team

Hi Mr. Yi,

We shall have to check whether this bug is fixable in 5.7 and then we shall come back to you .....