Details
-
Bug
-
Status: In Review (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 13.2
Description
If reading the foreign keys that reference a table fails while a statement in a stored procedure opens its tables, every later execution of that statement runs without the referencing (child) tables in its prelocking set. No metadata lock is taken on the child tables, so DDL on a child table proceeds while a transaction that has modified the parent through the procedure is still open. This lasts until the procedure is reloaded into the stored procedure cache.
The failure is only reachable when the engine runs out of memory in get_parent_foreign_key_list(). The test case below injects it with the debug hook fk_list_fail from MDEV-41434, which raises ER_OUT_OF_RESOURCES right after that call. The table being modified has a trigger, which is what makes the half-built prelocking set stick.
How to repeat
--source include/have_innodb.inc
|
--source include/have_debug.inc
|
--source include/have_perfschema.inc
|
CREATE TABLE p (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; |
CREATE TABLE c (a INT, FOREIGN KEY (a) REFERENCES p(a) ON UPDATE CASCADE) ENGINE=InnoDB; |
CREATE TABLE t_log (a INT) ENGINE=InnoDB; |
CREATE TRIGGER p_bu BEFORE UPDATE ON p FOR EACH ROW INSERT INTO t_log VALUES (OLD.a); |
INSERT INTO p VALUES (1, 10), (2, 20); |
INSERT INTO c VALUES (1), (2); |
CREATE PROCEDURE sp1(x INT) UPDATE p SET b = x WHERE a = 1; |
CREATE PROCEDURE sp2(x INT) UPDATE p SET b = x WHERE a = 2; |
|
|
--echo # sp1 never failed
|
BEGIN; |
CALL sp1(11);
|
SELECT object_name, lock_type FROM performance_schema.metadata_locks |
WHERE object_type = 'TABLE' AND object_schema = 'test' ORDER BY object_name; |
COMMIT; |
|
|
--echo # sp2 failed once
|
SET debug_dbug= '+d,fk_list_fail'; |
--error ER_OUT_OF_RESOURCES
|
CALL sp2(21);
|
SET debug_dbug= ''; |
BEGIN; |
CALL sp2(22);
|
SELECT object_name, lock_type FROM performance_schema.metadata_locks |
WHERE object_type = 'TABLE' AND object_schema = 'test' ORDER BY object_name; |
COMMIT; |
|
|
--echo # DDL on the child while each procedure's transaction is open
|
BEGIN; |
CALL sp1(12);
|
connect con1, localhost, root; |
SET lock_wait_timeout= 1; |
--error ER_LOCK_WAIT_TIMEOUT
|
ALTER TABLE c COMMENT 'x1'; |
connection default;
|
COMMIT;
|
BEGIN;
|
CALL sp2(23);
|
connection con1;
|
ALTER TABLE c COMMENT 'x2'; |
disconnect con1;
|
connection default; |
COMMIT; |
DROP PROCEDURE sp1; |
DROP PROCEDURE sp2; |
DROP TABLE t_log, c, p; |
# sp1 never failed
|
...
|
object_name lock_type
|
c SHARED_WRITE
|
p SHARED_WRITE
|
t_log SHARED_WRITE
|
...
|
# sp2 failed once
|
...
|
object_name lock_type
|
p SHARED_WRITE
|
t_log SHARED_WRITE
|
t_log SHARED
|
...
|
# DDL on the child while each procedure's transaction is open
|
...
|
ALTER TABLE c COMMENT 'x1';
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
...
|
ALTER TABLE c COMMENT 'x2';
|
...
|
Expected: sp2 takes the same locks as sp1, including SHARED_WRITE on c, and ALTER TABLE c waits for its transaction.
Actual: every CALL sp2 after the failed one takes no lock on c, and ALTER TABLE c completes while the transaction is open.
Root cause
DML_prelocking_strategy::handle_table() in sql/sql_base.cc sets *need_prelocking= TRUE and adds the tables of the table's triggers before it reads the foreign keys:
if (table_list->trg_event_map && trigger_prelocking_needed) |
{
|
if (table->triggers) |
{
|
*need_prelocking= TRUE;
|
|
|
if (table->triggers-> |
add_tables_and_routines_for_triggers(thd, prelocking_ctx, table_list))
|
return TRUE; |
}
|
|
|
if (prepare_fk_prelocking_list(thd, prelocking_ctx, table_list, |
need_prelocking,
|
table_list->trg_event_map))
|
return TRUE; |
}
|
When prepare_fk_prelocking_list() fails, extend_table_list() still marks the statement as having a prelocking set, since it does not look at the result of handle_table():
error= prelocking_strategy->handle_table(thd, lex, tables,
|
&need_prelocking);
|
|
|
if (need_prelocking && ! lex->requires_prelocking()) |
lex->mark_as_requiring_prelocking(save_query_tables_last);
|
For a statement in a stored procedure, sp_lex_keeper::reset_lex_and_exec_core() saves that tail of the table list after the execution and attaches it again on every later execution (sql/sp_head.cc on 10.11, sql/sp_instr.cc from 11.4 on):
if (m_lex->query_tables_own_last) |
{
|
...
|
lex_query_tables_own_last= m_lex->query_tables_own_last;
|
prelocking_tables= *lex_query_tables_own_last;
|
*lex_query_tables_own_last= NULL;
|
open_tables() then passes has_prelocking_list to extend_table_list(), which skips the prelocking strategy for a statement that already has a prelocking set. handle_table() is not called again, and the child tables are never added.
The code is the same on 10.11, 11.4, 11.8, 12.3, 13.1 and main.
Attachments
Issue Links
- relates to
-
-
- In Review
-
