Which SQL Statements Require a Commit in MySQL

1. Overview

MySQL runs all SQL inside a transaction when we use the default storage engine InnoDB, which sets the scope of this tutorial. There, we can make a transaction automatic, explicit, or implicit.

In this tutorial, we’ll learn about the SQL statements that require an explicit commit in MySQL. More precisely, we’ll learn about the statements that require an explicit commit of the transaction in which they run. To demonstrate, we’ll use the Department table from the University database.

First, though, let’s take a look at what we mean by implicit and automatic commit.

2. Implicit Commit

We can run one or more SQL statements in a single transaction by grouping them within the pair of statements START TRANSACTION and COMMIT or ROLLBACK. However, we don’t need to explicitly commit all SQL statements. This is because some SQL statements commit implicitly after they run.

Note that PostgreSQL and SQL Server do not support implicit commits. In other words, all SQL statements need a user to commit the transaction either manually or through an automatic commit.

3. Automatic Commit

A transaction in MySQL is an atomic unit of SQL statements that we can commit or roll back as one. Further, when we commit a transaction, all the changes are made permanent. Conversely, when we roll back a transaction, all the changes are undone.

AUTOCOMMIT mode in MySQL is a setting that determines how MySQL runs local transactions. Notably, MySQL applies a local transaction to a single client session. MySQL sets the default value of the AUTOCOMMIT mode to 1, which runs each SQL statement in its own transaction that commits automatically after the statement has run. Therefore, we don’t need to commit any transaction.

However, we can turn off the autocommit mode by running one of the two SQL statements independently:

START TRANSACTION;

SET AUTOCOMMIT=0;

When we turn off the AUTOCOMMIT mode, we must commit, or rollback, each transaction for some SQL statements in MySQL.

Subsequent sections explore the salient SQL statements that require a user to commit the transaction when AUTOCOMMIT mode is turned off in MySQL.

4. SELECT

A SELECT statement against a transactional table typically doesn’t make any database changes. When we run a simple consistent read, we don’t need to perform an explicit commit. A transactional table is a table in a transactional storage engine. As an example, InnoDB has transactional tables, but MyISAM has non-transactional tables.

However, we can’t receive changes made in other user sessions to the same table until we commit the first user’s transaction. InnoDB presents a user with the same data set each time the same read query is run in a transaction within a session.

In other words, the read is consistent. Meanwhile, other user sessions may make database changes to the same table, and MySQL does not present the new data in a read snapshot already established for a query run in the first user session.

Let’s demonstrate this with an example. Accordingly, let’s start a new user session and run a read query to return the default data set:

//First user session
START TRANSACTION;
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   |
+----+--------------------------------+------+

Alongside, let’s start a second user session and add new data to the default data set:

//Second user session
START TRANSACTION;
INSERT 
INTO Department 
VALUES(6,'Data Science','DS');

Let’s run the same read query in the first user session. The read query does not return the new data added in the second user session:

//First user session
SELECT * 
FROM Department;
-- Default data set returned

Let’s commit the transaction in the second user session:

//Second user session
COMMIT;

A read query in the first user session still does not return the new data added in the second user session:

//First user session
SELECT * 
FROM Department;
-- Default data set returned

Let’s commit the transaction in the first user session:

//First user session
COMMIT;

Afterward, let’s again query the same table in the first user session:

//First user session
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   |
+----+--------------------------------+------+

This time, it returns the new data.

5. CREATE TEMPORARY TABLE

Most of the Data Definition Language (DDL) statements that define or modify database objects cause an implicit commit of the active transaction before and after they run. These SQL statements include the CREATE TABLE, ALTER TABLE, and DROP TABLE.

However, two exceptions to this exist. A user needs to explicitly commit the transaction in which one or both of the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE run. Let’s show this with an example:

START TRANSACTION;

CREATE TEMPORARY TABLE tbl1 
    SELECT * 
    FROM Department;

DROP TEMPORARY TABLE tbl1;

COMMIT;

Although these statements don’t cause an implicit commit, the DDL change caused by these statements can’t be undone with a ROLLBACK statement. In other words, even if we run ROLLBACK to roll back the transaction, MySQL will still create the temporary table with CREATE TEMPORARY TABLE and drop it with DROP TEMPORARY TABLE.

6. INSERT

We can add data to a table using the Data Manipulation Language (DML) statement INSERT. We need to explicitly commit the transaction to add the data permanently. To demonstrate, let’s add two rows of data to the default data set in a user-managed transaction, and commit:

START TRANSACTION;

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

INSERT 
INTO Department 
VALUES(7,'Computer Engineering','CE');

COMMIT;

Let’s verify it adds new data:

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 | Computer Engineering           | CE   |
+----+--------------------------------+------+

Indeed, it adds the new data.

7. UPDATE

We can update data in a table using the DML statement UPDATE. We need to explicitly commit the transaction to update the data permanently. To demonstrate, let’s update a row of data in the default data set, and commit the user-managed transaction:

START TRANSACTION;

UPDATE Department 
SET code ='DSc' 
WHERE id=6;

COMMIT;

Afterward, let’s verify it updates a row of data:

SELECT * FROM Department WHERE id=6;
+----+--------------+------+
| id | name         | code |
+----+--------------+------+
|  6 | Data Science | DSc  |
+----+--------------+------+

Indeed, it updates the data.

8. REPLACE

We can replace data in a table using the DML statement REPLACE. We need to explicitly commit the transaction to replace the data permanently. The difference between REPLACE and UPDATE is that it replaces any existing row with the same PRIMARY KEY or UNIQUE KEY instead of updating it. To demonstrate, let’s replace the row of data with id 5 in the default data set, and commit:

START TRANSACTION;

REPLACE 
INTO Department 
VALUES (5, 'Math', 'MA');

COMMIT;

Let’s verify it replaces a row of data:

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

Indeed, it replaces the data. REPLACE performs two key operations. It first deletes a row, and subsequently adds the replacement row.

9. DELETE

We can delete data from a table using the DML statement DELETE. We need to explicitly commit the transaction to delete the data permanently. To demonstrate, let’s delete two rows of data from the default data set:

START TRANSACTION;

DELETE 
FROM Department 
WHERE id=4 OR id=5;

COMMIT;

Let’s verify it deletes the two rows of data:

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

Indeed, it deletes the data.

10. Other Statements

A user needs to commit all other statements that do not cause an implicit commit. These statements include DML statements CALL, DO, HANDLER, IMPORT TABLE, LOAD DATA, LOAD XML, TABLE, and VALUES.

A user doesn’t need to commit transaction-control statements themselves. This is to be expected because they manage the transactions for other SQL statements. These SQL statements are COMMIT, ROLLBACK, BEGIN, SET AUTOCOMMIT, and START TRANSACTION.

11. Conclusion

In this article, we learned about SQL statements that require a user to commit the enclosing transaction for the changes to become permanent. Thus, we can run these statements in a user-managed transaction started with START TRANSACTION, or in an implicit transaction enabled by turning off the AUTOCOMMIT mode.