Koekiebox Koekiebox - 1 year ago 58
MySQL Question

Automatic Rollback if COMMIT TRANSACTION is not reached

Consider the following:



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';



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
as above, is it safe as it seems to be doing the job in an example like above because the
never gets executed?

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download