mirror of
https://github.com/MariaDB/server.git
synced 2026-02-13 14:18:41 +01:00
d5db6c1daa
Issue: On galera write node INSERT statements does not acquire MDL locks on it's all child tables and thereby wsrep certification keys are also added for limited tables, but on applier nodes it does acquire MDL locks for all child tables. This can result into MDL BF-BF conflict on applier node when transactions referring to parent and child tables are executed concurrently. For example: Tables with foreign keys: t1<-t2<-t3<-t4 Conflicting transactions: INSERT t1 and DROP TABLE t4 Wsrep certification keys taken on write node: - for INSERT t1: t1 and t2 - for DROP TABLE t4: t4 On applier node MDL BF-BF conflict happened between two transaction because MDL locks on t1, t2, t3 and t4 were taken for INSERT t1, which conflicted with MDL lock on t4 taken by DROP TABLE t4. The Wsrep certification keys helps in resolving this MDL BF-BF conflict by prioritizing and scheduling concurrent transactions. But to generate Wsrep certification keys it needs to open and take MDL locks on all the child tables. On applier nodes Write_rows event is implicitly a REPLACE, deleting all conflicting rows which can cause cascading FK actions and locks on foreign key children tables. Solution: For Galera applier nodes the Write_rows event is considered pure INSERT which will never cause cascading FK actions and locks on foreign key children tables.
109 lines
1.8 KiB
Text
109 lines
1.8 KiB
Text
#
|
|
# MDEV-38216 was failing after MDEV-25039 changes.
|
|
#
|
|
|
|
--source include/galera_cluster.inc
|
|
--source include/have_innodb.inc
|
|
--source include/have_debug.inc
|
|
--source include/have_debug_sync.inc
|
|
--source include/have_perfschema.inc
|
|
|
|
--echo #
|
|
--echo # 1. LOCK TABLE and INSERT
|
|
--echo #
|
|
|
|
#
|
|
# Setup
|
|
#
|
|
--connection node_1
|
|
CREATE TABLE t1 (
|
|
id varchar(100),
|
|
PRIMARY KEY (id)) engine=innodb;
|
|
|
|
CREATE TABLE t2 (
|
|
id varchar(100) , a varchar(100),
|
|
PRIMARY KEY (id,a),
|
|
KEY a (a),
|
|
CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (id)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE t3 (
|
|
a varchar(100),
|
|
b varchar(100) ,
|
|
PRIMARY KEY (a, b),
|
|
CONSTRAINT FOREIGN KEY (a) REFERENCES t2 (a)
|
|
) ENGINE=InnoDB;
|
|
|
|
LOCK TABLES t1 WRITE;
|
|
INSERT INTO t1 VALUES ('a');
|
|
UNLOCK TABLES;
|
|
|
|
|
|
#
|
|
# Verify insert has succeded.
|
|
#
|
|
--connection node_2
|
|
--let $wait_condition = SELECT COUNT(*) = 1 FROM test.t1
|
|
--source include/wait_condition.inc
|
|
|
|
select * from t1;
|
|
|
|
--connection node_1
|
|
select * from t1;
|
|
|
|
|
|
#
|
|
# Cleanup
|
|
#
|
|
DROP TABLE t3, t2, t1;
|
|
|
|
|
|
--echo #
|
|
--echo # 2. FLUSH TABLES WITH READ LOCK and INSERT
|
|
--echo #
|
|
|
|
#
|
|
# Setup
|
|
#
|
|
--connection node_1
|
|
CREATE TABLE t1 (
|
|
id varchar(100),
|
|
PRIMARY KEY (id)) engine=innodb;
|
|
|
|
CREATE TABLE t2 (
|
|
id varchar(100) , a varchar(100),
|
|
PRIMARY KEY (id,a),
|
|
KEY a (a),
|
|
CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (id)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE t3 (
|
|
a varchar(100),
|
|
b varchar(100) ,
|
|
PRIMARY KEY (a, b),
|
|
CONSTRAINT FOREIGN KEY (a) REFERENCES t2 (a)
|
|
) ENGINE=InnoDB;
|
|
|
|
FLUSH TABLES WITH READ LOCK;
|
|
--error ER_CANT_UPDATE_WITH_READLOCK
|
|
INSERT INTO t1 VALUES ('a');
|
|
UNLOCK TABLES;
|
|
|
|
|
|
#
|
|
# Verify insert has succeded.
|
|
#
|
|
--connection node_2
|
|
--let $wait_condition = SELECT COUNT(*) = 0 FROM test.t1
|
|
--source include/wait_condition.inc
|
|
|
|
select * from t1;
|
|
|
|
--connection node_1
|
|
select * from t1;
|
|
|
|
|
|
#
|
|
# Cleanup
|
|
#
|
|
DROP TABLE t3, t2, t1;
|