Automatic Rollback if COMMIT TRANSACTION is not reached

mysql transactions rollback
Koekiebox picture Koekiebox · May 25, 2011 · Viewed 45.2k times · Source

Consider the following:

START TRANSACTION;

BEGIN;

INSERT INTO prp_property1 (module_name,environment_name,NAME,VALUE) VALUES ('','production','','300000');

/** Assume there is syntax error SQL here...**/
Blah blah blah

DELETE FROM prp_property1 WHERE environment_name = 'production';

COMMIT TRANSACTION;

Question:

I noticed that the transaction automatically rolls back and the record insert attempt fails.

If I don't provide a error handler or error check along with ROLLBACK TRANSACTION as above, is it safe as it seems to be doing the job in an example like above because the COMMIT TRANSACTION never gets executed?

I assume the transaction is rolled back immediately and discarded as soon as a error occurs.

Answer

MarkR picture MarkR · May 25, 2011

No, transactions are not rolled back as soon as an error occurs. But you may be using a client-application which applies this policy.

For example, if you are using the mysql command-line client, then it normally stops executing when an error occurs and will quit. Quitting while a transaction is in progress does cause it to be rolled back.

When you are writing your own application, you can control the policy on rollback, but there are some exceptions:

  • Quitting (i.e. disconnecting from the database) always rolls back a transaction in progress
  • A deadlock or lock-wait timeout implicitly causes a rollback

Other than these conditions, if you invoke a command which generates an error, the error is returned as normal, and you are free to do whatever you like, including committing the transaction anyway.

Related questions

mysql transaction - roll back on any exception

Is it possible to roll back automatically if any error occurs on a list of mysql commands? for example something along the lines of: begin transaction; insert into myTable values1 ... insert into myTable values2 ...; -- will throw an error commit; …

Read More
PHP & mySQL: Simple code to implement Transaction - Commit & Rollback

MY PLATFORM: PHP & mySQL MY SITUATION: I am trying to implement transactions within my code. I tried to follow examples, but it's not much help. I am running 3 queries and I wanted to write a transaction in such a …

Read More
MySQL rollback on transaction with lost/disconnected connection

I need to make MySQL server to rollback transaction immediately after its client disconnected, because each client works concurrently. The problem can be reproduced like these (using an innodb table type) On Client A: START TRANSACTION; SELECT MAX(ID) FROM …

Read More