mysql autocommit=0手动提交

mysql autocommit=0手动提交

If autocommit mode is disabled within a session with SET autocommit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.

If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

大概意思是说，当开启手动提交事务时，一次的 commit 或者 rollback 的结束，都会结束当前事务，并开启新的一个事务

mysql> set autocommit=0;                             # !设置手动提交事务时，自动创建一个事务
Query OK, 0 rows affected (0.03 sec)
 
mysql> SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX;
+-----------------+
| TRX_ID          |
+-----------------+
| 421657304808856 |
+-----------------+
1 row in set (0.00 sec)
 
mysql> select * from t_dict;
+----+------+-----+
| id | name | val |
+----+------+-----+
|  1 | test |   1 |
+----+------+-----+
1 row in set (0.00 sec)
 
mysql> SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX;
+-----------------+
| TRX_ID          |
+-----------------+
| 421657304808856 |
+-----------------+
1 row in set (0.00 sec)
 
mysql> commit;                                   # commit结束后，查询事务id为空
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX;     
Empty set
 
mysql> select * from t_dict;
+----+------+-----+
| id | name | val |
+----+------+-----+
|  1 | test |   1 |
+----+------+-----+
1 row in set (0.00 sec)
 
mysql> SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX;  # 又重新打开一个事务，并且事务id和之前相同
+-----------------+
| TRX_ID          |
+-----------------+
| 421657304808856 |
+-----------------+
1 row in set (0.00 sec)