Turning Off Autocommit For a MySQL Client

1. Overview

A transaction in MySQL is an atomic unit of SQL statement/s that we commit or roll back as one. We can make a transaction automatic, explicit, or implicit. Further, only transactional storage engines such as InnoDB support transactions. Importantly, MySQL makes all the changes permanent when we commit a transaction. Conversely, when we roll back a transaction MySQL undoes all the changes. AUTOCOMMIT mode in MySQL is a setting that determines how local transactions are run. MySQL sets the default value of the autocommit mode to ON, which means that each SQL statement runs in its own transaction. Furthermore, a local transaction applies only to a single client session.

In this tutorial, we’ll learn about turning off the autocommit mode in MySQL. To demonstrate, we’ll use the Department table from the University database.

2. How to Find the Autocommit Mode

MySQL sets the autocommit mode using the autocommit system variable, which is a dynamic variable of type boolean, and a scope of Global or Session. MySQL starts each user session with the autocommit mode turned on, by default. We can find the autocommit mode for a client session with a simple query.

SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

As an alternative, we can use any of the variations of the same query or the SHOW VARIABLES statement:

SELECT @@SESSION.autocommit;
SELECT @@LOCAL.autocommit;

SHOW VARIABLES LIKE 'autocommit';  
SHOW SESSION VARIABLES LIKE 'autocommit';

As another option, we can query the performance_schema to find the autocommit mode for a client session:

SELECT * 
FROM performance_schema.session_variables
WHERE VARIABLE_NAME = 'autocommit';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| autocommit    | ON             |
+---------------+----------------+

Further, we can find the global autocommit mode setting that applies to all client sessions with a query:

SELECT @@GLOBAL.autocommit;
+---------------------+
| @@GLOBAL.autocommit |
+---------------------+
|                   1 |
+---------------------+

As an alternative, we can use a variation of the same query or the performance_schema to find the global autocommit mode setting:

SHOW GLOBAL VARIABLES LIKE 'autocommit';

SELECT * 
FROM performance_schema.global_variables
WHERE VARIABLE_NAME = 'autocommit';

We can override the global setting of the autocommit mode with the session setting that we choose to set in a client session.

3. How to Turn Off the Autocommit Mode

Depending on the requirements, we can turn off the autocommit mode at the transaction level, the session level, or the global level.

3.1. Transaction Level

Let’s turn off the autocommit for a single transaction. To do this, start a new transaction with START TRANSACTION, which automatically turns off the autocommit mode:

START TRANSACTION;

While the transaction is active, we can verify that the autocommit mode is off by querying the performance_schema:

SELECT THREAD_ID, 
STATE, 
TRX_ID, 
ACCESS_MODE,
ISOLATION_LEVEL, 
AUTOCOMMIT FROM
performance_schema.events_transactions_current;
+-----------+-----------+-----------------+-------------+-----------------+------------+
| THREAD_ID | STATE     | TRX_ID          | ACCESS_MODE | ISOLATION_LEVEL | AUTOCOMMIT |
+-----------+-----------+-----------------+-------------+-----------------+------------+
|        50 | ACTIVE    |            NULL | READ WRITE  | REPEATABLE READ | NO         |
+-----------+-----------+-----------------+-------------+-----------------+------------+

Indeed, the AUTOCOMMIT column lists NO for the mode.

After that, we can run SQL statements such as SELECT, INSERT, UPDATE, REPLACE, and DELETE in the active transaction. To commit the transaction, we call COMMIT, and to rollback the transaction, we call ROLLBACK:

SELECT * 
FROM DEPARTMENT;

COMMIT;

When we end the transaction with COMMIT or ROLLBACK, the auto-commit mode reverts to its setting that existed before we called START TRANSACTION. We can continue with running other SQL statements. Let’s verify that the transaction has been committed and the autocommit mode has reverted at the first SQL query against a transactional table. We do this by querying the performance_schema again:

SELECT * 
FROM DEPARTMENT;

SELECT ...
AUTOCOMMIT FROM
performance_schema.events_transactions_current;
+-----------+-----------+-----------------+-------------+-----------------+------------+
| THREAD_ID | STATE     | TRX_ID          | ACCESS_MODE | ISOLATION_LEVEL | AUTOCOMMIT |
+-----------+-----------+-----------------+-------------+-----------------+------------+
|        50 | COMMITTED | 281475294500352 | READ WRITE  | REPEATABLE READ | YES        |
+-----------+-----------+-----------------+-------------+-----------------+------------+

Indeed, the foreground thread lists the AUTOCOMMIT as YES, which means the autocommit mode has reverted to being turned on, the default for a client session.

3.2. Session Level

Let’s turn off the autocommit mode at the session level. When we do so, the autocommit mode is turned off only for a single client session, and not for the other client sessions that may be running concurrently. To demonstrate, start a client session, and turn off the autocommit mode by running one of several options:

SET autocommit = 'OFF';

SET SESSION autocommit = 'OFF';
SET LOCAL autocommit = 'OFF';
SET @@SESSION.autocommit = 'OFF';
SET @@LOCAL.autocommit = 'OFF';
SET @@autocommit = 'OFF';
SET autocommit = 'OFF';
SET autocommit = 0;

A value of 0 is equivalent to a value of OFF for each of the SET statements. The SET statement only sets the session scope value for the system variable autocommit and doesn’t start a new transaction. To start a new implicit transaction we need to run a SQL query against a transactional table, albeit a simple SELECT statement:

SELECT * 
FROM DEPARTMENT;

Afterward, we can verify that a transaction is active, and the autocommit mode is off:

SELECT ...
AUTOCOMMIT FROM
performance_schema.events_transactions_current;
+-----------+-----------+-----------------+-------------+-----------------+------------+
| THREAD_ID | STATE     | TRX_ID          | ACCESS_MODE | ISOLATION_LEVEL | AUTOCOMMIT |
+-----------+-----------+-----------------+-------------+-----------------+------------+
|        46 | ACTIVE    | 281475294500352 | READ WRITE  | REPEATABLE READ | NO         |
+-----------+-----------+-----------------+-------------+-----------------+------------+

Indeed, the transaction state is ACTIVE, and the AUTOCOMMIT column lists a value of NO.

Subsequently, we can run SQL statements as usual, but we need to call COMMIT each time we want to commit the batch of Data Manipulation Language (DML statements just run, or ROLLBACK to roll back the batch of DML statements just run. Most Data Definition Language (DDL) statements don’t require to be committed and cause an implicit commit. We can always revert it to the default autocommit mode, which’s turned on, by calling one of the statements:

SET autocommit = DEFAULT;

SET autocommit = 1;

Turning on the autocommit mode from the off state causes an implicit commit of the running transaction. In other words, if we were in the middle of running a batch of DML statements with the autocommit mode turned off, the SET autocommit=1 or SET autocommit=DEFAULT commits the implicit transaction before turning on the autocommit mode.

3.3. Global Level

Let’s turn off the autocommit mode at the global level. When we do so, the autocommit mode is turned off only for all client sessions started subsequently, and not for the currently running client sessions. To demonstrate, let’s start a client session, and turn off the global autocommit mode with one of the two SET options:

SET GLOBAL autocommit = 'OFF';

SET @@GLOBAL.autocommit = 0;

Notably, the SET statement only sets the global scope value for the system variable autocommit and doesn’t start a new transaction. Further, it doesn’t affect the current session, or the other currently running transactions, which still list their turned-on setting:

SELECT @@SESSION.autocommit;
+----------------------+
| @@SESSION.autocommit |
+----------------------+
|                    1 |
+----------------------+

However, when we start a new client session after turning off the global autocommit mode, the autocommit setting is listed as off:

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

Furthermore, turning off global autocommit mode with SET doesn’t persist across a server restart. To turn off the global autocommit mode across server restarts also, we can call either of the SET statements:

SET PERSIST autocommit = 'OFF';

SET @@PERSIST.autocommit = 0;

We’ve yet another option for turning off the autocommit mode at the global level when persisting across server restarts. We can keep the current global setting, and only turn it off for subsequent server restarts with one of the SET statements:

SET PERSIST_ONLY autocommit = 'OFF';

SET @@PERSIST_ONLY.autocommit = 0;

Thereafter, we need to restart the MySQL server for the new setting to take effect.

4. Conclusion

In this article, we learned about turning off the autocommit mode in MySQL. We can turn it off at the transaction level, session level, and global level. Further, we can make the setting persistent across server restarts. We turn off the autocommit mode using a dynamic system variable called autocommit. Accordingly, when we turn off the autocommit mode, we can choose whether a transaction is explicit, or implicit.