Not the answer you need?
Register and ask your own question!

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

andy xieandy xie EntrantCurrent User Role Beginner
Here is an example:
TABLE:
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
INSERT INTO demo(stub) VALUES('a') ON DUPLICATE KEY UPDATE id = id + 1

Comments

  • andy xieandy xie Entrant Current User Role Beginner
    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. :)
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.