How to Disable MySQL Autocommit for Faster Bulk Inserts

MySQL’s InnoDB engine enables autocommit by default, causing each DML statement to issue an implicit COMMIT, which can significantly degrade performance during bulk data loads.

During an Oracle‑to‑MySQL migration, inserting a table of over 4,000 rows took more than 100 seconds because MySQL committed after every row.

Ways to disable autocommit

Two methods are commonly used: (1) issuing SET statements to change the session or global variable, which reverts after a server restart; (2) adding autocommit=0 to the MySQL configuration file ( my.ini) for a permanent change.

Test environment

Windows Server 2008 R2 with MySQL Community Server 5.7.16 (GPL).

mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64)
Connection id: 2
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.7.16 MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 9 min 5 sec
Threads: 1  Questions: 7  Slow queries: 0  Opens: 106  Flush tables: 1  Open tables: 99  Queries per second avg: 0.012
--------------

Method 1 – Using SET

Check the current value:

mysql> show global variables like '%commit%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| autocommit                  | ON    |
| binlog_group_commit_sync_delay| 0    |
| ... (other variables)        | ...   |
+------------------------------+-------+

Disable autocommit for the current session and globally:

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global autocommit=0;
Query OK, 0 rows affected (0.00 sec)

Verify the change:

mysql> show global variables like '%commit%';
| autocommit | OFF |

After restarting MySQL, the variable returns to its default ON state.

Method 2 – Editing my.ini

Locate the my.ini file and add the line autocommit=0 under the [mysqld] section:

[mysql]
default-character-set=utf8
[mysqld]
max_connections=200
default-storage-engine=INNODB
basedir=E:\mysql-5.7.16-winx64\bin
datadir=E:\mysql-5.7.16-winx64\data
port=3306
autocommit=0

Restart the MySQL service and confirm the setting:

mysql> show global variables like '%commit%';
| autocommit | OFF |

Performance impact

With autocommit disabled, inserting the same 4,000‑plus rows took only 8,869 ms instead of 101,505 ms, delivering more than a ten‑fold speed increase.