mysql 中 SET autocommit=1 和 START TRANSACTION 之间的区别（我错过了什么？）

Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)

提问by ralokt

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.

我正在阅读 MySQL 中的事务，但不确定我是否正确掌握了某些特定内容，我想确保我正确理解了这一点，所以这里是。我知道事务应该做什么，我只是不确定我是否理解语句语义。

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

所以，我的问题是，以下有什么问题吗（如果是这样，有什么问题）：

By default, autocommit mode is enabled in MySQL.

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

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).

现在，SET autocommit=0;将开始一个事务，SET autocommit=1;将隐式提交。这是可能的COMMIT;，以及ROLLBACK;，在两个自动提交仍设置为0之后（和一个新的隐式启动事务）的情况下。

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

START TRANSACTION;将基本上SET autocommit=0;直到发生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;

换句话说，START TRANSACTION;andSET autocommit=0;是等价的，除了这样一个事实，即START TRANSACTION;隐式添加一个SET autocommit=0;afterCOMMIT;或ROLLBACK;

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?

如果是这种情况，我不明白http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_serializable- 看到隔离级别意味着存在事务，这意味着无论如何应该关闭自动提交？

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

如果开始事务和设置自动提交之间还有另一个区别（除了上述区别），那是什么？

回答by OMG Ponies

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

了解数据库的事务（自动提交、显式和隐式）处理可以使您不必从备份中恢复数据。

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 COMMITor ROLLBACKstatement (per ANSI-92, which sadly did not include syntax for creating/beginning a transaction so it is vendor specific). COMMITapplies the changes (if any) made within the transaction. ROLLBACKdisregards whatever actions took place within the transaction - highly desirable when an UPDATE/DELETE statement does something unintended.

事务控制数据操作语句以确保它们是原子的。“原子”意味着事务要么发生，要么不发生。向数据库发出事务完成信号的唯一方法是使用COMMITorROLLBACK语句（根据 ANSI-92，遗憾的是它不包含用于创建/开始事务的语法，因此它是特定于供应商的）。 COMMIT应用在交易中所做的更改（如果有）。ROLLBACK忽略事务中发生的任何操作 - 当 UPDATE/DELETE 语句执行意外操作时非常可取。

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 onby default for InnoDB- MyISAM doesn't support transactions. It can be disabled by using:

通常，单个 DML（插入、更新、删除）语句在自动提交事务中执行 - 一旦语句成功完成，它们就会被提交。这意味着在像您这样的情况下，没有机会将数据库回滚到语句运行之前的状态。当出现问题时，唯一可用的恢复选项是从备份中重建数据（假设存在）。在 MySQL 中，默认情况下 InnoDB 的自动提交是启用的- MyISAM 不支持事务。可以使用以下方法禁用它：

SET autocommit = 0

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 COMMITor ROLLBACKstatement at the end of the transaction. Nested transactions is beyond the scope of this topic.

显式事务是将语句包装在显式定义的事务代码块中 -对于 MySQL，即START TRANSACTION. 它还需要在交易结束时明确作出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 COMMITor ROLLBACKstatement to be supplied.

隐式交易与显式交易略有不同。隐式事务不需要显式定义事务。但是，与显式事务一样，它们需要提供COMMITorROLLBACK语句。

Conclusion

结论

Explicit transactions are the most ideal solution - they require a statement, COMMITor 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 COMMITstatements should only be specified once results have been tested & thoroughly determined to be valid.

显式事务是最理想的解决方案 - 它们需要一个语句COMMIT或ROLLBACK，来完成事务，并且如果需要，其他人可以清楚地阅读正在发生的事情。如果以交互方式使用数据库，隐式事务是可以的，但是COMMIT只有在结果经过测试并彻底确定有效后才应指定语句。

That means you should use:

这意味着你应该使用：

SET autocommit = 0;

START TRANSACTION;
  UPDATE ...;

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

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

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 priorto attempting the UPDATE/DELETE statement.

也就是说，UPDATE 和 DELETE 语句通常只返回受影响的行数，而不是具体的细节。将此类语句转换为 SELECT 语句并在尝试 UPDATE/DELETE 语句之前检查结果以确保正确性。

Addendum

附录

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.

DDL（数据定义语言）语句是自动提交的——它们不需要 COMMIT 语句。IE：表、索引、存储过程、数据库和视图创建或更改语句。

回答by mikl

In InnoDByou have START TRANSACTION;, which in this engine is the officialy recommended way to do transactions, instead of SET AUTOCOMMIT = 0;(don't use SET AUTOCOMMIT = 0;for transactions in InnoDBunless it is for optimizing read onlytransactions). Commit with COMMIT;.

在InnoDB 中有START TRANSACTION;，在这个引擎中这是官方推荐的事务处理方式，而不是SET AUTOCOMMIT = 0;（不要SET AUTOCOMMIT = 0;用于InnoDB 中的事务，除非它用于优化只读事务）。与COMMIT;.

You might want to use SET AUTOCOMMIT = 0;in InnoDBfor testing purposes, and not precisely for transactions.

您可能希望SET AUTOCOMMIT = 0;在InnoDB 中用于测试目的，而不是完全用于事务。

In MyISAMyou do not have START TRANSACTION;. In this engine, use SET AUTOCOMMIT = 0;for transactions. Commit with COMMIT;or SET AUTOCOMMIT = 1;(Difference explained in MyISAM example commentary below). You can do transactions this way in InnoDB too.

在MyISAM 中，您没有START TRANSACTION;. 在这个引擎中，SET AUTOCOMMIT = 0;用于交易。使用COMMIT;或提交SET AUTOCOMMIT = 1;（差异在下面的 MyISAM 示例评论中解释）。您也可以在 InnoDB 中以这种方式进行事务。

Source: http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_autocommit

来源：http: //dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_autocommit

Examples of general use transactions:

一般用途交易的例子：

/* InnoDB */
START TRANSACTION;

INSERT INTO table_name (table_field) VALUES ('foo');
INSERT INTO table_name (table_field) VALUES ('bar');

COMMIT; /* SET AUTOCOMMIT = 1 might not set AUTOCOMMIT to its previous state */

/* MyISAM */
SET AUTOCOMMIT = 0;

INSERT INTO table_name (table_field) VALUES ('foo');
INSERT INTO table_name (table_field) VALUES ('bar');

SET AUTOCOMMIT = 1; /* COMMIT statement instead would not restore AUTOCOMMIT to 1 */

回答by Marco Aurelio Curado

https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly. For example, if you need to write to table t1 and read from table t2, you can do this:

对事务表（例如 InnoDB 表）使用 LOCK TABLES 和 UNLOCK TABLES 的正确方法是使用 SET autocommit = 0（不是 START TRANSACTION）开始事务，然后是 LOCK TABLES，并且在提交事务之前不调用 UNLOCK TABLES明确地。例如，如果您需要写入表 t1 并从表 t2 读取，您可以这样做：

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

回答by RINSON KE

If you want to use rollback, then use start transactionand otherwise forget all those things, since MySQL sets autocommitto 1 by default.

如果您想使用回滚，则使用启动事务，否则忘记所有这些事情，因为 MySQLautocommit默认设置为 1。