mysql 查看autocommit_MySQL的自动提交模式

作者：Cpp五条 | 2024-03-01 11:29:14

踩

mysql 查看 auto_commit

默认情况下, MySQL启用自动提交模式（变量autocommit为ON）。这意味着, 只要你执行DML操作的语句,MySQL会立即隐式提交事务（Implicit Commit）。这个跟SQL Server基本是类似的。如果你了解SQL Server数据库的话。

查看autocommit模式

由于变量autocommit分会话系统变量与全局系统变量，所以查询的时候，最好区别是会话系统变量还是全局系统变量。

mysql

Value的值为ON，表示autocommit开启。OFF表示autocommit关闭。

修改autocommit模式

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 
    -> 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）

（完）

声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】