MySQL Bugs: #93806: Document error about "ON DUPLICATE KEY UPDATE "

Bug #93806	Document error about "ON DUPLICATE KEY UPDATE "
Submitted:	3 Jan 2019 17:41	Modified:	20 Feb 2019 15:19
Reporter:	lin bryce	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

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

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.

The error is exactly where you pointed it to be.

Hi,

This issue is fixed with a patch committed in the release 8.0.16.

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.

Thank you, Daniel.