Avoid unique violation in atomic transaction

Question

Is possible to create atomic transaction in MySQL?

Consider I have table 'category' with these rows:

id|name
--|---------
1 |'tablets'
2 |'phones'

And column name is my primary key.

If I try:

START TRANSACTION;
update "category" set name = 'phones' where id = 1;
update "category" set name = 'tablets' where id = 2;
COMMIT;

I'm getting:

ERROR:  duplicate key value violates unique constraint 
"category_name_key"
DETAIL:  Key (name)=(tablets) already exists.

My expectation is that constraint checks should be done only during commit. Is this possible with MySQL?

Answer 1

There is no such thing possible currently in MySQL. As per mysql doc

Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself using a foreign key.

To do this in Mysql use intermediate table so that the same issue doesn't happens but currently there is no clean way to do this in mysql.

Its possible in other databases like Postgresql where you can set mode to check for contraints after transaction is committed

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

Answer 2

You make the example too simple so even when this solve your question, maybe isnt what you want.

SQL Fiddle Demo

UPDATE `category`
SET id =  CASE WHEN name = 'phones'   THEN 1
               WHEN name = 'tablets'  THEN 2
          END
WHERE name in ('tablets', 'phones');

OUTPUT

| id |    name |
|----|---------|
|  2 | tablets |
|  1 |  phones |

Answer 3

Is not possible because mysql MySQL processes updates enforcinge the checking of UNIQUE (and other) constraints after every single row update and not - as it should be doing - after the whole UPDATE statement is completed.

then you should use a intermediate update

update "category" set name = 'temp' where id = 1;
update "category" set name = 'tablets' where id = 2;
update "category" set name = 'phone' where id = 1;