默认情况下
, MySQL
启用自动提交模式（变量
autocommit
为
ON
）。这意味着
,
只要你执行
DML
操作的语句
,MySQL
会立即隐式提交事务（
Implicit Commit
）。这个跟
SQL Server
基本是类似的。如果你了解
SQL Server
数据库的话。
查看
autocommit
模式
由于变量
autocommit
分会话系统变量与全局系统变量，所以查询的时候，最好区别是会话系统变量还是全局系统变量。
mysql> show session variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
Value
的值为
ON
，表示
autocommit
开启。
OFF
表示
autocommit
关闭。
修改
autocommit
模式
mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show session variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> set global autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.01 sec)
mysql>
注意，上述
SQL
修改会话系统变量或全局系统变量，只对当前实例有效，如果
MySQL
服务重启的话，这些设置就会丢失，如果要永久生效，就必须在配置文件中修改系统变量。
[mysqld]
autocommit=0
不过网上还有种方式，如下所示，我在
MySQL 5.6/5.7
下测试，发现不生效，查了一下，这种方式似乎从
MySQL 5.6
开始已经不生效了，必须用
autocommit=0这种方式
替换。
[mysqld]
init_connect='SET autocommit=0'
对于显性事务
start transaction
或
begin
， 在自动提交模式关闭（关闭隐式提交）的情况下，开启一个事务上下文。首先数据库会隐式提交之前的
还未被提交的操作
，同时开启一个新事务。如有不明，可以用下面小实验理解一下：
测试如下所示：
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from MyDB.test where name='kerry';
Query OK, 1 row affected (0.00 sec)
此时在会话
2
中查看，此时可以查询到会话
ID
为
1
的事务信息， 如下所示
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 2 |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT a.trx_state,
-> b.event_name,
-> a.trx_started,
-> b.timer_wait / 1000000000000 timer_wait,
-> a.trx_mysql_thread_id blocking_trx_id,
-> b.sql_text
-> FROM information_schema.innodb_trx a,
-> performance_schema.events_statements_current b,
-> performance_schema.threads c
-> WHERE a.trx_mysql_thread_id = c.processlist_id
-> AND b.thread_id = c.thread_id;
+-----------+----------------------+---------------------+------------+-----------------+------------------------------------------+
| trx_state | event_name | trx_started | timer_wait | blocking_trx_id | sql_text |
+-----------+----------------------+---------------------+------------+-----------------+------------------------------------------+
| RUNNING | statement/sql/delete | 2018-03-23 14:55:00 | 0.0010 | 1 | delete from MyDB.test where name='kerry' |
+-----------+----------------------+---------------------+------------+-----------------+------------------------------------------+
1 row in set (0.00 sec)
如果在会话
1
当中开启显性事务，那么之前挂起的事务会自动提交，然后，你再去会话
2
当中查询，就发现之前的
DELETE
操作已经提交。
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 2 |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT a.trx_state,
-> b.event_name,
-> a.trx_started,
-> b.timer_wait / 1000000000000 timer_wait,
-> a.trx_mysql_thread_id blocking_trx_id,
-> b.sql_text
-> FROM information_schema.innodb_trx a,
-> performance_schema.events_statements_current b,
-> performance_schema.threads c
-> WHERE a.trx_mysql_thread_id = c.processlist_id
-> AND b.thread_id = c.thread_id;
+-----------+----------------------+---------------------+------------+-----------------+------------------------------------------+
| trx_state | event_name | trx_started | timer_wait | blocking_trx_id | sql_text |
+-----------+----------------------+---------------------+------------+-----------------+------------------------------------------+
| RUNNING | statement/sql/delete | 2018-03-23 14:55:00 | 0.0010 | 1 | delete from MyDB.test where name='kerry' |
+-----------+----------------------+---------------------+------------+-----------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT a.trx_state,
-> b.event_name,
-> a.trx_started,
-> b.timer_wait / 1000000000000 timer_wait,
-> a.trx_mysql_thread_id blocking_trx_id,
-> b.sql_text
-> FROM information_schema.innodb_trx a,
-> performance_schema.events_statements_current b,
-> performance_schema.threads c
-> WHERE a.trx_mysql_thread_id = c.processlist_id
-> AND b.thread_id = c.thread_id;
Empty set (0.00 sec)
With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state
使用
START TRANSACTION
，自动提交将保持禁用状态，直到你使用
COMMIT
或
ROLLBACK
结束事务。 自动提交模式然后恢复到之前的状态（如果
start transaction
前
autocommit = 1
，则完成本次事务后
autocommit
还是
1
。如果
start transaction
前
autocommit = 0
，则完成本次事务后
autocommit
还是
0
）
