Bug #53871 X lock is taken by innodb on duplicate key error, insert on duplicate key update
Submitted: 21 May 2010 2:57 Modified: 21 May 2010 22:50
Reporter: Mark Callaghan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: duplicate, innodb, insert, key, on, UPDATE

[21 May 2010 2:57] Mark Callaghan 
Description:
 
Per http://bugs.mysql.com/bug.php?id=7975
An X lock is taken on duplicate key error during INSERT ... ON DUPLICATE KEY UPDATE. The docs state an S lock is taken.
http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html

How to repeat:
Read the bug update, ask Harrison
[21 May 2010 19:07] Paul DuBois 
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added:

REPLACE is done like an INSERT if there is no collision on a unique
key. Otherwise, an exclusive next-key lock is placed on the row to be
updated. Similarly, for INSERT ... ON DUPLICATE KEY UPDATE, an
exclusive next-key lock is placed on the row to be updated when a
duplicate-key error occurs.
[21 May 2010 22:50] Paul DuBois 
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

INSERT ... ODKU given separate item.