Commit, Rollback, Savepoint in MySQL

1. Overview

In this tutorial, we’ll learn how to use COMMIT, ROLLBACK, and SAVEPOINT to run a user-managed transaction within a MySQL database.

2. Transactions

A database transaction is a unit of SQL work. Furthermore, each SQL statement runs within a separate transaction by default. Therefore, these statements can’t be used to manage a transaction with autocommit mode enabled.

We can run multiple SQL statements in a single transaction by grouping them within the pair of statements START TRANSACTION and COMMIT or ROLLBACK. However, we can’t nest transactions.

COMMIT, ROLLBACK, and SAVEPOINT are SQL transaction management statements in MySQL. However, only the transactional storage engine InnoDB supports all three of these SQL statements.

3. Model and Transactions

Let’s use the Department table from our University database.

We’ll add new departments in a batch within a single transaction, then make the changes permanent in one example and roll back all the changes in another:

4. Using the COMMIT Statement

COMMIT is an SQL statement that makes changes permanent and ends a user-managed transaction. Furthermore, when a change is made permanent, it becomes visible in all user sessions.

COMMIT’s syntax with optional sub-clauses in square brackets is:

COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

The COMMIT WORK statement is equivalent to the COMMIT statement.

4.1. Example

Let’s start a new transaction in a MySQL session and add new data:

START TRANSACTION;

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

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

COMMIT;

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, we can extend the functionality of COMMIT with the optional clauses.

4.2. Optional Clauses

By default, when one transaction ends with COMMIT, there’s no active transaction. We can use COMMIT AND CHAIN to commit one transaction and thereupon chain or start a new transaction:

START TRANSACTION;  
/* SQL 
   Statements */ 
COMMIT AND CHAIN;

Accordingly, it starts a new transaction after the one we started is committed. To verify, let’s try to call SET TRANSACTION, only to get an error message:

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

Further, the COMMIT statement ends a transaction by default, and the active user session continues. We can use the RELEASE clause to disconnect the active client session after a transaction is committed:

START TRANSACTION;  
/* SQL 
   Statements */ 
COMMIT RELEASE;

To verify, run a SELECT query:

SELECT 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    9
Current database: University
+---+
| 1 |
+---+
| 1 |
+---+

It indeed disconnects the client session.

5. Using the ROLLBACK Statement

The ROLLBACK statement undoes all the changes and thereby ends an active transaction. Therefore, the changes are never visible in other user sessions.

ROLLBACK’s syntax with optional sub-clauses in square brackets is:

ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

The ROLLBACK WORK statement is equivalent to ROLLBACK.

We can use ROLLBACK AND CHAIN to start a new transaction when one ends.

Similarly, we can use the RELEASE clause to end the active client session when rolling back a transaction.

5.1. Example

Let’s delete all data from the default data set:

START TRANSACTION;

DELETE 
FROM Department;

SELECT *
FROM Department;

ROLLBACK;

The SELECT statement returns an empty set, and ROLLBACK undoes all the changes. In other words, in the end, data isn’t deleted from the Department table. 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, all the rows are still there.

5.2. We Can’t Roll Back Some Statements

However, we can’t roll back some statements even with an explicit ROLLBACK.

These are data definition language (DDL) statements – CREATE, ALTER, and DROP for tables and stored routines.

6. Using the SAVEPOINT Statement

We may not always want to roll back an entire transaction. SAVEPOINT is an SQL statement that creates a savepoint for a group of SQL statements within a transaction. A savepoint acts as a transaction marker.

We can roll back a transaction to a savepoint without affecting the statements run before it. Furthermore, we can create multiple savepoints within a single transaction.

6.1. Creating a Savepoint

We can use the SAVEPOINT statement to create a savepoint:

SAVEPOINT identifier;

The identifier in the syntax is the savepoint’s name, which we can use later. Let’s start a new transaction to demonstrate savepoints:

START TRANSACTION;

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

SAVEPOINT sp1;

Creating a savepoint doesn’t commit the transaction or save the changes made by the SQL statements before the savepoint.

6.2. Rolling Back to a Savepoint

We can use the ROLLBACK TO statement to undo the changes made within a transaction after a savepoint. However, we can’t use it to roll back the entire transaction itself.

The syntax of ROLLBACK TO, including optional clauses, is:

ROLLBACK [WORK] TO [SAVEPOINT] identifier

Let’s continue with the same example, delete data for id=6, and roll back to sp1:

DELETE 
FROM Department 
WHERE id=6;

ROLLBACK TO SAVEPOINT sp1;

As a result, all changes made after the savepoint sp1 are undone:

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   |
+----+--------------------------------+------+

Indeed, it lists the data from before the savepoint, undoing the DELETE.

6.3. Releasing a Savepoint

We can use the RELEASE SAVEPOINT statement to remove a savepoint. It doesn’t commit or roll back the active transaction.

The syntax is:

RELEASE SAVEPOINT identifier

After a savepoint is removed, it can’t be used. However, we can still roll back the complete transaction with ROLLBACK.

7. Implicit Commit

Some SQL statements will implicitly commit an active transaction. These statements include the Data Definition Language (DDL) statements that define or modify database objects, such as the CREATE TABLE statement.

It’s important to know about implicit commits so that we can avoid unexpected data changes. It’s best to exclude DDL statements from a user-managed transaction because they end the transaction without notifying the user.

Let’s start a new transaction to demonstrate an implicit commit:

START TRANSACTION;

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

CREATE TABLE t(i INTEGER);

ROLLBACK;

However, the transaction isn’t rolled back, and the new table is still there:

SHOW TABLES;
+----------------------+
| Tables_in_University |
+----------------------+
| Department           |
  ...
| t                    |
+----------------------+

Furthermore, notwithstanding the ROLLBACK statement, the transaction adds new data. This is because the CREATE TABLE commits the transaction started with START TRANSACTION. Therefore, ROLLBACK has no effect.

8. Conclusion

In this article, we learned about the COMMIT, ROLLBACK, and SAVEPOINT statements in MySQL. These are transactional statements supported by the InnoDB engine, and we can use them to manage our transactions.