Detailed explanation of MySQL and Spring's autocommit

1 MySQL autocommit settings

MySQL automatically commits by default, which means that each DML (add, delete, and modify) statement will be implicitly committed as a separate transaction. If the status is changed to closed, the DML statement must be manually submitted for it to take effect.
Check whether the auto-commit of the current session is enabled:

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+

Check whether global auto-commit is enabled:

mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+

You can turn the operation off and on by modifying the autocommit variable

Turn off the autocommit mode for the current session mysql> set autocommit=0;

 
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+

 The global autocommit is still enabled mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+

 
 Disable global autocommit
mysql> set global autocommit=0;

 
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+

If you want the MySQL service to remain effective after restarting, you need to set system environment variables. MySQL 5.7 Set the autocommit value under [mysqld] in the cnf configuration file.

[mysqld]
...
autocommit=0

Controlling automatic submission in Spring

The MySQL JDBC driver package mysql-connector-java will enable auto-commit for the session connection by default, for example, the code of mysql-connector-java-8.0.22 version:

//com.mysql.cj.protocol.a.NativeServerSession.java
  private boolean autoCommit = true;

Commonly used database connection pools such as HikariCP, druid, etc., also enable auto-commit by default, and will change the connection auto-commit settings to true.
Druid sets the connection's autocommit to true when initializing the DataSource. The code is as follows:

com.alibaba.druid.pool.DruidAbstractDataSource.java
  protected volatile boolean defaultAutoCommit = true;
  ...
  public void initPhysicalConnection(Connection conn, Map<String, Object> variables, Map<String, Object> globalVariables) throws SQLException {
    if (conn.getAutoCommit() != defaultAutoCommit) {
      //Set the connection's autocommit to true
      conn.setAutoCommit(defaultAutoCommit);
    }
    ...
 
  }

HikariCP initializes DataSource with autocommit set to true by default:

com.zaxxer.hikari.HikariConfig.java
  public HikariConfig()
  {
   ...
   isAutoCommit = true;
  }

For explicit transactions managed by the PlatformTransactionManager transaction manager (such as @Transactional annotation declarations), the auto-commit mode is turned off when the transaction is opened. The code is as follows:

	@Override
	protected void doBegin(Object transaction, TransactionDefinition definition) {
		DataSourceTransactionObject txObject = (DataSourceTransactionObject) transaction;
		Connection con = null;

		try {
      		........

			// Switch to manual commit if necessary. This is very expensive in some JDBC drivers,
			// so we don't want to do it unnecessarily (for example if we've explicitly
			// configured the connection pool to set it already).
			if (con.getAutoCommit()) {
				txObject.setMustRestoreAutoCommit(true);
				if (logger.isDebugEnabled()) {
					logger.debug("Switching JDBC Connection [" + con + "] to manual commit");
				}
                //Turn off the auto-commit mode con.setAutoCommit(false);
			}

      		.......
		}

		catch (Throwable ex) {
     		.......
		}
	}

Summarize

The autocommit mode of MySQL is turned on by default. To prevent errors caused by manual DML operations, the production environment can be set to the default closed state. Generally, jdbc connection pools are enabled by default and are configurable. In an explicit transaction, it will be set to the closed state. Simply modifying the autocommit of the database environment will not affect the behavior of the code.

The above is a detailed explanation of MySQL and Spring's autocommit. For more information about MySQL autocommit, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

A brief analysis of SQL examples for finding uncommitted transactions in MySQL
Detailed example of how to implement transaction commit and rollback in mysql
How to find out uncommitted transaction information in MySQL
Detailed explanation of Mysql transaction isolation level read commit
Exploring the impact of indexes and commit frequency on InnoDB table write speed in MySQL
Solution to many spaces when PHP submits textarea data to MySQL
MySQL implements transaction commit and rollback examples
Python connects to MySQL and submits MySQL transaction example
JSP + MySQL Chinese garbled characters problem post submission garbled characters solution

<<: XHTML Getting Started Tutorial: Commonly Used XHTML Tags

>>: Detailed explanation of the deep and shallow cloning principles of JavaScript arrays and non-array objects