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:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:all OS:Any
Assigned to: CPU Architecture:Any

[3 Jan 2019 17:41] xiaobin lin


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';
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] Sinisa Milivojevic

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] Sinisa Milivojevic
The error is exactly where you pointed it to be.
[20 Feb 2019 15:19] Sinisa Milivojevic

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