atomicity about REPLACE INTO and INSERT...ON DUPLICATE KEY UPDATE

MySQL & MariaDB Other MySQL® Questions
1

Here is an example:
TABLE:
[INDENT] CREATE TABLE TICKET (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
stub char(1) NOT NULL DEFAULT ‘’,
PRIMARY KEY (id),
UNIQUE KEY idx_ticket_stub (stub)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
[/INDENT]
[INDENT] INSERT INTO demo(stub) VALUES(‘a’) ON DUPLICATE KEY UPDATE id = id + 1
[/INDENT]

2

I think it is just like “REPLACE INTO” about atomicity. I run a golang demo to simulate concurrent execution when using “REPLACE INTO” and “INSERT … INTO ON DUPLICATE KEY UPDATE”. All of those will sometimes output error info about “exec error Error 1213: Deadlock found when trying to get lock; try restarting transaction”. I think this means that both “REPLACE INTO” and “INSERT … INTO ON DUPLICATE KEY UPDATE” use lock or transaction internally. :slight_smile: