connection node_2; connection node_1; # # 1. LOCK TABLE and INSERT # 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; connection node_2; select * from t1; id a connection node_1; select * from t1; id a DROP TABLE t3, t2, t1; # # 2. FLUSH TABLES WITH READ LOCK and INSERT # 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; INSERT INTO t1 VALUES ('a'); ERROR HY000: Can't execute the query because you have a conflicting read lock UNLOCK TABLES; connection node_2; select * from t1; id connection node_1; select * from t1; id DROP TABLE t3, t2, t1;