1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
connect con1, localhost, root,,;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
connection default;
CREATE TABLE t1(c1 VARCHAR(10) NOT NULL, c2 VARCHAR(10) NOT NULL, c3 VARCHAR(10) NOT NULL);
INSERT INTO t1(c1, c2, c3) VALUES('A1','B1','IT1'), ('A2','B2','IT1'), ('A3','B3','IT1'), ('A4','B4','IT1'), ('A5','B5','IT1'), ('A6','B6','IT1'), ('A7','B7','IT1');
CREATE TABLE t2(c1 VARCHAR(10) NOT NULL, c2 VARCHAR(10) NOT NULL, c3 VARCHAR(10) NOT NULL);
INSERT INTO t2(c1, c2, c3) VALUES ('A3','B3','IT2'), ('A2','B2','IT2'), ('A4','B4','IT2'), ('A5','B5','II2');
CREATE TABLE result(id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, c1 VARCHAR(10) NOT NULL, c2 VARCHAR(10),
c3 VARCHAR(10), update_count INT DEFAULT 0, UNIQUE KEY uniq_idx (c1,c2), PRIMARY KEY (id)) ENGINE = innodb;
SET DEBUG_SYNC = "ha_write_row_end WAIT_FOR flushed EXECUTE 1";
INSERT INTO result(c1, c2, c3) SELECT * FROM t1 ON DUPLICATE KEY UPDATE c2=t1.c2, c3='UT1', update_count=update_count+1;
connection con1;
INSERT INTO result(c1, c2, c3) SELECT * FROM t2 ON DUPLICATE KEY UPDATE c2=t2.c2, c3='UT2', update_count=update_count+1;
SET DEBUG_SYNC = "now SIGNAL flushed";
connection default;
SELECT * FROM result;
id c1 c2 c3 update_count
1 A1 B1 IT1 0
2 A3 B3 UT1 1
3 A2 B2 UT1 1
4 A4 B4 UT1 1
5 A5 B5 UT1 1
9 A6 B6 IT1 0
10 A7 B7 IT1 0
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE result;
SET DEBUG_SYNC = "RESET";
|
