I've come to the conclusion that the only way to swap two rows where the value to be swapped is part of a unique constraint, is to update row 1 to a temporary value outside the normal range (such as a negative number for an INT column), change row 2 to the previous value of row 1 and change row 1 to the previous value of row 2.
Simplified example:
SELECT uniquevalue1 AS prev1 FROM sometable;
SELECT uniquevalue2 AS prev2 FROM sometable;
UPDATE sometable SET uniquevalue1=-1;
UPDATE sometable SET uniquevalue2=[prev1];
UPDATE sometable SET uniquevalue1=[prev2];
Followed by a commit.
This works fine, no problem, but:
Is this operation safe to use?
Imagine a unique constraint on a default (signed) INT(11) column where you swap temporarily to -1 and then follow the pattern described above - with autocommit disabled, of course.
What happens if you perform these two swap operations at the same time? Will the lack of a commit until after -1 is no longer the value of any rows mean that the statements will not collide?
You can not execute two or more statements at the same time. They are always executed one by one.
However, if you use a transaction (remark: This will only work for tables of innodb type, not myisam) this opteration will either fail with no changes left or succeed. A second swap that also tries to use the number -1 will be locked until the first transaction finishes.