MySQL交易难题

本文介绍了MySQL交易难题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要在单个原子事务中执行几次插入.例如:

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

开始交易；

start transaction;

插入...

插入...

提交;

但是，当MySQL遇到错误时，它将仅中止导致错误的特定语句.例如，如果第二个insert语句中有错误，则提交仍将发生，并且将记录第一个insert语句.因此，当发生错误时，MySQL事务实际上不是事务.为了克服这个问题，我使用了一个错误退出处理程序，在其中回滚事务.现在，交易被静默中止，但是我不知道出了什么问题.

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.

这是给您的难题:

我如何才能使MySQL在遇到错误时中止交易，并将错误代码传递给调用方?

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

推荐答案

当遇到错误时，如何使MySQL中止事务并将错误代码传递给调用方?

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

MySQL确实将错误代码传递给调用方，并且根据此错误代码，调用方可以自由决定是要提交直到当前的工作(忽略此特定INSERT语句的错误)还是回滚交易.

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.

这不同于PostgreSQL，后者总是在出错时中止事务，这种行为是许多问题的根源.

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

更新:

在存储过程中使用无条件的ROLLBACK是一种不好的做法.

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

存储过程是可堆叠的，事务不能存储，因此嵌套存储过程中的ROLLBACK将回滚到事务的开始，而不是存储过程执行的状态.

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.

如果要使用事务恢复错误时的数据库状态，请使用SAVEPOINT构造，并使用DECLARE HANDLER回滚到保存点:

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.