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， 在自动提交模式关闭（关闭隐式提交）的情况下，开启一个事务上下文。首先数据库会隐式提交之前的还未被提交的操作，同时开启一个新事务。如有不明，可以用下面小实验理解一下：
测试如下所示：
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 test.testmodel_tag where name='Game';
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 | 2019-02-21 14:56:00 | 0.0010 | 1 | delete from test.testmodel_tag where name='Game' |
+-----------+----------------------+---------------------+------------+-----------------+--------------------------------------------------+
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 | 2019-02-21 14:56:00 | 0.0010 | 1 | delete from test.testmodel_tag where name='Game' |
+-----------+----------------------+---------------------+------------+-----------------+--------------------------------------------------+
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