Duplicate key on delete and update of unique key I have a table with 10^7 records. I do the following:


Delete a record using a unique key to identify it
Update another record's unique key to the one I have just deleted


MySQL 5.7 complains about duplicate keys. Schematically, the sequence of commands looks like the following:

create database foo;
use foo;

create table users(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `login` (`login`),
  KEY `email_idx` (`email`)
);

insert into users values(1, 'foo', 'foo@gmail.com');
insert into users values(2, 'bar', 'bar@hotmail.com');

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
delete from users where login = 'foo';
update users set login = 'foo' where email = 'bar@hotmail.com';
COMMIT;


The code above does not fail, but the actual code does fail, even when just one transaction runs.

Why could this be happening?
The mentioned issue will not surface for the given code. If you could create a new code with which we shall reproduce the same error, immediate reply is guaranteed.

The choices are


Do not alter the unique key. You shall redesign your logic.
Unique key fields takes little longer to get updated as they need to be updated both in the data file and the index file. So, give some time gap before using the same key.
Try (atleast) committing the delete and then move on to creating another row with the same deleted key row (though not a good suggestion).