mirror of
https://github.com/MariaDB/server.git
synced 2025-02-15 18:05:32 +01:00
e333d82964
Comment from Codership:- To fix the problem, we changed the certification logic in galera to treat insert on child table row as exclusive to prevent any operation on referenced parent table row. At the same time, update and delete on child table row were demoted to "shared", which makes it possible to update/delete referenced parent table row, but only in a later transaction. This change allows somewhat more concurrency for foreign key constrained transactions, but is still safe for correct certification end result.
155 lines
5 KiB
Text
155 lines
5 KiB
Text
CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
|
|
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
|
|
CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1)) ;
|
|
INSERT INTO p VALUES (1, 0);
|
|
INSERT INTO p VALUES (2, 0);
|
|
SET AUTOCOMMIT=ON;
|
|
START TRANSACTION;
|
|
DELETE FROM p WHERE f1 = 1;
|
|
SET SESSION wsrep_sync_wait = 0;
|
|
SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
|
|
INSERT INTO c VALUES (1, 1);
|
|
SET SESSION wsrep_on = 0;
|
|
SET SESSION wsrep_on = 1;
|
|
SET GLOBAL wsrep_provider_options = 'dbug=';
|
|
SET GLOBAL wsrep_provider_options = 'dbug=d,local_monitor_enter_sync';
|
|
COMMIT;
|
|
SET SESSION wsrep_on = 0;
|
|
SET SESSION wsrep_on = 1;
|
|
SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
|
|
SET GLOBAL wsrep_provider_options = 'signal=local_monitor_enter_sync';
|
|
SET GLOBAL wsrep_provider_options = 'dbug=';
|
|
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
|
|
SELECT * FROM p;
|
|
f1 f2
|
|
1 0
|
|
2 0
|
|
SELECT * FROM c;
|
|
f1 p_id
|
|
1 1
|
|
DROP TABLE c;
|
|
DROP TABLE p;
|
|
CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
|
|
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
|
|
f2 INTEGER,
|
|
CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1)) ;
|
|
INSERT INTO p VALUES (1, 0);
|
|
INSERT INTO p VALUES (2, 0);
|
|
INSERT INTO c VALUES (1, 1, 0);
|
|
SET AUTOCOMMIT=ON;
|
|
START TRANSACTION;
|
|
UPDATE p SET f2 = 1 WHERE f1 = 1;
|
|
SET SESSION wsrep_sync_wait = 0;
|
|
SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
|
|
UPDATE c SET f2 = 1 WHERE f1 = 1;
|
|
SET SESSION wsrep_on = 0;
|
|
SET SESSION wsrep_on = 1;
|
|
SET GLOBAL wsrep_provider_options = 'dbug=';
|
|
SET GLOBAL wsrep_provider_options = 'dbug=d,local_monitor_enter_sync';
|
|
COMMIT;
|
|
SET SESSION wsrep_on = 0;
|
|
SET SESSION wsrep_on = 1;
|
|
SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
|
|
SET GLOBAL wsrep_provider_options = 'signal=local_monitor_enter_sync';
|
|
SET GLOBAL wsrep_provider_options = 'dbug=';
|
|
SELECT * FROM p;
|
|
f1 f2
|
|
1 1
|
|
2 0
|
|
SELECT * FROM c;
|
|
f1 p_id f2
|
|
1 1 1
|
|
DROP TABLE c;
|
|
DROP TABLE p;
|
|
CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
|
|
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
|
|
CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1)) ;
|
|
INSERT INTO p VALUES (1, 0);
|
|
INSERT INTO p VALUES (2, 0);
|
|
INSERT INTO c VALUES (1, 1);
|
|
SET AUTOCOMMIT=ON;
|
|
START TRANSACTION;
|
|
UPDATE p SET f2 = 1 WHERE f1 = 1;
|
|
SET SESSION wsrep_sync_wait = 0;
|
|
SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
|
|
DELETE FROM c WHERE f1 = 1;
|
|
SET SESSION wsrep_on = 0;
|
|
SET SESSION wsrep_on = 1;
|
|
SET GLOBAL wsrep_provider_options = 'dbug=';
|
|
SET GLOBAL wsrep_provider_options = 'dbug=d,local_monitor_enter_sync';
|
|
COMMIT;
|
|
SET SESSION wsrep_on = 0;
|
|
SET SESSION wsrep_on = 1;
|
|
SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
|
|
SET GLOBAL wsrep_provider_options = 'signal=local_monitor_enter_sync';
|
|
SET GLOBAL wsrep_provider_options = 'dbug=';
|
|
SELECT * FROM p;
|
|
f1 f2
|
|
1 1
|
|
2 0
|
|
SELECT * FROM c;
|
|
f1 p_id
|
|
DROP TABLE c;
|
|
DROP TABLE p;
|
|
CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER UNIQUE KEY) ENGINE=INNODB;
|
|
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER,
|
|
CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f2)) ;
|
|
INSERT INTO p VALUES (1, 0);
|
|
SET AUTOCOMMIT=ON;
|
|
START TRANSACTION;
|
|
UPDATE p SET f2 = 1 WHERE f1 = 1;
|
|
SET SESSION wsrep_sync_wait = 0;
|
|
SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
|
|
INSERT INTO c VALUES (1, 0);;
|
|
SET SESSION wsrep_on = 0;
|
|
SET SESSION wsrep_on = 1;
|
|
SET GLOBAL wsrep_provider_options = 'dbug=';
|
|
SET GLOBAL wsrep_provider_options = 'dbug=d,local_monitor_enter_sync';
|
|
COMMIT;
|
|
SET SESSION wsrep_on = 0;
|
|
SET SESSION wsrep_on = 1;
|
|
SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
|
|
SET GLOBAL wsrep_provider_options = 'signal=local_monitor_enter_sync';
|
|
SET GLOBAL wsrep_provider_options = 'dbug=';
|
|
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
|
|
SELECT * FROM p;
|
|
f1 f2
|
|
1 0
|
|
SELECT * FROM c;
|
|
f1 p_id
|
|
1 0
|
|
DROP TABLE c;
|
|
DROP TABLE p;
|
|
CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB;
|
|
CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER, f2 INTEGER,
|
|
CONSTRAINT fk_1 FOREIGN KEY (p_id) REFERENCES p (f1)
|
|
ON DELETE CASCADE) ;
|
|
INSERT INTO p VALUES (1, 0);
|
|
INSERT INTO p VALUES (2, 0);
|
|
INSERT INTO c VALUES (1, 1, 0);
|
|
SET AUTOCOMMIT=ON;
|
|
START TRANSACTION;
|
|
DELETE FROM p WHERE f1 = 1;
|
|
SET SESSION wsrep_sync_wait = 0;
|
|
SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync';
|
|
UPDATE c SET f2 = 1 WHERE f1 = 1;
|
|
SET SESSION wsrep_on = 0;
|
|
SET SESSION wsrep_on = 1;
|
|
SET GLOBAL wsrep_provider_options = 'dbug=';
|
|
SET GLOBAL wsrep_provider_options = 'dbug=d,local_monitor_enter_sync';
|
|
COMMIT;
|
|
SET SESSION wsrep_on = 0;
|
|
SET SESSION wsrep_on = 1;
|
|
SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync';
|
|
SET GLOBAL wsrep_provider_options = 'signal=local_monitor_enter_sync';
|
|
SET GLOBAL wsrep_provider_options = 'dbug=';
|
|
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
|
|
SELECT * FROM p;
|
|
f1 f2
|
|
1 0
|
|
2 0
|
|
SELECT * FROM c;
|
|
f1 p_id f2
|
|
1 1 1
|
|
DROP TABLE c;
|
|
DROP TABLE p;
|