SET autocommit = 1和mysql中的START TRANSACTION之间的区别(我错过了什么吗?) [英] Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)

问题描述

我正在阅读MySQL中的事务，并且不确定我是否正确掌握了某些特定的知识，并且我想确保自己理解正确，所以这里是正确的.我知道交易应该做什么，只是不确定我是否理解语句的语义.

I am reading up on transactions in MySQL and am not sure whether I have grasped something specific correctly, and I want to be sure I understood that correctly, so here goes. I know what a transaction is supposed to do, I'm just not sure whether I understood the statement semantics or not.

所以，我的问题是，有什么不对的地方(如果是，那是什么地方不对劲)，并且出现以下情况:

So, my question is, is anything wrong, (and, if that is the case, what is wrong) with the following:

默认情况下，MySQL中启用了自动提交模式.

By default, autocommit mode is enabled in MySQL.

现在，SET autocommit=0;将开始事务，SET autocommit=1;将隐式提交. COMMIT;和ROLLBACK;都是可能的，在这两种情况下，之后自动提交仍然设置为0(并且隐式启动了新事务).

Now, SET autocommit=0; will begin a transaction, SET autocommit=1; will implicitly commit. It is possible to COMMIT; as well as ROLLBACK;, in both of which cases autocommit is still set to 0 afterwards (and a new transaction is implicitly started).

START TRANSACTION;基本上会一直SET autocommit=0;，直到发生COMMIT;或ROLLBACK;.

START TRANSACTION; will basically SET autocommit=0; until a COMMIT; or ROLLBACK; takes place.

换句话说，START TRANSACTION;和SET autocommit=0;是等效的，除了START TRANSACTION;等效于在COMMIT;或ROLLBACK;

In other words, START TRANSACTION; and SET autocommit=0; are equivalent, except for the fact that START TRANSACTION; does the equivalent of implicitly adding a SET autocommit=0; after COMMIT; or ROLLBACK;

如果是这种情况，我不理解 http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_serializable -认为具有隔离级别意味着存在事务，这意味着应禁用自动提交反正吗?

If that is the case, I don't understand http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_serializable - seeing as having an isolation level implies that there is a transaction, meaning that autocommit should be off anyway?

如果开始事务和设置自动提交之间还有其他区别(上述区别除外)，那是什么?

And if there is another difference (other than the one described above) between beginning a transaction and setting autocommit, what is it?

推荐答案

了解数据库的事务(自动提交，显式和隐式)处理可以使您不必从备份中还原数据.

Being aware of the transaction (autocommit, explicit and implicit) handling for your database can save you from having to restore data from a backup.

事务控制数据操作语句以确保它们是原子的. 原子性"表示交易已发生或未发生.向数据库发出事务完成信号的唯一方法是使用COMMIT或ROLLBACK语句(根据ANSI-92，遗憾的是它不包含用于创建/开始事务的语法，因此它是特定于供应商的). COMMIT应用事务中所做的更改(如果有). ROLLBACK忽略了事务中发生的任何操作-当UPDATE/DELETE语句执行了意外操作时，这是非常可取的.

Transactions control data manipulation statement(s) to ensure they are atomic. Being "atomic" means the transaction either occurs, or it does not. The only way to signal the completion of the transaction to database is by using either a COMMIT or ROLLBACK statement (per ANSI-92, which sadly did not include syntax for creating/beginning a transaction so it is vendor specific). COMMIT applies the changes (if any) made within the transaction. ROLLBACK disregards whatever actions took place within the transaction - highly desirable when an UPDATE/DELETE statement does something unintended.

通常，单个DML(插入，更新，删除)语句在自动提交事务中执行-语句成功完成后便会提交它们.这意味着在像您这样的情况下，没有机会将数据库回滚到该语句运行之前的状态.当出现问题时，唯一可用的还原选项是从备份中重建数据(假设存在备份).在MySQL中，自动提交 对于InnoDB默认为-MyISAM不支持事务.可以通过以下方式禁用它:

Typically individual DML (Insert, Update, Delete) statements are performed in an autocommit transaction - they are committed as soon as the statement successfully completes. Which means there's no opportunity to roll back the database to the state prior to the statement having been run in cases like yours. When something goes wrong, the only restoration option available is to reconstruct the data from a backup (providing one exists). In MySQL, autocommit is on by default for InnoDB - MyISAM doesn't support transactions. It can be disabled by using:

SET autocommit = 0

显式事务是指将语句包装在显式定义的事务代码块中-对于MySQL，是START TRANSACTION .在事务结束时，还需要显式生成的COMMIT或ROLLBACK语句.嵌套事务超出了本主题的范围.

An explicit transaction is when statement(s) are wrapped within an explicitly defined transaction code block - for MySQL, that's START TRANSACTION. It also requires an explicitly made COMMIT or ROLLBACK statement at the end of the transaction. Nested transactions is beyond the scope of this topic.

隐式交易与显式交易略有不同.隐式事务不需要显式定义事务.但是，像显式事务一样，它们需要提供COMMIT或ROLLBACK语句.

Implicit transactions are slightly different from explicit ones. Implicit transactions do not require explicity defining a transaction. However, like explicit transactions they require a COMMIT or ROLLBACK statement to be supplied.

显式事务是最理想的解决方案-它们需要语句COMMIT或ROLLBACK来完成事务，并且清楚地说明了发生的情况，以便其他人在需要时阅读.如果以交互方式使用数据库，则隐式事务是可以的，但是COMMIT语句仅应在测试和测试结果后指定.彻底确定是有效的.

Explicit transactions are the most ideal solution - they require a statement, COMMIT or ROLLBACK, to finalize the transaction, and what is happening is clearly stated for others to read should there be a need. Implicit transactions are OK if working with the database interactively, but COMMIT statements should only be specified once results have been tested & thoroughly determined to be valid.

这意味着您应该使用:

SET autocommit = 0;

START TRANSACTION;
  UPDATE ...;

...并且仅在结果正确时使用COMMIT;.

...and only use COMMIT; when the results are correct.

也就是说，UPDATE和DELETE语句通常仅返回受影响的行数，而不返回特定的详细信息.将此类语句转换为SELECT语句&查看结果以确保在尝试UPDATE/DELETE语句之前 的正确性.

That said, UPDATE and DELETE statements typically only return the number of rows affected, not specific details. Convert such statements into SELECT statements & review the results to ensure correctness prior to attempting the UPDATE/DELETE statement.

DDL(数据定义语言)语句是自动提交的-它们不需要COMMIT语句. IE:表，索引，存储过程，数据库以及视图创建或更改语句.

DDL (Data Definition Language) statements are automatically committed - they do not require a COMMIT statement. IE: Table, index, stored procedure, database, and view creation or alteration statements.

这篇关于SET autocommit = 1和mysql中的START TRANSACTION之间的区别(我错过了什么吗?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！