MySQL transaction conundrum

I need to perform several inserts in a single atomic transaction. For example:

start tran开发者_Go百科saction;

insert ...

insert ...

commit;

However when MySQL encounters an error it aborts only the particular statement that caused the error. For example, if there is an error in the second insert statement the commit will still take place and the first insert statement will be recorded. Thus, when errors occur a MySQL transaction is not really a transaction. To overcome this problem I have used an error exit handler where I rollback the transaction. Now the transaction is silently aborted but I don't know what was the problem.

So here is the conundrum for you:

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

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

MySQL does pass error code to the caller and based on this error code the caller is free to decide whether it wants to commit work done up to the moment (ignoring the error with this particular INSERT statement) or to rollback the transaction.

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

Update:

It's a bad practice to use an unconditional ROLLBACK inside the stored procedures.

Stored procedures are stackable and transactions are not, so a ROLLBACK within a nested stored procedure will roll back to the very beginning of the transaction, not to the state of the stored procedure execution.

If you want to use transactions to restore the database state on errors, use SAVEPOINT constructs and DECLARE HANDLER to rollback to the 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 either insert will roll back all changes made by the procedure and exit it.

Using Mr. Quassnoi's example, here's my best approach to catching specific errors:

The idea is to use a tvariable to catch a simple error message, then you can catch sql states you think may happen to save custom messages to 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 do your usual call, and do a select statement for the variable like:

call prc_work(); select @prc_work_error;

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

It's tedious and not very flexible because requires a DECLARE EXIT HANDLER segment for each status code you want to catch, it won't also show detailed error messages but hey, it works.