MySQL：存储过程中的事务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9974325/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL : transaction within a stored procedure
提问by Priyank Kapasi
The basic structure of my stored procedure is,
我的存储过程的基本结构是，
BEGIN
.. Declare statements ..
START TRANSACTION;
.. Query 1 ..
.. Query 2 ..
.. Query 3 ..
COMMIT;
END
MySQL version:5.1.61-0ubuntu0.11.10.1-log
MySQL 版本：5.1.61-0ubuntu0.11.10.1-log
Currently, if 'query 2' fails, result of 'query 1' is committed.
当前，如果“查询 2”失败，则提交“查询 1”的结果。
- How can I rollback the transaction if any of the query fails?
- 如果任何查询失败，我如何回滚事务？
采纳答案by rkosegi
Take a look at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html
看看http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html
Basically you declare error handler which will call rollback
基本上你声明错误处理程序将调用回滚
START TRANSACTION;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
EXIT PROCEDURE;
END;
COMMIT;
回答by Priyank Kapasi
Just an alternative to the code by rkosegi,
只是 rkosegi 代码的替代方案，
BEGIN
.. Declare statements ..
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
.. set any flags etc eg. SET @flag = 0; ..
ROLLBACK;
END;
START TRANSACTION;
.. Query 1 ..
.. Query 2 ..
.. Query 3 ..
COMMIT;
.. eg. SET @flag = 1; ..
END
回答by user2288580
Here's an example of a transaction that will rollback on error and return the error code.
下面是一个事务示例，它将在错误时回滚并返回错误代码。
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_CREATE_SERVER_USER`(
IN P_server_id VARCHAR(100),
IN P_db_user_pw_creds VARCHAR(32),
IN p_premium_status_name VARCHAR(100),
IN P_premium_status_limit INT,
IN P_user_tag VARCHAR(255),
IN P_first_name VARCHAR(50),
IN P_last_name VARCHAR(50)
)
BEGIN
DECLARE errno INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;
SELECT errno AS MYSQL_ERROR;
ROLLBACK;
END;
START TRANSACTION;
INSERT INTO server_users(server_id, db_user_pw_creds, premium_status_name, premium_status_limit)
VALUES(P_server_id, P_db_user_pw_creds, P_premium_status_name, P_premium_status_limit);
INSERT INTO client_users(user_id, server_id, user_tag, first_name, last_name, lat, lng)
VALUES(P_server_id, P_server_id, P_user_tag, P_first_name, P_last_name, 0, 0);
COMMIT WORK;
END$$
DELIMITER ;
This is assuming that autocommit is set to 0. Hope this helps.
这是假设自动提交设置为 0。希望这会有所帮助。
回答by Madacol
This is just an explanation not addressed in other answers
这只是其他答案中未涉及的解释
At least in recent versions of Mysql, your first query is not committed.
至少在 Mysql 的最新版本中，您的第一个查询未提交。
If you query it under the same session you will see the changes, but if you query it from a different session, the changes are not there, they are not committed.
如果您在同一个会话下查询它，您将看到更改，但是如果您从不同的会话查询它，则更改不存在，它们没有提交。
What's going on?
这是怎么回事？
When you open a transaction, and a query inside it fails, the transaction keeps open, it does not commitnor rollbackthe changes.
当您打开一个事务并且其中的查询失败时，该事务保持打开状态，它不会提交或回滚更改。
So BE CAREFUL, any table/row that was locked with a previous query likeSELECT ... FOR SHARE/UPDATE, UPDATE, INSERTor any other locking-query, keeps locked until that session is killed (and executes a rollback), or until a subsequent query commits it explicitly (COMMIT) or implicitly, thus making the partial changes permanent(which might happen hours later, while the transaction was in a waiting state).
所以要小心，任何表/一行被锁定，就像以前的查询SELECT ... FOR SHARE/UPDATE，UPDATE，INSERT或任何其他锁定查询，一直锁定，直到该会话被杀死（并执行回滚），或直到随后的查询提交它明确（COMMIT）或隐式，从而使部分更改永久化（这可能会在数小时后发生，而交易处于等待状态）。
That's why the solution involves declaring handlers to immediately ROLLBACKwhen an error happens.
这就是解决方案涉及ROLLBACK在发生错误时立即声明处理程序的原因。
Extra
额外的
Inside the handler you can also re-raise the error using RESIGNAL, otherwise the stored procedure executes "Successfully"
在处理程序中，您还可以使用 重新引发错误RESIGNAL，否则存储过程将“成功”执行
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
#.. Query 1 ..
#.. Query 2 ..
#.. Query 3 ..
COMMIT;
END
