MySQL的自动提交模式

默认情况下, 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'

autocommit与显性事务的关系

对于显性事务start transaction或begin，在自动提交模式关闭（关闭隐式提交）的情况下，开启一个事务上下文。首先数据库会隐式提交之前的还未被提交的操作，同时开启一个新事务。如有不明，可以用下面小实验理解一下：测试，会话1：

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）