1set global transaction isolation level repeatable read; 2CREATE TABLE t1( 3id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 4k INT, 5c CHAR(1), 6UNIQUE KEY(k)) ENGINE=InnoDB; 7SHOW CREATE TABLE t1; 8Table Create Table 9t1 CREATE TABLE `t1` ( 10 `id` int(11) NOT NULL AUTO_INCREMENT, 11 `k` int(11) DEFAULT NULL, 12 `c` char(1) DEFAULT NULL, 13 PRIMARY KEY (`id`), 14 UNIQUE KEY `k` (`k`) 15) ENGINE=InnoDB DEFAULT CHARSET=latin1 16# 17# Sequential execution 18# 19INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1'; 20affected rows: 3 21info: Records: 3 Duplicates: 0 Warnings: 0 22# 23# 1 duplicate 24# 25INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2'; 26affected rows: 4 27info: Records: 3 Duplicates: 1 Warnings: 0 28# 29# 5 rows, consecutive auto_inc values 30# 31SELECT * FROM t1 order by k; 32id k c 331 1 NULL 342 2 2 353 3 NULL 364 4 NULL 375 5 NULL 38affected rows: 5 39DROP TABLE t1; 40affected rows: 0 41CREATE TABLE t1( 42id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 43k INT, 44c CHAR(1), 45UNIQUE KEY(k)) ENGINE=InnoDB; 46affected rows: 0 47# 48# Sequential execution 2 49# 50INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2'; 51affected rows: 3 52info: Records: 3 Duplicates: 0 Warnings: 0 53# 54# 1 duplicate 55# 56INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1'; 57affected rows: 4 58info: Records: 3 Duplicates: 1 Warnings: 0 59# 60# 5 rows, consecutive auto_inc values 61# 62SELECT * FROM t1 order by k; 63id k c 644 1 NULL 651 2 1 665 3 NULL 672 4 NULL 683 5 NULL 69affected rows: 5 70DROP TABLE t1; 71affected rows: 0 72CREATE TABLE t1( 73id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 74k INT, 75c CHAR(1), 76UNIQUE KEY(k)) ENGINE=InnoDB; 77affected rows: 0 78# 79# Parallel execution 80# 81connect con2, localhost, root; 82SET DEBUG_SYNC='now WAIT_FOR write_row_done'; 83connect con1, localhost, root; 84SET DEBUG_SYNC='ha_write_row_end SIGNAL write_row_done WAIT_FOR continue'; 85affected rows: 0 86INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1'; 87connection con2; 88affected rows: 0 89SET DEBUG_SYNC='execute_command_after_close_tables SIGNAL continue'; 90affected rows: 0 91INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2'; 92affected rows: 3 93info: Records: 3 Duplicates: 0 Warnings: 0 94connection con1; 95# 96# 2 duplicates 97# 98affected rows: 4 99info: Records: 3 Duplicates: 1 Warnings: 0 100connection default; 101# 102# 3 rows 103# 104SELECT * FROM t1 order by k; 105id k c 1061 1 NULL 1074 2 1 1082 3 NULL 1095 4 NULL 1106 5 NULL 111affected rows: 5 112INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2'; 113affected rows: 6 114info: Records: 3 Duplicates: 3 Warnings: 0 115SELECT * FROM t1 order by k; 116id k c 1171 1 NULL 1184 2 2 1192 3 NULL 1205 4 2 1216 5 2 122affected rows: 5 123disconnect con1; 124disconnect con2; 125connection default; 126DROP TABLE t1; 127# 128# Parallel test with read_committed 129# 130set global transaction isolation level read committed; 131drop table if exists t1; 132CREATE TABLE t1( 133id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 134k INT, 135c CHAR(1), 136UNIQUE KEY(k)) ENGINE=InnoDB; 137connect con1, localhost, root; 138SET DEBUG_SYNC='ha_write_row_end SIGNAL continue2 WAIT_FOR continue1'; 139affected rows: 0 140INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1'; 141connect con2, localhost, root; 142SET DEBUG_SYNC='ha_write_row_start WAIT_FOR continue2'; 143affected rows: 0 144SET DEBUG_SYNC='after_mysql_insert SIGNAL continue1'; 145affected rows: 0 146INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2'; 147affected rows: 3 148info: Records: 3 Duplicates: 0 Warnings: 0 149disconnect con2; 150connection con1; 151affected rows: 4 152info: Records: 3 Duplicates: 1 Warnings: 0 153SET DEBUG_SYNC='RESET'; 154# 155# 5 rows, gap in autoinc values 156# 157SELECT * FROM t1 ORDER BY k; 158id k c 1591 1 NULL 1604 2 1 1612 3 NULL 1625 4 NULL 1636 5 NULL 164disconnect con1; 165connection default; 166DROP TABLE t1; 167set global transaction isolation level repeatable read; 168