Bug #103575 | parent metadata locks for foreign keys on child table when foreign_key_checks=0 | ||
---|---|---|---|
Submitted: | 4 May 2021 17:52 | Modified: | 18 May 2021 13:12 |
Reporter: | Serge Grachov | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
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 2021 17:52]
Serge Grachov
[6 May 2021 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 2021 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.