Bug #68360 Duplicate entry for key while updating
Submitted: 13 Feb 2013 10:36 Modified: 15 Feb 2013 18:00
Reporter: Andreas Mller Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.28, 5.0.97, 5.1.69, 5.5.31, 5.7.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: key violation update

[13 Feb 2013 10:36] Andreas Mller
Description:
Updating multiple rows in one single statement fails with key violation if temporary a key value is douplicated even if there is no key violation at the end of this statement.

If suggest the same is happening within a multi statement transaction.

Because it is a single statement (or a single transaction) temporary states should not result in failing. The example below works on mssql or oracle without any problems.

How to repeat:
+----+-----------+------------+-------------+-------------+-------------+
| id | id_parent | sort_order | some_data   | other_data  | more_data   |
+----+-----------+------------+-------------+-------------+-------------+
| 1  |         1 |          1 | lorem ipsum | lorem ipsum | lorem ipsum |
| 2  |         1 |          2 | lorem ipsum | lorem ipsum | lorem ipsum |
| 3  |         1 |          3 | lorem ipsum | lorem ipsum | lorem ipsum |
+----+-----------+------------+-------------+-------------+-------------+

unique key on (ip_parent,sort_order)

UPDATE table SET sort_order=sort_order+1 WHERE id_parent=1;

Suggested fix:
Check key violations only at the end of a single statement/transaction.
[13 Feb 2013 10:44] Hartmut Holzgraefe
Workaround: ... ORDER BY sort_order DESC

MySQL does not defer any constraint checks until commit, everything is checked immediately, and for unique keys i don't even thing deferring the check makes sense ... is there any other DB product out there that works this way?

For foreign key constraints the limitation is documented on 

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

" In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT. "
[13 Feb 2013 10:55] Andreas Mller
As described it works i.e. with ms-sql or oracle.

The workaround works for me but I think it is a violation of the sql idea. A single statement should work if the result is correct.
[15 Feb 2013 18:00] Sveta Smirnova
Thank you for the reasonable feature request.

Test case:

create table t1(f1 int unique, f2 int);
insert into t1 values (1,1), (2,1), (3,1);
select * from t1;
update t1 set f1 = f1 + 1 where f2 = 1;
select * from t1;

PostgreSQL works like MySQL:

test=# update t1 set f1 = f1 + 1 where f2 = 1;
ERROR:  duplicate key value violates unique constraint "t1_f1_key"