Difference Between SET autocommit=1 and START TRANSACTION in MySQL

1. Overview

MySQL runs all SQL statements within transactions by default. A transaction in MySQL is an atomic unit of SQL statements that commits or rolls back as one. However, only a transactional storage engine such as InnoDB supports transactions. Furthermore, a transaction can be automatic, explicit, or implicit. The default transaction mode is automatic. Additionally, the AUTOCOMMIT mode, with a default setting of ON, determines how local transactions are run. Accordingly, MySQL (InnoDB) runs each SQL statement in a separate transaction that a user doesn’t start or end explicitly.

We can use the SET autocommit=1 and START TRANSACTION statements in MySQL to control transactions.

In this tutorial, we’ll learn about the differences between these two statements. To demonstrate, we’ll use the Department table from the University database.

2. SET autocommit=1

The SET autocommit=1 is a transaction control statement. It enables the autocommit mode within a user/client session. The scope of SET autocommit=1 is the user/client session in which it’s run. However, we can use different autocommit mode settings in different mysql shell sessions within the same user connection to the MySQL server.

Additionally, we can use any of its equivalent forms:

SET autocommit=1;
SET autocommit = DEFAULT;

SET autocommit='ON';
SET @@autocommit = 'ON';

SET SESSION autocommit = 'ON';
SET @@SESSION.autocommit = 'ON';

SET LOCAL autocommit = 'ON';
SET @@LOCAL.autocommit = 'ON';

When we run SET autocommit=1, MySQL sets the session value of the system variable autocommit to ON. Notably, MySQL takes a value of ON to be equivalent to 1 in each of these SET statements.

2.1. Purpose

We can use the SET autocommit=1 statement to enable or turn on the autocommit mode. However, it doesn’t start a transaction by itself. Even so, the SET autocommit=1 causes an implicit commit of the active transaction, if any.

2.2. Use Case

We don’t need to call the SET autocommit=1 statement when the autocommit mode is already turned ON. In other words, we don’t need to use this statement under default settings. Therefore, we should determine the current autocommit mode with a simple query:

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

The main use case is that when we turn off the autocommit mode, we can turn it back on with the SET autocommit=1 statement. Afterward, MySQL runs each SQL statement in a separate transaction. We typically turn on the autocommit mode when we need to run a Data Definition Language (DDL) statement or a single Data Manipulation Language (DML) statement:

SET autocommit=1;

CREATE TABLE Department
(
    id INT PRIMARY KEY NOT Null,
    name VARCHAR (50),
    code VARCHAR (4),
    UNIQUE (id)
); -- Runs in a separate transaction

SELECT SELECT * FROM DEPARTMENT; --Runs in a separate transaction

INSERT INTO Department VALUES(6,'Data Science','DS'); --Runs in a separate transaction

Accordingly, MySQL runs each transactional SQL statement as if it were run within the pair of statements START TRANSACTION and COMMIT or ROLLBACK. Accordingly, the transaction engine commits a transaction if it succeeds. Conversely, the transaction engine rolls back a transaction when it fails.

Yet, we can’t use SET autocommit=1 with non-transactional SQL statements such as SELECT 1. Data Manipulation Language (DML) statements, for example, INSERT and UPDATE, against a non-transactional storage engine such as MyISAM are also non-transactional.

3. START TRANSACTION

The START TRANSACTION is also a transaction-control statement. MySQL defines the syntax of the START TRANSACTION statement to include optional transaction characteristics such as the access mode. The scope of START TRANSACTION is a single transaction. However, we can’t nest transactions. In other words, we can’t run a START TRANSACTION within another transaction started with a START TRANSACTION. As a result, when we do so, the first transaction commits implicitly and a second transaction gets started.

3.1. Purpose

We can use the START TRANSACTION statement to start a new user-managed transaction. Furthermore, we can set the access mode to READ WRITE (default) or READ ONLY. We use the access mode READ ONLY to make read-only queries in a transaction. However, we can’t set transaction characteristics once a transaction has started.

3.2. Use Case

Typically, we use the START TRANSACTION statement when the autocommit mode is in the ON, or enabled, state. Thus, we run this statement to start an explicit transaction that disables the autocommit functionality temporarily. Herein lies the main difference between the two. We can run only one SQL statement per transaction when we enable the autocommit mode with SET autocommit=1. However, we can run multiple SQL statements that don’t cause an implicit commit in a single transaction when we start the transaction with START TRANSACTION:

START TRANSACTION; --Starts a single transaction

/* SQL Statements*/
SELECT SELECT * FROM DEPARTMENT;
INSERT INTO Department VALUES(6,'Data Science','DS');
INSERT INTO Department VALUES(7,'Computer Engineering','CE');
UPDATE Department SET name = 'Math' WHERE id=5;  

COMMIT; -- or ROLLBACK - Ends the single transaction

We can commit the transaction with COMMIT, or roll it back with ROLLBACK. Importantly, when we commit a transaction, MySQL makes all the changes permanent. Conversely, when we roll back a transaction, MySQL undoes all the changes.

Further, when we end the transaction with COMMIT or ROLLBACK, the autocommit mode reverts to being enabled, which is the default. However, let’s suppose a user has disabled the autocommit mode with SET autocommit=0, or one of its equivalent forms, before calling START TRANSACTION. In that case, the autocommit mode reverts to the turned-off state after the transaction started with START TRANSACTION ends.

4. SET autocommit=1 vs. START TRANSACTION

Let’s now summarize all the aspects of usage:

5. Conclusion

In this article, we learned about the differences between two SQL statements SET autocommit=1 and START TRANSACTION, which are transaction-control statements. The main difference between the two is their purpose. The SET autocommit=1 enables the autocommit mode, whereas START TRANSACTION starts a new transaction.