mysql中的事务基础 [英] transaction basics in mysql

问题描述

大家好，
很长时间的人，通常只是在代码休息室闲逛，但是
我真的很感谢mysql带来的一些帮助，因为我是新手.
它.我是Sybase ASE的长期用户...所以
mysql不能像我应该的那样轻松快捷地点击我...

曾经在mysql中处理过交易吗?他们无法像我那样工作
期望从我使用sybase和mssql的经验中得到，只是想看看如何
您处理过...
首先，是的，我知道您必须使用innodb存储引擎
而不是默认的myisam ...到目前为止，这还不是问题，
我听说有人说innodb的性能不佳
myisam可以，但是我还没遇到......吗?
加上它易于在两者之间转换:
"ALTER TABLE表名ENGINE = INNODB;"

无论如何，我怎么知道交易在存储中看起来像这样
mysql中的过程:

................................切................. ................
DELIMITER $$
如果存在someProc $$
，则删除程序 创建过程someProc(IN参数VARCHAR(80))
SQL安全调用程序
开始
如果不存在([..某些条件..])则
[..做一些事情..];
[..做一些事情..];
[..做一些事情..];
ELSE
[..做一些事情..];
开始交易；
[..这些是您要在事务中使用的UPDATE语句..]
UPDATE sometable1设置somevalue = value WHERE something = something;
UPDATE sometable2设置somevalue = value WHERE something = something;
UPDATE sometable3设置somevalue = value WHERE something = something;
UPDATE sometable4设置somevalue = value WHERE something = something;
[..这些是您要在事务中使用的UPDATE语句..]
COMMIT;

[..做更多事情..];
END IF;
END $$
DELIMITER;
................................切................. ................

我希望上面要做的是，
中的语句 如果
，事务将全部触发或全部失败/回滚 其中一个出现错误...这就是sybase中的情况..

但是，如果
，我最终会在sometable3和4中得到不稳定的数据 更新语句在sometable2上失败.

我发现我必须做的是对错误进行显式回滚
条件，因此添加:
用于SQLEXCEPTION，SQLWARNING，未找到回滚的DECLARE退出处理程序；

如果发生类型为sqlexecption的错误，将调用回滚，
sqlwarning或未找到". (基本上是任何错误)

我将它作为存储过程中的第一行添加到begin
之后 阻止，现在上面的作品现在也像我期望的那样(很好)现在
如果对sometable1-4的更新之一失败，则所有更新都将返回到
交易前状态.

只是想知道这是您如何处理交易，还是我
不在基地上，缺少一些更根本的东西...

sybase与mysql的唯一其他区别是回滚
不会向应用程序返回错误...我可以看到此
在某些地方很方便，但是在这种情况下，我确实想要这种行为，所以
我将退出处理程序更改为:

SQLEXCEPTION的DECLARE退出处理程序，SQLWARNING，未找到
开始回滚；致电ERROR_ROLLBACK_OCCURRED; END;

这将发出回滚，然后调用不存在的存储过程
引发错误(直到mysql6成为主流的可怜人的引发错误)

哦，好吧，谢谢您的反馈，

谢谢，
-Jam

Hello all,
long time lerker, normally just hang out in the code lounge, but
I would really appreciate some help with mysql as I am new to
it. I am a long time Sybase ASE user... so some things in
mysql don''t quick click for me as easy as they should...

Ever deal with transactions in mysql? they seam to work not as I
expect from my exp with sybase and mssql and just wanted to see how
you handled em...
first off, yeah, i know you have to use the innodb storage engine
instead of the default myisam... so far that hasn''t been a problem,
I''ve heard people say that innodb just doesn''t have the performance
that myisam does but I haven''t run into that... have you?
plus it seams easy to convert between the two:
"ALTER TABLE tablename ENGINE=INNODB;"

anyway, how I know a transaction would look like this in a stored
procedure in mysql:

................................cut.................................
DELIMITER $$
DROP PROCEDURE IF EXISTS someProc$$
CREATE PROCEDURE someProc(IN params VARCHAR(80))
SQL SECURITY INVOKER
BEGIN
IF NOT EXISTS ([.. some condition ..]) THEN
[.. do some stuff ..];
[.. do some stuff ..];
[.. do some stuff ..];
ELSE
[.. do some stuff ..];
START TRANSACTION;
[.. these are the UPDATE statements you want in a transaction ..]
UPDATE sometable1 set somevalue=value WHERE something=something;
UPDATE sometable2 set somevalue=value WHERE something=something;
UPDATE sometable3 set somevalue=value WHERE something=something;
UPDATE sometable4 set somevalue=value WHERE something=something;
[.. these are the UPDATE statements you want in a transaction ..]
COMMIT;

[.. do some more stuff ..];
END IF;
END$$
DELIMITER ;
................................cut.................................

What I would expect the above to do, is that the statements in
the transaction block either all fire or all fail/rollback if
there was an error with one of them... this is how it was in sybase..

however, I end up with inconstant data in sometable3 and 4 if
the update statement failed on sometable2.

what I found I have to do, is call an explicit rollback on an error
condition, so adding:
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND ROLLBACK;

will call a rollback if an error occurs of type sqlexecption,
sqlwarning or ''not found''. (basically, any error)

I add it as the 1st line in the stored procedure after the begin
block and now the above works as I expect it too (well almost) now
if one of the updates to sometable1-4 fail, all are returned to the
pre transaction state.

just wondering if this is how you handled transactions, or if I
am off base and missing something more fundamental...

the only other difference with mysql from sybase is that a rollback
does not raise an error back to the application... I can see this
being handy in places, but for this case, I do want the behavior, so
I changed my exit handler to this:

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
BEGIN ROLLBACK; CALL ERROR_ROLLBACK_OCCURRED; END;

this will issue a rollback then call a non-existent stored procedure
to raise an error (poor-man''s raise error until mysql6 is mainstream)

oh well, have a good one, I''d appreciate any feedback,

Thanks,
-Jam

推荐答案

如果存在someProc，则删除程序

DROP PROCEDURE IF EXISTS someProc

创建过程someProc(IN参数VARCHAR(80))
SQL安全调用程序
开始
如果不存在([..某些条件..])则
[..做一些事情..];
[..做一些事情..];
[..做一些事情..];
ELSE
[..做一些事情..];
开始交易；
[..这些是您要在事务中使用的UPDATE语句..]
UPDATE sometable1设置somevalue = value WHERE something = something;
UPDATE sometable2设置somevalue = value WHERE something = something;
UPDATE sometable3设置somevalue = value WHERE something = something;
UPDATE sometable4设置somevalue = value WHERE something = something;
[..这些是您要在事务中使用的UPDATE语句..]
COMMIT;

[..做更多事情..];
END IF;
END

CREATE PROCEDURE someProc(IN params VARCHAR(80))
SQL SECURITY INVOKER
BEGIN
IF NOT EXISTS ([.. some condition ..]) THEN
[.. do some stuff ..];
[.. do some stuff ..];
[.. do some stuff ..];
ELSE
[.. do some stuff ..];
START TRANSACTION;
[.. these are the UPDATE statements you want in a transaction ..]
UPDATE sometable1 set somevalue=value WHERE something=something;
UPDATE sometable2 set somevalue=value WHERE something=something;
UPDATE sometable3 set somevalue=value WHERE something=something;
UPDATE sometable4 set somevalue=value WHERE something=something;
[.. these are the UPDATE statements you want in a transaction ..]
COMMIT;

[.. do some more stuff ..];
END IF;
END

DELIMITER;
................................切................. ................

我希望上面要做的是，
中的语句 如果
，事务将全部触发或全部失败/回滚 其中一个出现错误...这就是sybase中的情况..

但是，如果
，我最终会在sometable3和4中得到不稳定的数据 更新语句在sometable2上失败.

我发现我必须做的是对错误进行显式回滚
条件，因此添加:
用于SQLEXCEPTION，SQLWARNING，未找到回滚的DECLARE退出处理程序；

如果发生类型为sqlexecption的错误，将调用回滚，
sqlwarning或未找到". (基本上是任何错误)

我将它作为存储过程中的第一行添加到begin
之后 阻止，现在上面的作品也按我期望的那样(几乎可以)现在
如果对sometable1-4的更新之一失败，则所有更新都将返回到
交易前状态.

只是想知道这是您处理交易的方式，还是我
不在基地上，缺少一些更根本的东西...

sybase与mysql的唯一其他区别是回滚
不会向应用程序返回错误...我可以看到此
在某些地方很方便，但是在这种情况下，我确实想要这种行为，所以
我将退出处理程序更改为:

SQLEXCEPTION的DECLARE退出处理程序，SQLWARNING，未找到
开始回滚；致电ERROR_ROLLBACK_OCCURRED; END;

这将发出回滚，然后调用不存在的存储过程
引发错误(直到mysql6成为主流的可怜人的引发错误)

哦，好吧，谢谢您的反馈，

谢谢，
-Jam

DELIMITER ;
................................cut.................................

What I would expect the above to do, is that the statements in
the transaction block either all fire or all fail/rollback if
there was an error with one of them... this is how it was in sybase..

however, I end up with inconstant data in sometable3 and 4 if
the update statement failed on sometable2.

what I found I have to do, is call an explicit rollback on an error
condition, so adding:
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND ROLLBACK;

will call a rollback if an error occurs of type sqlexecption,
sqlwarning or ''not found''. (basically, any error)

I add it as the 1st line in the stored procedure after the begin
block and now the above works as I expect it too (well almost) now
if one of the updates to sometable1-4 fail, all are returned to the
pre transaction state.

just wondering if this is how you handled transactions, or if I
am off base and missing something more fundamental...

the only other difference with mysql from sybase is that a rollback
does not raise an error back to the application... I can see this
being handy in places, but for this case, I do want the behavior, so
I changed my exit handler to this:

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
BEGIN ROLLBACK; CALL ERROR_ROLLBACK_OCCURRED; END;

this will issue a rollback then call a non-existent stored procedure
to raise an error (poor-man''s raise error until mysql6 is mainstream)

oh well, have a good one, I''d appreciate any feedback,

Thanks,
-Jam

这篇关于mysql中的事务基础的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！