Bug #93806 Document error about "ON DUPLICATE KEY UPDATE "
Submitted: 3 Jan 2019 17:41 Modified: 20 Feb 2019 15:19
Reporter: xiaobin lin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:all OS:Any
Assigned to: CPU Architecture:Any

[3 Jan 2019 17:41] xiaobin lin
Description:

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html 

INSERT ... ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value.

*An exclusive index-record lock is taken for a duplicate primary key value* seems to be an error.

My test:

--session A
create table t(id int primary key, a int)engine=innodb;
insert into t values(1,1),(5,5);

set transaction_isolation='repeatable-read';
begin;
insert into t values(5,5) ON DUPLICATE KEY UPDATE a=a+1;

--session B
set transaction_isolation='repeatable-read';
insert into t values(4,4);
(blocked here, shows the next-key lock (1,5] is hold by session A.  Not only   index-record lock)

How to repeat:
as above

Suggested fix:
document stuff
[14 Jan 2019 15:15] MySQL Verification Team
Hi,

Thank you for your bug report.

I agree with you. How can a primary or unique key have a duplicate value !!!!!!

Verified as reported.
[14 Jan 2019 15:17] MySQL Verification Team
The error is exactly where you pointed it to be.
[20 Feb 2019 15:19] MySQL Verification Team
Hi,

This issue is fixed with a patch committed in the release 8.0.16.
[28 Aug 2020 15:18] Daniel Price
Posted by developer:
 
The scenario is no longer reproduceable. The issue was addressed in 5.7.26, 8.0.16 by the fix for:
Bug #25966845 (see https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-16.html)

The INSERT with (4,4) now succeeds without waiting. Documentation update no longer required.

Session A:
CREATE TABLE t(id INT PRIMARY KEY, a INT);
INSERT INTO t VALUES(1,1),(5,5);

BEGIN;
INSERT INTO t VALUES(5,5) ON DUPLICATE KEY UPDATE a=a+1;

mysql> SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                  1614 | t           | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|                  1614 | t           | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 5         |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)

SESSION B:
mysql> INSERT INTO t VALUES(4,4);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t;
+----+------+
| id | a    |
+----+------+
|  1 |    1 |
|  4 |    4 |
|  5 |    5 |
+----+------+
3 rows in set (0.00 sec)

Thank you for the bug report.
[21 Sep 2020 14:21] MySQL Verification Team
Thank you, Daniel.