InnoDB should clean n_foreign_key_checks usage and assume that SQL is already holding
appropriate MDL. But an Insert into the child table doesn't seem to acquire MDL on the parent. When vice-versa is happening that there is MDL on the child when insert is happening
for the parent table.
The following test case could repeat the issue:
--source include/have_innodb.inc
create table t1(f1 int not null, primary key(f1))engine=innodb;
create table t2(f1 int not null primary key,
f2 int not null, foreign key(f2) references t1(f1))engine=innodb;
set DEBUG_SYNC='ib_after_row_insert_step SIGNAL alter WAIT_FOR dml';
--send insert into t1 values(1)
connect(con1,localhost,root,,,);
set DEBUG_SYNC="now WAIT_FOR alter";
alter table t2 force;
set DEBUG_SYNC="now SIGNAL dml";
connection default;
reap;
drop table t2, t1;
disconnect con1;
In above test case, ALTER TABLE t2 FORCE waits for MDL to open the table.
Another test case:
--source include/have_innodb.inc
create table t1(f1 int not null, primary key(f1))engine=innodb;
create table t2(f1 int not null primary key,
f2 int not null, foreign key(f2) references t1(f1))engine=innodb;
set DEBUG_SYNC='ib_after_row_insert_step SIGNAL alter WAIT_FOR dml';
--send insert into t2 values(1, 2)
connect(con1,localhost,root,,,);
set DEBUG_SYNC="now WAIT_FOR alter";
alter table t1 force;
set DEBUG_SYNC="now SIGNAL dml";
connection default;
reap;
drop table t2, t1;
disconnect con1;
In this case, alter waits at commit_inplace_alter_table(). That too for innodb table lock.
#6 0x00005555565df591 in lock_wait_suspend_thread (thr=0x7fffb8030768)
at /home/thiru/mariarepo/10.5/10.5-work/storage/innobase/lock/lock0wait.cc:351
#7 0x00005555566bb378 in row_mysql_handle_errors (new_err=0x7ffff01c3844,
Related to this, I believe that an UPDATE or DELETE (or REPLACE or INSERT…ON DUPLICATE KEY UPDATE) on a parent table may require accessing a child table, to reject or to cascade the operation. Furthermore, I believe that any modification of the child table may require accessing the parent table.
Marko Mäkelä
added a comment - Related to this, I believe that an UPDATE or DELETE (or REPLACE or INSERT…ON DUPLICATE KEY UPDATE ) on a parent table may require accessing a child table, to reject or to cascade the operation. Furthermore, I believe that any modification of the child table may require accessing the parent table.
I think that we should aim for replacing InnoDB table locks with MDL.
I believe that this problem would have to be resolved also for implementing FOREIGN KEY support in the SQL layer (MDEV-12483).
Marko Mäkelä
added a comment - I think that we should aim for replacing InnoDB table locks with MDL.
I believe that this problem would have to be resolved also for implementing FOREIGN KEY support in the SQL layer ( MDEV-12483 ).
As part of this, some code from row_ins_check_foreign_constraints() should be removed. This should greatly speed up large INSERT operations or ALTER TABLE on tables that are connected by FOREIGN KEY:
dict_table_t* referenced_table
= foreign->referenced_table;
if (referenced_table == NULL) {
ref_table = dict_table_open_on_name(
foreign->referenced_table_name_lookup,
FALSE, FALSE, DICT_ERR_IGNORE_NONE);
}
if (0 == trx->dict_operation_lock_mode) {
got_s_lock = TRUE;
row_mysql_freeze_data_dictionary(trx);
}
if (referenced_table) {
foreign->foreign_table->inc_fk_checks();
}
Slightly related note: InnoDB used to break its own transactional table locks until a follow-up fix to MDEV-13407.
Marko Mäkelä
added a comment - As part of this, some code from row_ins_check_foreign_constraints() should be removed. This should greatly speed up large INSERT operations or ALTER TABLE on tables that are connected by FOREIGN KEY :
dict_table_t* referenced_table
= foreign->referenced_table;
if (referenced_table == NULL) {
ref_table = dict_table_open_on_name(
foreign->referenced_table_name_lookup,
FALSE, FALSE, DICT_ERR_IGNORE_NONE);
}
if (0 == trx->dict_operation_lock_mode) {
got_s_lock = TRUE;
row_mysql_freeze_data_dictionary(trx);
}
if (referenced_table) {
foreign->foreign_table->inc_fk_checks();
}
Slightly related note: InnoDB used to break its own transactional table locks until a follow-up fix to MDEV-13407 .
It looks like fixing the prelocking code would allow us to remove dict_operation_lock (which in 10.5 was renamed to dict_sys.latch) altogether. The S-latch is currently only used in two cases in 10.5:
in some ALTER TABLE and FOREIGN KEY handling code, to work around the missing MDL
on ROLLBACK of recovered transactions.
Fixing the ROLLBACK might be a little tricky, because for normal non-recovered transactions we should already be covered by MDL. (MDEV-16678 fixed purge of history to use MDL). If it is OK to attempt to acquire MDL while already holding a compatible MDL, then also that should be trivial to fix.
Marko Mäkelä
added a comment - It looks like fixing the prelocking code would allow us to remove dict_operation_lock (which in 10.5 was renamed to dict_sys.latch ) altogether. The S-latch is currently only used in two cases in 10.5:
in some ALTER TABLE and FOREIGN KEY handling code, to work around the missing MDL
on ROLLBACK of recovered transactions.
Fixing the ROLLBACK might be a little tricky, because for normal non-recovered transactions we should already be covered by MDL. ( MDEV-16678 fixed purge of history to use MDL). If it is OK to attempt to acquire MDL while already holding a compatible MDL, then also that should be trivial to fix.
marko, I cannot fix prelocking blindly. Can you please add some assert to InnoDB that would fire unless prelocking works correctly? In other words, when assert is always true, you can remove dict_table_t::n_foreign_key_checks_running.
After adding the assert, please reassign back to me.
Sergei Golubchik
added a comment - marko , I cannot fix prelocking blindly. Can you please add some assert to InnoDB that would fire unless prelocking works correctly? In other words, when assert is always true, you can remove dict_table_t::n_foreign_key_checks_running.
After adding the assert, please reassign back to me.
serg, I think that I will remove the InnoDB work-arounds and add an assertion that should catch the problems in row_drop_table_for_mysql(), so that you can fix the upper layer. This will likely also require fixing MDEV-21602 (make the error handling of CREATE…SELECT invoke ROLLBACK before invoking handler::delete_table).
For TRUNCATE TABLE we might have to retain row_mysql_drop_list, but we will see about that (if we ever want to address MDEV-16306).
Marko Mäkelä
added a comment - serg , I think that I will remove the InnoDB work-arounds and add an assertion that should catch the problems in row_drop_table_for_mysql() , so that you can fix the upper layer. This will likely also require fixing MDEV-21602 (make the error handling of CREATE…SELECT invoke ROLLBACK before invoking handler::delete_table ).
For TRUNCATE TABLE we might have to retain row_mysql_drop_list , but we will see about that (if we ever want to address MDEV-16306 ).
If the tables are sufficiently protected by MDL, there is no need to hold the data dictionary latch to prevent concurrent DDL operations. At the start of this function, my fix already removed an assertion that we must be holding the latch.
There is no public test (not even in MySQL) that would exercise the DEBUG_SYNC point.
Marko Mäkelä
added a comment - In my prototype, I forgot to remove some more code, but strangely no tests were failing due to that:
diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc
--- a/storage/innobase/row/row0ins.cc
+++ b/storage/innobase/row/row0ins.cc
@@ -1395,16 +1395,6 @@ row_ins_foreign_check_on_constraint(
err = row_update_cascade_for_mysql(thr, cascade,
foreign->foreign_table);
- /* Release the data dictionary latch for a while, so that we do not
- starve other threads from doing CREATE TABLE etc. if we have a huge
- cascaded operation running. */
-
- row_mysql_unfreeze_data_dictionary(thr_get_trx(thr));
-
- DEBUG_SYNC_C("innodb_dml_cascade_dict_unfreeze");
-
- row_mysql_freeze_data_dictionary(thr_get_trx(thr));
-
mtr_start(mtr);
/* Restore pcur position */
If the tables are sufficiently protected by MDL, there is no need to hold the data dictionary latch to prevent concurrent DDL operations. At the start of this function, my fix already removed an assertion that we must be holding the latch.
There is no public test (not even in MySQL) that would exercise the DEBUG_SYNC point.
marko, I don't understand. Tests that you've disabled and that crash in your prototype branch are tests like
CREATETABLE t1 (a int, b int) engine=myisam;
INSERTINTO t1 values (1,1),(1,2);
--error ER_DUP_ENTRY
CREATETABLE t2 (primarykey (a)) engine=innodb select * from t1;
How is that related to the problem at hand, to MDL or foreign key referenced tables?
And the test from the bug description, that shows that an fk-referenced table does not have a proper MDL, this does does not crash in your prototype branch.
Sergei Golubchik
added a comment - - edited marko , I don't understand. Tests that you've disabled and that crash in your prototype branch are tests like
CREATE TABLE t1 (a int , b int ) engine=myisam;
INSERT INTO t1 values (1,1),(1,2);
--error ER_DUP_ENTRY
CREATE TABLE t2 ( primary key (a)) engine=innodb select * from t1;
How is that related to the problem at hand, to MDL or foreign key referenced tables?
And the test from the bug description, that shows that an fk-referenced table does not have a proper MDL, this does does not crash in your prototype branch.
MDEV-21602 CREATE TABLE…PRIMARY KEY…SELECT workaround
causes DROP TABLE to ignore locks
The error handling of CREATE…SELECT would invoke
handler::delete_table() while still holding locks
on the table, due to not having invoked handlerton::rollback
first. InnoDB used to work around this as well.
In MDEV-742 this was worked around further by breaking
MDL, causing MDEV-22733.
Unfortunately, thiru did not include enough details to reproduce the originally reported problem. At least, we would have to know the source code revision. Based on the filing date, I would guess that it is somewhere around 5a00792c69065140a34315e3c5aec00bdbef8447.
Marko Mäkelä
added a comment - Related to what I wrote earlier, MDEV-23484 removed unnecessary operations on dict_operation_lock on ROLLBACK .
serg , my earlier comment in this ticket tried to explain that also MDEV-21602 is breaking some rules. Did you read the commit message that it linked to?
MDEV-21602 CREATE TABLE…PRIMARY KEY…SELECT workaround
causes DROP TABLE to ignore locks
The error handling of CREATE…SELECT would invoke
handler::delete_table() while still holding locks
on the table, due to not having invoked handlerton::rollback
first. InnoDB used to work around this as well.
In MDEV-742 this was worked around further by breaking
MDL, causing MDEV-22733 .
Unfortunately, thiru did not include enough details to reproduce the originally reported problem. At least, we would have to know the source code revision. Based on the filing date, I would guess that it is somewhere around 5a00792c69065140a34315e3c5aec00bdbef8447.
Bug
Major
MDEV-12483 Add foreign keys support for partitioned tables
MDEV-20612 Improve InnoDB lock_sys scalability
Related to this, I believe that an UPDATE or DELETE (or REPLACE or INSERT…ON DUPLICATE KEY UPDATE) on a parent table may require accessing a child table, to reject or to cascade the operation. Furthermore, I believe that any modification of the child table may require accessing the parent table.