Mysql – duplicate key error does not cancel/rollback thesql transaction

MySQLrollbacktransactions

When in a mysql innodb transaction, I would expect a duplicate key error to cause a rollback. It doesn't, instead it simply throws an error and continues on to the next command. Once the COMMIT command is reached, the transaction will be committed, sans the duplicate key causing command.

Is this the expected behaviour? If so, how would one go about setting it up so that the transaction is rolled back instead of committed when such an error occurs?

test environment:

CREATE TABLE `test` (  
  `id` int(11) NOT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

BEGIN;
     INSERT INTO test VALUES (5);
     INSERT INTO test VALUES (5);
COMMIT;

expected result: table test is empty

actual result: table test contains one record with value 5

Best Answer

MySql (and other sql engine AFAIK) does not automatically rollback a transaction if an error occurs.

You have to declare an error handler that will Rollback the transaction:

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
BEGIN 
  ROLLBACK; 
  CALL ERROR_ROLLBACK_OCCURRED; 
END;

Related Solutions

SQL Identity (autonumber) is Incremented Even with a Transaction Rollback

If you think about it, the auto-increment number should not be transactional. If other transactions had to wait to see if the auto-number was going to be used or "rolled back", they would be blocked by the existing transaction using the auto-number. For example, consider my psuedo code below with table A using an auto-number field for the ID column:

User 1
------------
begin transaction
insert into A ...
insert into B ...
update C ...
insert into D ...
commit


User 2
-----------
begin transaction
insert into A ...
insert into B ...
commit

If user 2's transaction starts a millisecond after user 1's, then their insert into table A would have to wait for user 1's entire transaction to complete just to see if the auto-number from the first insert into A was used.

This is a feature, not a bug. I would recommend using another scheme to generate auto-numbers if you need them to be tightly sequential.

Mysql – “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”

I would recommend using INSERT...ON DUPLICATE KEY UPDATE.

If you use INSERT IGNORE, then the row won't actually be inserted if it results in a duplicate key. But the statement won't generate an error. It generates a warning instead. These cases include:

Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints.
Inserting a NULL into a column with a NOT NULL constraint.
Inserting a row to a partitioned table, but the values you insert don't map to a partition.

If you use REPLACE, MySQL actually does a DELETE followed by an INSERT internally, which has some unexpected side effects:

A new auto-increment ID is allocated.
Dependent rows with foreign keys may be deleted (if you use cascading foreign keys) or else prevent the REPLACE.
Triggers that fire on DELETE are executed unnecessarily.
Side effects are propagated to replicas too.

correction: both REPLACE and INSERT...ON DUPLICATE KEY UPDATE are non-standard, proprietary inventions specific to MySQL. ANSI SQL 2003 defines a MERGE statement that can solve the same need (and more), but MySQL does not support the MERGE statement.

A user tried to edit this post (the edit was rejected by moderators). The edit tried to add a claim that INSERT...ON DUPLICATE KEY UPDATE causes a new auto-increment id to be allocated. It's true that the new id is generated, but it is not used in the changed row.

See demonstration below, tested with Percona Server 5.5.28. The configuration variable innodb_autoinc_lock_mode=1 (the default):

mysql> create table foo (id serial primary key, u int, unique key (u));
mysql> insert into foo (u) values (10);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   10 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

mysql> insert into foo (u) values (10) on duplicate key update u = 20;
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

The above demonstrates that the IODKU statement detects the duplicate, and invokes the update to change the value of u. Note the AUTO_INCREMENT=3 indicates an id was generated, but not used in the row.

Whereas REPLACE does delete the original row and inserts a new row, generating and storing a new auto-increment id:

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+
mysql> replace into foo (u) values (20);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  3 |   20 |
+----+------+

Best Answer

Related Solutions

SQL Identity (autonumber) is Incremented Even with a Transaction Rollback

Mysql – “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”

Related Topic