php Mysql 事务：提交和回滚
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19890966/
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 : commit and rollback
提问by JinSnow
I updated my PhpMyAdmin database engine from MyISAM to INNODB to allow rollback.
我将我的 PhpMyAdmin 数据库引擎从 MyISAM 更新到 INNODB 以允许回滚。
This is my SQL query :
这是我的 SQL 查询：
START TRANSACTION;
UPDATE jkm_content SET state=0 WHERE title IN ('title-1','title2');
And the result :
结果：
start transaction;# MySQL returned an empty result set (i.e. zero
rows).
UPDATE jkm_content SET state=1 WHERE title IN ('title-1','title2');# 2 rows affected.
1) So the statement informs me that 2 rows are affected but the change doesn't appear anywhere (neither in my DB nor in the website).I though start transactionwould allow me to visualize the changes (in a temporary DB) and then if I am satisfy I "commit" the query. (I understand I need to committo update the DB, but if I committhe change will be permanent).
1）所以该语句通知我 2 行受到影响，但更改没有出现在任何地方（既不在我的数据库中，也没有在网站中）。我虽然start transaction允许我可视化更改（在临时数据库中），然后如果我我很满意我“提交”了查询。（我知道我需要commit更新数据库，但如果我commit更改将是永久性的）。
2) Then I don't get the point of rollbackif I can't see the effect before committing it. What will be the difference between these two queries :
2）那么我不明白rollback如果在提交之前我看不到效果。这两个查询之间有什么区别：
START TRANSACTION;
UPDATE jkm_content SET state=0 WHERE title IN ('title-1','title2');
AND
和
START TRANSACTION;
UPDATE jkm_content SET state=0 WHERE title IN ('title-1','title2');
ROLLBACK;
3) if I got it right, these functions are all the same :
3) 如果我猜对了，这些功能都是一样的：
START TRANSACTION
BEGIN
BEGIN WORK
回答by Peter van der Wal
1) All changes you make are visible within the same transaction. If you do
1) 您所做的所有更改都在同一事务中可见。如果你这样做
START TRANSACTION;
INSERT INTO MyTable VALUES ('Hi there');
SELECT * FROM MyTable;
your output will include the 'Hi there'. But if you start a second database-connection the new row won't be displayed until you commit your transaction from within the first connection. Try playing with this using two database-connections using the command-line.
您的输出将包括“嗨”。但是，如果您启动第二个数据库连接，则在您从第一个连接中提交事务之前，不会显示新行。尝试使用命令行使用两个数据库连接来玩这个。
You're not seeing the effect in your website because you can't have the same transaction within two database-connection (a new db-connection will be made at the beginning of your request).
您没有在您的网站中看到效果，因为您不能在两个数据库连接中进行相同的事务（在您的请求开始时将创建一个新的 db-connection）。
2) All transactions that aren't committed will be rolled back when the connection with the database is closed. So if these are your only two queries, there are no difference. However there is a difference between
2）当与数据库的连接关闭时，所有未提交的事务将被回滚。因此，如果这是您仅有的两个查询，则没有区别。但是两者之间有区别
START TRANSACTION;
INSERT INTO MyTable VALUES ('This one would be discarded on rollback');
ROLLBACK;
INSERT INTO MyTable VALUES ('This one will be permanent because not within transaction');
3) Yes, these are all the same.
3) 是的，这些都是一样的。
回答by a1ex07
Changes you made within one transaction are not visible to other transactions (except transactions with
READ UNCOMMITTEDisolation level) until the transaction is committed.There is a huge difference between rolling back transaction and keeping it open forever (or until the engine kill it due to timeout). The latter means server cannot free resources allocated to support transaction. In addition, since you do
UPDATE, mysql has to issue exclusive locks on rows affected, and no other transaction can update/delete these rows. If you have an application that leaves transactions open, you will very likely end up with either all connections busy and waiting forever, or bunch of deadlocks .Yes, they all start a new transaction in mysql.
在提交事务之前，您在一个事务中所做的更改对其他事务不可见（具有
READ UNCOMMITTED隔离级别的事务除外）。回滚事务和保持它永远打开（或直到引擎因超时而终止它）之间存在巨大差异。后者意味着服务器不能释放分配给支持事务的资源。此外，由于您这样做
UPDATE，mysql 必须对受影响的行发出排他锁，并且没有其他事务可以更新/删除这些行。如果您的应用程序让事务保持打开状态，您很可能最终会遇到所有连接都忙且永远等待的情况，或者一堆死锁。是的，它们都在 mysql 中启动了一个新事务。
