MySQL默認(rèn)是開(kāi)啟自動(dòng)提交的，即每一條DML（增刪改）語(yǔ)句都會(huì)被作為一個(gè)單獨(dú)的事務(wù)進(jìn)行隱式提交。如果修改為關(guān)閉狀態(tài)，則執(zhí)行DML語(yǔ)句之后要手動(dòng)提交 才能生效。查詢當(dāng)前會(huì)話的自動(dòng)提交是否開(kāi)啟：

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

查詢?nèi)值淖詣?dòng)提交是否開(kāi)啟：

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

通過(guò)修改autocommit變量可以關(guān)閉和開(kāi)啟操作

關(guān)閉當(dāng)前會(huì)話的自動(dòng)提交模式mysql> set autocommit=0; mysql> show variables like ’autocommit’;+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | OFF |+---------------+-------+ 全局的autocommit還是開(kāi)啟狀態(tài)mysql> show global variables like ’autocommit’;+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+ 關(guān)閉全局的autocommitmysql> set global autocommit=0; mysql> show global variables like ’autocommit’;+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | OFF |+---------------+-------+

如果想要MySQL服務(wù)重啟之后仍能生效，需要設(shè)置系統(tǒng)環(huán)境變量。MySQL5.7 在cnf配置文件中[mysqld]下面設(shè)置autocommit的值。

[mysqld]...autocommit=0Spring中對(duì)自動(dòng)提交的控制

MySQL的JDBC驅(qū)動(dòng)包 mysql-connector-java 會(huì)給會(huì)話的connection默認(rèn)開(kāi)啟自動(dòng)提交，譬如 mysql-connector-java-8.0.22版本的代碼：

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

常用的數(shù)據(jù)庫(kù)連接池 如HikariCP,druid等，默認(rèn)也是開(kāi)啟自動(dòng)提交，會(huì)將connection的自動(dòng)提交設(shè)置都改為true。druid在初始化DataSource的時(shí)候設(shè)置connection的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設(shè)置為true conn.setAutoCommit(defaultAutoCommit); } ... }

HikariCP 初始化DataSource的默認(rèn)配置 中autocommit也是true：

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

對(duì)于事務(wù)管理器PlatformTransactionManager管理的顯式事務(wù)（譬如@Transactional注解聲明）在 開(kāi)啟事務(wù)時(shí)會(huì)關(guān)閉自動(dòng)提交模式。 代碼如下：

@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');}//關(guān)閉自動(dòng)提交模con.setAutoCommit(false);} .......}catch (Throwable ex) { .......}}總結(jié)

MySQL的autocommit模式默認(rèn)是打開(kāi)狀態(tài)，為了防止手動(dòng)的DML操作導(dǎo)致失誤，生產(chǎn)環(huán)境可以設(shè)置為默認(rèn)關(guān)閉的狀態(tài)。一般的jdbc 連接池默認(rèn)都是開(kāi)啟狀態(tài)，而且是可配置的。顯式事務(wù)下會(huì)設(shè)置成關(guān)閉狀態(tài)，單純的修改數(shù)據(jù)庫(kù)環(huán)境的autocommit不會(huì)對(duì)代碼的行為產(chǎn)生影響。

以上就是詳解MySQL與Spring的自動(dòng)提交(autocommit)的詳細(xì)內(nèi)容，更多關(guān)于MySQL 自動(dòng)提交(autocommit)的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！