MySQL transaction riddle

Question

I need to do multiple attachments in one atomic transaction. For instance:

start a transaction;

insert ...

insert ...

fixation;

However, when MySQL encounters an error, it interrupts only the specific statement that caused the error. For example, if there is an error in the second insert, then the commit will still be performed, and the first insert statement will be written. Thus, when errors occur, the MySQL transaction is not a transaction. To overcome this problem, I used an error exit handler where I roll back the transaction. Now the transaction is silently aborting, but I do not know what the problem is.

So, here's a riddle for you:

How can I get MySQL to abort a transaction when it encounters an error and pass the error code to the caller?

Answer 1

How can I make MySQL abort a transaction when it encounters an error and pass the error code to the caller?

MySQL passes the error code to the caller and, based on this error code, the caller can decide whether he wants to complete the work up to the moment (ignoring the error using this specific INSERT ) or rollback the transaction.

This is not like PostgreSQL , which always aborts a transaction on error, and this behavior is the source of many problems.

Update:

Bad practice of using unconditional ROLLBACK inside stored procedures.

Stored procedures are stacked and there are no transactions, so ROLLBACK inside the nested stored procedure is rolled back to the very beginning of the transaction, and not to the execution state of the stored procedure.

If you want to use transactions to restore the database in case of errors, use the SAVEPOINT and DECLARE HANDLER to roll back to savepoints:

 CREATE PROCEDURE prc_work() BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_prc_work; SAVEPOINT sp_prc_work; INSERT …; INSERT …; … END; 

Failure in any insert will roll back all changes made by the procedure and exit it.

Answer 2

Using the example of Mr. Kwasnui, here is my best approach to finding specific errors:

The idea is to use tvariable to catch a simple error message, then you can catch the SQL states that you think might happen to save custom messages in your variable:

 DELIMITER $$ DROP PROCEDURE IF EXISTS prc_work $$ CREATE PROCEDURE prc_work () BEGIN DECLARE EXIT HANDLER FOR SQLSTATE '23000' BEGIN SET @prc_work_error = 'Repeated key'; ROLLBACK TO sp_prc_work; END; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET @prc_work_error = 'Unknown error'; ROLLBACK TO sp_prc_work; END; START TRANSACTION; SAVEPOINT sp_prc_work; INSERT into test (id, name) VALUES (1, 'SomeText'); COMMIT; END $$ DELIMITER ; 

Then you just make your normal call and execute the select statement on a variable of type:

 call prc_work(); select @prc_work_error; 

This will return either NULL if there is no error, or a message error in case of an error. If you need a constant error message, you can create a table to store it.

This is tedious and not very flexible, because for every status code you want to catch, the DECLARE EXIT HANDLER segment is required, it will not show detailed error messages either, but hey, it works.