详解MySQL与Spring的自动提交(autocommit)

1 MySQL的autocommit设置

MySQL默认是开启自动提交的，即每一条DML（增删改）语句都会被作为一个单独的事务进行隐式提交。如果修改为关闭状态，则执行DML语句之后要手动提交才能生效。
查询当前会话的自动提交是否开启：

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

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

关闭当前会话的自动提交模式mysql> set autocommit=0; mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit  | OFF  |+---------------+-------+ 全局的autocommit还是开启状态mysql> show global variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit  | ON  |+---------------+-------+  关闭全局的autocommitmysql> set global autocommit=0; mysql> show global variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit  | OFF  |+---------------+-------+

[mysqld]...autocommit=0

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

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) {      //将connection的autocommit设置为true      conn.setAutoCommit(defaultAutoCommit);    }    ...   }

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

@Overrideprotected 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");}                //关闭自动提交模                con.setAutoCommit(false);}      .......}catch (Throwable ex) {     .......}}

1 MySQL的autocommit设置

更多相关文章

随机推荐