| Bug #111703 | INSERT ON DUPLICATE KEY locks INSERT if table have many PRIMARY and UNIQUE key | ||
|---|---|---|---|
| Submitted: | 10 Jul 2023 6:40 | Modified: | 15 Jul 2023 1:58 |
| Reporter: | Aristotle Po | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 8.0.33, 5.7.42 | OS: | Any |
| Assigned to: | Jon Stephens | CPU Architecture: | Any |
| Tags: | ON DUPLICATE KEY UPDATE | ||
[10 Jul 2023 7:15]
Aristotle Po
I also got similar result with INSERT ... ON DUPLICATE KEY UPDATE on Session 2
######################
# Session 1
######################
mysql [localhost:5742] {msandbox} (test) > BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5742] {msandbox} (test) > INSERT INTO t1(a, b) values(1, 'test1') ON DUPLICATE KEY UPDATE b = VALUES(b);
Query OK, 0 rows affected (0.00 sec)
######################
# Session 2
######################
mysql [localhost:5742] {msandbox} (test) > INSERT INTO t1(a, b) values(3, 'test3') ON DUPLICATE KEY UPDATE a = VALUES(a), b= VALUES(b);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[10 Jul 2023 7:43]
Aristotle Po
Further testing shows the locking happens for both existing and non existing records : ############## # Session 1 ############## t1> BEGIN; Query OK, 0 rows affected (0.00 sec) t1> INSERT INTO t1(a, b) values(1, 'test1') ON DUPLICATE KEY UPDATE b = VALUES(b); Query OK, 0 rows affected (0.00 sec) t1> SELECT * FROM t1; +---+---+-------+ | i | a | b | +---+---+-------+ | 1 | 1 | test1 | | 2 | 2 | test2 | +---+---+-------+ 2 rows in set (0.01 sec) ############## # Session 2 ############## t2> INSERT INTO t1(a, b) values(3, 'test3') ON DUPLICATE KEY UPDATE a = VALUES(a), b= VALUES(b); -- non existing ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction t2> INSERT INTO t1(a, b) values(2, 'test2') ON DUPLICATE KEY UPDATE a = VALUES(a), b= VALUES(b); -- already existing ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[10 Jul 2023 7:47]
MySQL Verification Team
Hello Aristotle Po, Thank you for the report and feedback. regards, Umesh
[15 Jul 2023 1:58]
Jon Stephens
Hi! I'm sorry, but this is not a bug. Reason: We already indicate in the Manual that you should not use ON DUPLICATE KEY UPDATE with tables having multiple unique keys. (Note that primary keys are by definition also unique keys.) We provide some reasons for this. It's not an exhaustive list, nor does it need to be; we've established that it's a Bad Thing to be avoided. I don't mean to sound harsh, but when you go directly against advice given in the Manual, I don't think we can't really be held responsible for what happens next, and I don't see how enshrining it in our documentation provides any benefit to users. Thanks and regards, jon.
Description: INSERT ON DUPLICATE KEY locks an INSERT of non existent record in another session when a table has multiple PRIMARY and UNIQUE key. Server version: 8.0.33 MySQL Community Server - GPL Server version: 5.7.42 MySQL Community Server (GPL) ###################### # Session 1 ###################### t1> BEGIN; Query OK, 0 rows affected (0.00 sec) t1> INSERT INTO t1(a, b) values(1, 'test1') ON DUPLICATE KEY UPDATE b = VALUES(b); Query OK, 0 rows affected, 1 warning (0.00 sec) ###################### # Session 2 ###################### t2> INSERT INTO t1(a, b) values(3, 'test3'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction How to repeat: ############################################ Summary of steps : ############################################ ###################### # 1) create the table with multiple PK/UK ###################### CREATE TABLE t1(i int NOT NULL AUTO_INCREMENT primary key, a int NOT NULL, b varchar(9) DEFAULT NULL, UNIQUE KEY uk1(a, b)); -- OR -- CREATE TABLE t1(i int NOT NULL AUTO_INCREMENT primary key, a int NOT NULL, b varchar(9) NOT NULL, UNIQUE KEY uk1(a, b)); -- make the UK to another PK my making it's column NOT NULLABLE ###################### # 2) Insert records ###################### INSERT INTO t1(a, b) values(1, 'test1'), (2, 'test2'); SHOW CREATE TABLE t1\G SELECT * FROM t1; ###################### # 3) Session1 # mysql test --prompt='t1> ' ###################### BEGIN; INSERT INTO t1(a, b) values(1, 'test1') ON DUPLICATE KEY UPDATE b = VALUES(b); SELECT * FROM t1; ###################### # 4) Session 2 # mysql test --prompt='t2> ' ###################### INSERT INTO t1(a, b) values(3, 'test3'); -- gets lock even if new records, does not exists yet. ############################################ # Outputs : ############################################ # 1 ###################### mysql [localhost:8033] {msandbox} (test) > CREATE TABLE t1(i int NOT NULL AUTO_INCREMENT primary key, a int NOT NULL, b varchar(9) DEFAULT NULL, UNIQUE KEY uk1(a, b)); Query OK, 0 rows affected (0.07 sec) ###################### # 2 ###################### mysql [localhost:8033] {msandbox} (test) > INSERT INTO t1(a, b) values(1, 'test1'), (2, 'test2'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql [localhost:8033] {msandbox} (test) > SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `i` int NOT NULL AUTO_INCREMENT, `a` int NOT NULL, `b` varchar(9) DEFAULT NULL, PRIMARY KEY (`i`), UNIQUE KEY `uk1` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql [localhost:8033] {msandbox} (test) > SELECT * FROM t1; +---+---+-------+ | i | a | b | +---+---+-------+ | 1 | 1 | test1 | | 2 | 2 | test2 | +---+---+-------+ 2 rows in set (0.00 sec) ###################### # 3) Session 1 ###################### t1> BEGIN; Query OK, 0 rows affected (0.00 sec) t1> INSERT INTO t1(a, b) values(1, 'test1') ON DUPLICATE KEY UPDATE b = VALUES(b); Query OK, 0 rows affected, 1 warning (0.00 sec) t1> SELECT * FROM t1; +---+---+-------+ | i | a | b | +---+---+-------+ | 1 | 1 | test1 | | 2 | 2 | test2 | +---+---+-------+ 2 rows in set (0.00 sec) ###################### # 4) Session 2 ###################### t2> INSERT INTO t1(a, b) values(3, 'test3'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction Suggested fix: If it is an expected behavior, please update the documentation We can see notes about multiple unique from https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html, but none mentioned about locking an INSERT : > In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes. > An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637) Else, please avoid the locking for new records that still does not exists. ############################################ # Possible work around # Retain only one Primary key on a table like below : ############################################ CREATE TABLE t1(a int NOT NULL, b varchar(9) NOT NULL, UNIQUE KEY uk1(a, b)); -- make the UK to PK by making it's column NOT NULLABLE INSERT INTO t1(a, b) values(1, 'test1'), (2, 'test2'); ###################### # Perform similar steps. It should be able to proceed without locking : ###################### t1> BEGIN; Query OK, 0 rows affected (0.00 sec) t1> INSERT INTO t1(a, b) values(1, 'test1') ON DUPLICATE KEY UPDATE b = VALUES(b); Query OK, 0 rows affected, 1 warning (0.00 sec) t2> INSERT INTO t1(a, b) values(3, 'test3'); Query OK, 1 row affected (0.01 sec)