MySQL SET autocommit

By:
On:
In: MySQL
Tagged: ,

2013-05-27_141127

这是MySQL 5实力养成暨评量里的8-54.‘在MySQL中要启动交易(transaction)功能，下列指令何者正确？’

答案：(D) SET AUTOCOMMIT = 0

[adsense][/adsense]

这实在是很诡异？上次在乖乖牌资料库发生了人间交易惨剧提过，但是好像没有分享很透彻，而且这一个题意还真让人看不懂，真不知是ㄚ琪的语文造诣有问题，还是出题者的中文有问题，只能说大家小心了，今天我们再详细分享手册这一节的所有部份，并且好好地思索出题者的原意是怎样。

册的MySQL 5.7 Reference Manual :: 13 SQL Statement Syntax :: 13.3 MySQL Transactional and Locking Statements :: 13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax的说明

或是简体中文版的MySQL 5.1参考手册::13. SQL语句语法::13.4. MySQL事务处理和锁定语句::13.4.1. START TRANSACTION, COMMIT和ROLLBACK语法

转译繁体如下：

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

START TRANSACTION或BEGIN语句可以开始一项新的交易。COMMIT可以提交当前交易，是变更成为永久变更。ROLLBACK可以 回滚当前交易，取消其变更。SET AUTOCOMMIT语句可以禁用或启用预设的autocommit模式，用于当前连接。

自选的WORK关键词被支援，用于COMMIT和RELEASE，与CHAIN和RELEASE子句。CHAIN和RELEASE可以被用于对交易完成进行附加控制。Completion_type系统变数的值决定了预设完成的性质。请参见5.3.3节，“伺服器系统变数”。

AND CHAIN子句会在当前交易结束时，立刻启动一个新交易，并且新交易与刚结束的交易有相同的隔离等级。RELEASE子句在终止了当前交易后，会让伺服器中断与当前客户端的连接。包含NO关键词可以抑制CHAIN或RELEASE完成。如果completion_type系统变数被设置为一定的值，使连锁或释放完成可以预设进行，此时NO关键词有用。

预设情况下，MySQL采用autocommit模式运行。这意味着，当您执行一个用于更新（修改）资料表的语句之后，MySQL立刻把更新储存到磁盘中。

如果您正在使用一个交易安全型的储存引擎（如InnoDB, BDB或NDB丛集），则您可以使用以下语句禁用autocommit模式：

SET AUTOCOMMIT=0;

通过把AUTOCOMMIT变数设置为零，禁用autocommit模式之后，您必须使用COMMIT把变更储存到磁盘中，或着如果您想要忽略从交易开始进行以来做出的变更，使用ROLLBACK。

如果您想要对于一个单一系列的语句禁用autocommit模式，则您可以使用START TRANSACTION语句：

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

使用START TRANSACTION，autocommit仍然被禁用，直到您使用COMMIT或ROLLBACK结束交易为止。然后autocommit模式恢复到原来的状态。

BEGIN和BEGIN WORK被作为START TRANSACTION的别名受到支援，用于对事务进行初始化。START TRANSACTION是标准的SQL语法，并且是启动一个ad-hoc交易的推荐方法。BEGIN语句与BEGIN关键词的使用不同。BEGIN关键词可以启动一个BEGIN…END复合语句。后者不会开始一项交易。请参见20.2.7节，“BEGIN … END复合语句”。

您也可以按照如下方法开始一项交易：

START TRANSACTION WITH CONSISTENT SNAPSHOT;

WITH CONSISTENT SNAPSHOT子句用于启动一个一致的读取，用于具有此类功能的储存引擎。目前，该子句只适用于InnoDB。该子句的效果与发布一个START TRANSACTION，后面跟一个来自任何InnoDB资料表的SELECT的效果一样。请参见15.2.10.4节，“一致的非锁定读”。

开始一项交易会造成一个隐含的UNLOCK TABLES被执行。

为了获得最好的结果，交易应只使用由单一交易储存引擎管理的资料表执行。否则，会出现以下问题：

·         如果您使用的资料表来自多个交易安全型储存引擎（例如InnoDB和BDB），并且交易隔离等级不是SERIALIZABLE，则有可能当一个交易提交时，其它正在进行中的、使用同样的资料表的交易将只会发生由第一个交易产生的变更。也就是，用混合引擎不能保证交易的原子性，并会造成不一致。（如果混合引擎交易不经常有，则您可以根据需要使用SET TRANSACTION ISOLATION LEVEL把隔离等级设置到SERIALIZABLE。）

·         如果您在交易中使用非交易安全型资料表，则对这些资料表的任何变更被立刻储存，不论autocommit模式的状态如何。

如果您在更新了交易中一个交易资料表之后，发布一个ROLLBACK语句，则会出现一个ER_WARNING_NOT_COMPLETE_ROLLBACK警告。对交易安全型资料表的变更被回滚，但是对非交易安全型资料表没有变更。

每个交易被储存在一个组块中的二进制日志中，在COMMIT之上。被回滚的交易不被计入日志。（例外情况：对非交易资料表的更改不会被 回滚。如果一个被回滚的交易包括对非交易资料表的更改，则整个交易使用一个在末端的ROLLBACK语句计入日志，以确保对这些资料表的更改进行复制。）见5.11.3节，“二进制日志”。

您可以使用SET TRANSACTION ISOLATION LEVEL更改交易的隔离等级。请参见13.4.6节，“SET TRANSACTION语法”。

回滚可以慢速运行。在用户没有明确要求时，也可以进行回滚（例如，当错误发生时）。因此，在明确地和隐含的（ROLLBACK SQL命令）回滚时，SHOW PROCESSLIST会在Stage列中显示Rolling back，用于连接。

Comments are closed.