Bug #103575 parent metadata locks for foreign keys on child table when foreign_key_checks=0
Submitted: 4 May 17:52 Modified: 18 May 13:12
Reporter: Serge Grachov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Data Dictionary Severity:S3 (Non-critical)
Version:8.0.23+ OS:Linux
Assigned to: CPU Architecture:Any
Tags: foreign keys, metadata locking

[4 May 17:52] Serge Grachov
Description:
Now:

Due to https://dev.mysql.com/worklog/task/?id=6049 mysql tries to get an EXCLUSIVE lock on a parent table when adding a foreign key to a child table (or dropping child table), locking out next transactions from reading parent table for a duration of PENDING. However it does so even when foreign_key_checks=0. At the same time allowing to create a foreign key to completely non-existing table when foreign_key_checks=0

Expected:

when foreign_key_checks=0 - no need to require EXCLUSIVE lock on a parent table at all or at least allow tx3 to read from parent table while tx2 still has PENDING but not GRANTED ?

How to repeat:
Steps:

create table testparent(id bigint, a bigint, primary key(id));
insert into testparent values (1,1);
create table testchild(id bigint, parentid bigint, primary key(id), index childindex (parentid));
set global max_write_lock_count=1;

tx1:
start transaction; select * from testparent where 1=0;

tx2:
start transaction; set foreign_key_checks=0; select * from performance_schema.metadata_locks; alter table testchild add foreign key xfkchild (parentid) references testparent(id);  -- waiting for table metadata lock
 
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | sw2_std            | testparent     | NULL        |       139837223042656 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:5760 |            1456 |             11 |
| TABLE       | performance_schema | metadata_locks | NULL        |       139838363896576 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:5760 |            1457 |              6 |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
2 rows in set (0.00 sec)

-- hangs, waiting for table metadata lock (tx1)

tx3:
start transaction; select * from performance_schema.metadata_locks; select * from testparent; -- waiting for table metadata lock

Query OK, 0 rows affected (0.00 sec)

+-------------------+--------------------+-------------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| OBJECT_TYPE       | OBJECT_SCHEMA      | OBJECT_NAME       | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE             | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------------+--------------------+-------------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| TABLE             | sw2_std            | testparent        | NULL        |       139837223042656 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:5760  |            1456 |             11 |
| GLOBAL            | NULL               | NULL              | NULL        |       139838364560560 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5426   |            1457 |              7 |
| BACKUP LOCK       | NULL               | NULL              | NULL        |       139838364560464 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5433   |            1457 |              7 |
| SCHEMA            | sw2_std            | NULL              | NULL        |       139838363913360 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5413   |            1457 |              7 |
| TABLE             | sw2_std            | testchild         | NULL        |       139838363851664 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:5760  |            1457 |              7 |
| TABLESPACE        | NULL               | sw2_std/testchild | NULL        |       139838363845904 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | lock.cc:801        |            1457 |              7 |
| COLUMN STATISTICS | sw2_std            | testchild         | id          |       139838363854160 | SHARED_READ         | STATEMENT     | GRANTED     | sql_base.cc:576    |            1457 |              7 |
| COLUMN STATISTICS | sw2_std            | testchild         | parentid    |       139838363893856 | SHARED_READ         | STATEMENT     | GRANTED     | sql_base.cc:576    |            1457 |              7 |
| SCHEMA            | sw2_std            | NULL              | NULL        |       139838363894304 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_table.cc:9643  |            1457 |              7 |
| TABLE             | sw2_std            | testparent        | NULL        |       139838364257584 | SHARED_UPGRADABLE   | STATEMENT     | GRANTED     | sql_table.cc:9635  |            1457 |              7 |
| TABLE             | sw2_std            | #sql-3e6_553      | NULL        |       139838364257680 | EXCLUSIVE           | STATEMENT     | GRANTED     | sql_table.cc:16507 |            1457 |              7 |
| FOREIGN KEY       | sw2_std            | testchild_ibfk_1  | NULL        |       139838364370480 | EXCLUSIVE           | STATEMENT     | GRANTED     | sql_table.cc:9712  |            1457 |              7 |
| COLUMN STATISTICS | sw2_std            | testchild         | id          |       139838364271952 | EXCLUSIVE           | TRANSACTION   | GRANTED     | histogram.cc:208   |            1457 |              8 |
| TABLE             | sw2_std            | testparent        | NULL        |       139838364388944 | EXCLUSIVE           | STATEMENT     | PENDING     | sql_table.cc:9635  |            1457 |              8 |
| TABLE             | performance_schema | metadata_locks    | NULL        |       139839576923440 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:5760  |            1458 |              5 |
+-------------------+--------------------+-------------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
15 rows in set (0.00 sec)

-- hangs, waiting for table metadata lock 

Now kill tx2 and tx3 (via Ctrl+C) but keep tx1, then re-run tx2 to create a foreign key to non-existing parent table - it works and doesn't require EXCLUSIVE lock on non-existing table:

tx2:
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> start transaction; set foreign_key_checks=0; select * from performance_schema.metadata_locks; alter table testchild add foreign key xfkchild (parentid) references nonexisting_testparent(id);  -- works ok
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | sw2_std            | testparent     | NULL        |       139837223042656 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:5760 |            1456 |             11 |
| TABLE       | performance_schema | metadata_locks | NULL        |       139839576923440 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:5760 |            1458 |              5 |
| TABLE       | performance_schema | metadata_locks | NULL        |       139838363980016 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:5760 |            1457 |             12 |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

mysql> select * from nonexisting_testparent;
ERROR 1146 (42S02): Table 'sw2_std.nonexisting_testparent' doesn't exist
[6 May 10:07] Serge Grachov
something like this in sql_table.cc  ?

1271a1272,1290
>     
>     /*  
>       #103575 workaround start
>       When FOREIGN_KEY_CHECKS=0 - don't lock parent table when specifically asked to override WL#6049 scenario
>       (by setting it - client guarantees that child table is in single-user mode and have no records)
>       WL#6049 scenario:
>       This is because we want to guarantee that there can be no
>       races between a DML statement, which analyzes foreign keys in which
>       the table serves as a parent during building of the prelocking set
>       and a DDL statement that adds/drops a foreign key referencing the
>       table being analyzed by the first statement
>      */
>     MDL_request *mdl_request;
> //    if (lock_type == MDL_EXCLUSIVE) {
>     if (lock_type == MDL_EXCLUSIVE && thd->variables.option_bits & OPTION_NO_FOREIGN_KEY_CHECKS) {
>       // ignore parent lock if foreign_key_checks=0
>     } else {    
>       mdl_request = new (thd->mem_root) MDL_request;
>       if (mdl_request == nullptr) return true;
1273,1277c1292,1293
<     MDL_request *mdl_request = new (thd->mem_root) MDL_request;
<     if (mdl_request == nullptr) return true;
< 
<     MDL_REQUEST_INIT(mdl_request, MDL_key::TABLE, buff_db, buff_table,
<                      lock_type, MDL_STATEMENT);
---
>       MDL_REQUEST_INIT(mdl_request, MDL_key::TABLE, buff_db, buff_table,
>                        lock_type, MDL_STATEMENT);
1279c1295,1297
<     mdl_requests->push_front(mdl_request);
---
>       mdl_requests->push_front(mdl_request);
>     }
>     // #103575 workaround end
9601,9602c9619,9636
<       MDL_request *mdl_request = new (thd->mem_root) MDL_request;
<       if (mdl_request == nullptr) return true;
---
>     /*  
>       #103575 workaround start
>       When FOREIGN_KEY_CHECKS=0 - don't lock parent table when specifically asked to override WL#6049 scenario
>       (by setting it - client guarantees that child table is in single-user mode and have no records)
>       WL#6049 scenario:
>       This is because we want to guarantee that there can be no
>       races between a DML statement, which analyzes foreign keys in which
>       the table serves as a parent during building of the prelocking set
>       and a DDL statement that adds/drops a foreign key referencing the
>       table being analyzed by the first statement
>      */
>       MDL_request *mdl_request;
> //      if (lock_type == MDL_EXCLUSIVE) {
>       if (lock_type == MDL_EXCLUSIVE && thd->variables.option_bits & OPTION_NO_FOREIGN_KEY_CHECKS) {
>         // ignore parent lock if foreign_key_checks=0
>       } else {
>         mdl_request = new (thd->mem_root) MDL_request;
>         if (mdl_request == nullptr) return true;
9604,9605c9638,9639
<       MDL_REQUEST_INIT(mdl_request, MDL_key::TABLE, fk->ref_db.str,
<                        fk->ref_table.str, lock_type, MDL_STATEMENT);
---
>         MDL_REQUEST_INIT(mdl_request, MDL_key::TABLE, fk->ref_db.str,
>                          fk->ref_table.str, lock_type, MDL_STATEMENT);
9607c9641,9643
<       mdl_requests->push_front(mdl_request);
---
>         mdl_requests->push_front(mdl_request);
>       }
>       // #103575 workaround end
12363,12364c12399,12416
<           MDL_request *mdl_request = new (thd->mem_root) MDL_request;
<           if (mdl_request == nullptr) return true;
---
>           /*  
>             #103575 workaround start
>             When FOREIGN_KEY_CHECKS=0 - don't lock parent table when specifically asked to override WL#6049 scenario
>             (by setting it - client guarantees that child table is in single-user mode and have no records)
>             WL#6049 scenario:
>             This is because we want to guarantee that there can be no
>             races between a DML statement, which analyzes foreign keys in which
>             the table serves as a parent during building of the prelocking set
>             and a DDL statement that adds/drops a foreign key referencing the
>             table being analyzed by the first statement
>           */
>           MDL_request *mdl_request;
> //          if (1 == 1) {
>           if (thd->variables.option_bits & OPTION_NO_FOREIGN_KEY_CHECKS) {
>             // ignore parent lock if foreign_key_checks=0
>           } else {    
>             mdl_request = new (thd->mem_root) MDL_request;
>             if (mdl_request == nullptr) return true;
12366,12367c12418,12419
<           MDL_REQUEST_INIT(mdl_request, MDL_key::TABLE, buff_db, buff_table,
<                            MDL_EXCLUSIVE, MDL_STATEMENT);
---
>             MDL_REQUEST_INIT(mdl_request, MDL_key::TABLE, buff_db, buff_table,
>                              MDL_EXCLUSIVE, MDL_STATEMENT);
12369c12421,12423
<           mdl_requests->push_front(mdl_request);
---
>             mdl_requests->push_front(mdl_request);
>           }
>           // #103575 workaround end
[18 May 13:12] MySQL Verification Team
Hi Mr. Grachov,

Thank you for your bug report.

What you describe is expected behaviour and parent metadata have to be locked even if FK checks are set to zero.

This is described in the chapter on InnoDB SE in both our Reference Manual and Internals Manual.

Not a bug.