Automatic Rollback if COMMIT TRANSACTION is not reached

Question

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.

Twinge

I am using SQLyog as a client. – Twinge

See here #6122417 – Epicalyx

Answer 1

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.

Hangover

Confused with documentation: "Rolling back can be a slow operation that may occur implicitly without the user having explicitly asked for it (for example, when an error occurs)." :-S – Thomajan

The documentation is not clear; perhaps it means in the cases I mentioned above? – Hangover

Error 1205: Lock wait timeout exceeded does not cause a rollback as long as your server version >= 5.0.13 dev.mysql.com/doc/refman/5.0/en/innodb-error-handling.html – Pus

Answer 2

Use Mysql stored procedure

   BEGIN

   DECLARE exit handler for sqlexception
      BEGIN
      ROLLBACK;
   END;

   DECLARE exit handler for sqlwarning
     BEGIN
     ROLLBACK;
   END;

   START TRANSACTION;

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

   [ERROR]

   COMMIT;

   END

You can set if warning or error rollback, then you don't need delete, with transaction all entry is deleted.

Epicalyx

Answer 3

You may use procedure to do this more effectively.
Transaction with Stored Procedure in MySQL Server

Carefree

Thanks for the link -- it's the only proper example of error handling that I was able to find! – Cozmo

Answer 4

I would like to add to what @MarkR already said. Error Handling, assuming InnoDB engine, happens as described in the Mysql Server Documentation

• If you run out of file space in a tablespace, a MySQL Table is full error occurs and InnoDB rolls back the SQL statement.
• A transaction deadlock causes InnoDB to roll back the entire transaction.
• A duplicate-key error rolls back the SQL statement
• A row too long error rolls back the SQL statement.
• Other errors are mostly detected by the MySQL layer of code (above the InnoDB storage engine level), and they roll back the corresponding SQL statement

My understanding is also that when the Mysql session ends (when the php scripts ends), anything that is not committed is rolled back. I yet have to find a really reliable source to back this statement so do not take my word for it.

Semaphore

Answer 5

I've tested these three situations; mySQL does not roll back automatically.

A transaction deadlock causes InnoDB to roll back the entire transaction. A duplicate-key error rolls back the SQL statement A row too long error rolls back the SQL statement.

Only the affected records fail, the rest of the records succeed unless your application calls "rollback" explicitly.

Hitandrun

Answer 6

Mysql version is 8.x

Top topic saied two things

1. TRANSACTION
2. Stored Routines

My Refrence content :

By default, MySQL runs with autocommit mode enabled. This means that, when not otherwise inside a transaction, each statement is atomic, as if it were surrounded by START TRANSACTION and COMMIT. You cannot use ROLLBACK to undo the effect; however, if an error occurs during statement execution, the statement is rolled back.

To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.

Miki