Autocommit Mode in MySQL

1. Overview

Autocommit mode in MySQL is a setting that determines how local transactions are run. Specifically, automatically committing means each SQL statement runs in its own transaction. Notably, a local transaction applies only to a single client session.

In this tutorial, we’ll learn about the role of the autocommit mode in MySQL. A transaction can be automatic, explicit, or implicit. We’ll discuss each of these types of transactions in the context of the automatic commit mode.

To demonstrate, we’ll use the Department table from the University database.

2. Transactions

A transaction in MySQL is an atomic unit of SQL statements that can be committed or rolled back as one.

Importantly, when a transaction is committed, all the changes are made permanent. On the other hand, rolling back a transaction means all the changes are undone.

Critically, only transactional storage engines such as InnoDB support transactions.

3. The autocommit System Variable

We toggle automatic commit mode in MySQL via a system variable called autocommit. Each new MySQL session or connection starts with autocommit mode enabled by default.

Let’s start a new session and find the value of the variable:

SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

A value of ON means that each SQL statement runs within a separate transaction. This is the automatic transaction mode.

Even so, with autocommit enabled, we can run multiple SQL statements in a single transaction by grouping them within a pair of statements: START TRANSACTION and COMMIT or ROLLBACK.

The START TRANSACTION statement disables the autocommit mode. Further, it stays turned off within the user-managed transaction block. However, the automatic commit mode reverts to its previous state after COMMIT or ROLLBACK is run. Thus, we can use the COMMIT, and ROLLBACK SQL transaction management statements to run an explicit transaction. However, we can’t nest explicit transactions.

4. Setting the Autocommit Mode

We set the autocommit mode using the autocommit variable, which is a dynamic system variable of type boolean and a scope of Global or Session. In particular, we can set the value at runtime with the SET statement without having to stop and restart the server.

In general, we can set its value using one of three methods:

• SET statement
• option file
• command line

Let’s see each method in action.

To set the value of the autocommit variable with a SET statement in a MySQL session, we just use a basic statement:

SET SESSION autocommit = 'OFF';

Likewise, we can set the autocommit system variable in an option file (/etc/my.cnf or similar, depending on the OS and platform):

[mysqld]
autocommit=0

Additionally, we can set the value on the command line:

$ mysqld --autocommit=OFF

MySQL best practices recommend that we turn off autocommit mode when we want to run multiple SQL statements in a single transaction. We can turn off the automatic commit mode using one of the methods we just discussed, or by running START TRANSACTION to start an explicit transaction. However, keeping autocommit turned on can improve performance for read-only transactions.

5. Implicit Transactions

MySQL automatically starts an implicit transaction at the first SQL statement against a transactional table after we turn off the autocommit mode.

Let’s demonstrate an implicit transaction by first turning off autocommit mode with one of the three methods discussed.

Now, we verify it’s in the OFF state:

SET autocommit=0;
SHOW VARIABLES 
LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+

Afterward, let’s run a simple query on a table:

SELECT * 
FROM Department;

Accordingly, MySQL starts an implicit transaction to run the SQL query. To verify that a transaction is active, let’s try to use SET TRANSACTION:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress

Indeed, as the error message indicates, a transaction is in progress.

Subsequent sections explore how the COMMIT, and ROLLBACK statements can be used with an implicit transaction.

6. Using COMMIT

The COMMIT SQL statement makes changes within a transaction permanent.

6.1. Example

Let’s turn the autocommit mode off, and add new data to the default data set:

SET autocommit = 0;

INSERT 
INTO Department 
VALUES(6,'Data Science','DS');

INSERT 
INTO Department 
VALUES(7,'Electrical Engineering','EE');

Afterward, we commit the implicit transaction:

COMMIT;

Hence, the new data is now visible to all users and sessions:

SELECT * 
FROM Department;
+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Electronics and Communications | EC   |
|  3 | Mechanical Engineering         | ME   |
|  4 | Civil Engineering              | CE   |
|  5 | Mathematics                    | MA   |
|  6 | Data Science                   | DS   |
|  7 | Electrical Engineering         | EE   |
+----+--------------------------------+------+

Further, the COMMIT statement doesn’t change the autocommit mode:

SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+

Indeed, the automatic commit mode is still turned off.

6.2. Optional Clauses

Let’s demonstrate how we can use COMMIT AND CHAIN to commit one implicit transaction and chain or start a new implicit transaction:

SET autocommit=0; 
/* SQL 
   Statements */ 
COMMIT AND CHAIN;

Accordingly, the database engine starts a new transaction after the first one is committed.

To verify, let’s try to use SET TRANSACTION, only to get an error message:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress

Further, let’s use the RELEASE clause to disconnect the active user session after committing an implicit transaction:

SET autocommit=0;   
/* SQL 
   Statements */ 
COMMIT RELEASE;

To verify, we run a SELECT query to find the autocommit mode setting:

SELECT @@autocommit;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    10
Current database: test

+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

Because COMMIT RELEASE has already released the active session, this action disconnects the client from the server. MySQL starts a new session with the default autocommit setting of 1.

7. Using ROLLBACK

The ROLLBACK statement undoes all the changes made within a transaction.

Let’s delete all data from the default data set after turning off the autocommit mode:

SET autocommit=0; 

DELETE 
FROM Department;

Because we’re in an implicit transaction, we can roll it back:

ROLLBACK;

The ROLLBACK undoes all the changes.

Let’s run a SELECT query to verify:

SELECT * 
FROM Department;
+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Electronics and Communications | EC   |
|  3 | Mechanical Engineering         | ME   |
|  4 | Civil Engineering              | CE   |
|  5 | Mathematics                    | MA   |
+----+--------------------------------+------+

Indeed, the output still lists the default data set.

8. Implicit Commit

Some SQL statements implicitly commit an active transaction. Such statements include the Data Definition Language (DDL) statements. Some transaction-control statements such as SET AUTOCOMMIT, and START TRANSACTION also cause an implicit commit.

Once again, let’s turn the autocommit mode off and add new data to the default data set:

SET autocommit = 0;

INSERT 
INTO Department 
VALUES(6,'Data Science','DS');

This time, let’s not run COMMIT or ROLLBACK. Instead, we run SET AUTOCOMMIT to turn on the autocommit mode:

SET autocommit = 1;

The statement SET autocommit = 1 implicitly commits the running transaction. As a result, it adds the new row of data permanently.

9. Conclusion

In this article, we learned about the autocommit mode in MySQL. We can set it using a dynamic system variable called autocommit. Thus, we can use the mode to choose whether a transaction is automatic, explicit, or implicit.