默认情况下 , 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 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 )
参考资料:
https://dev.mysql.com/doc/refman/5.7/en/commit.html
来源: https://www.thinksaas.cn/group/topic/839164/