SQL Transactions and Isolation Levels: The Ultimate Guide

Transactions are an important part of SQL and the databases they run on. Learn all about SQL transactions in this guide.

What is a Transaction in SQL?

A transaction is a unit of work that is performed in its entirety on a database. Multiple SQL statements are run individually, but if they are part of a transaction, either all of the statements succeed or none of them succeed.

Why is this important? Can’t we just let the SQL statements run without introducing this complexity of a transaction?

We could do that, but in some situations, it can cause problems.

Consider the scenario of a database for a bank. Let’s say you want to transfer money from one account to another. The steps to do that in the application could be:

1. Select account to transfer from and to
2. Enter the amount to transfer
3. Click “Transfer”
4. The “account from” has the amount reduced
5. The “account to” has the amount increased

Once you click Transfer to transfer the money, the balances of both accounts are updated. Let’s say this is done using an UPDATE statement, and the amount to transfer is $100:

UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;

These two statements will cause $100 to be transferred from account_id 1 to account_id 2.

But what happens if the second statement fails? The first statement succeeds and the account 1 balance is now $100 less, but the account 2 balance remains the same.

Or, what happens if the first statement fails? Account 2 has an increase in balance but account 1 remains the same.

Ideally, these two statements should be executed together. Either both statements run successfully, or both statements don’t.

This is the purpose of a transaction.

All major database vendors support this functionality, and in this guide, I’ll explain everything you need to know about transactions.

When To Use SQL Transactions

Transactions should be used in SQL when you need to ensure that multiple statements need to be run all together or not at all. One common example of this is updating balances in two accounts when a transfer is made.

You can use SQL without transactions, but if you need the “all or nothing” concept and other features of transactions, consider using one.

What is ACID?

For something to be a transaction, it needs to adhere to four properties. These four properties are known as ACID, which stands for Atomicity, Consistency, Isolation, and Durability.

Let’s take a look at the definition of each of these.

Atomicity

Atomicity, or atomic, means that a transaction must succeed as a whole or fail as a whole. It means that all steps must run successfully or they all fail. The transaction cannot be completed in part. A transaction therefore guarantees that all steps are completed.

Using the example above of transferring money between two accounts, for it to be a transaction, both of the update statements need to successfully run. If only one is successful and the other fails, then it is not “atomic” and therefore not a transaction.

Consistency

Consistency means that after the transaction is completed, the database is left in a valid and consistent state. This means that all primary and foreign keys are adhered to, as well as other constraints. Data is not corrupted or missing as a side effect of this transaction.

Isolation

Isolation means that the transaction operates without impacting other users or sessions of the database. The operations performed by the transaction don’t impact other transactions and other operations don’t impact this transaction.

Durability

Durability means that the result of this transaction is stored in the database and won’t be lost if the database crashes or fails. This is achieved by permanently storing the data in the database and using backups.

Transaction Commands

How do we create a transaction, instead of running the SQL statements separately?

There are a few commands included in the database that allow you to work with transactions. These are often called TCL commands, or Transaction Control Language, and are different commands to DDL (Data Definition Language) and DML (Data Manipulation Language) commands.

Start

The Start command will start or begin a new transaction. The exact command for this is shown in the examples below, but running it allows you to specifically start a new transaction.

A transaction is started when a database session is started, but sometimes you may want to begin a new transaction at a specific point.

Commit

The Commit command allows you to indicate that the transaction has ended successfully and the data should be saved permanently, or committed, to the database.

This happens automatically at some points (an implicit commit), but you can run a COMMIT command to commit data at a certain point (an explicit commit).

A commit can occur:

• When you run a DDL statement (e.g. CREATE TABLE)
• When you exit the IDE and indicate you want to commit your changes
• When you run a COMMIT command

Rollback

The Rollback command allows you to undo the changes from a transaction and restore the database to a state before the transaction began. It’s the opposite to a Commit.

Just like a commit, a rollback can happen automatically if an error occurs (an implicit rollback) or you can specify it at a certain point (an explicit rollback).

A rollback can occur:

• When an exception happens in your query
• When the SQL IDE exits
• When you run a ROLLBACK command

Savepoint

When running a transaction, you can specify a savepoint, which is a point in the transaction that can be used as a point to return to upon a failure.

It’s like a checkpoint in a video game. If something fails in the transaction after the savepoint, you can rollback to the savepoint and continue the transaction, instead of rolling back the entire transaction.

Set transaction name

Another feature of using transactions is the ability to set a name for the transaction. This is optional and just allows you to identify the transaction in your code and improve readability.

Let’s take a look at some examples of these commands in different databases.

Oracle Transaction Examples

How do you use a transaction in Oracle SQL? Let’s see some examples.

The official manual for Oracle transactions can be found here.

Implicit Transaction

This example uses an implicit transaction. This means it uses the transaction that is automatically created when a new session is started (when you log in and run a query):

UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;

This transaction does not commit or rollback yet. After running these two commands, the transaction is still in progress.

Explicit Transaction

You can’t explicitly create a transaction in Oracle. A transaction is automatically created when an executable statement is run, which includes DML, some DDL statements, and a SET TRANSACTION command.

Commit

To commit a transaction in Oracle, call the COMMIT command.

UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

The changes made between the start of the transaction and the COMMIT statement are permanently stored (committed) on the database. Any savepoints created are removed.

Rollback

To roll back a transaction in Oracle, call the ROLLBACK command.

UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
ROLLBACK;

The changes made from these Update statements are not stored in the database, and the data is reverted back to the way it was before the statements were run.

Savepoint

A savepoint can be created with the SAVEPOINT command:

UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
SAVEPOINT after_bal_transfer;

SQL commands can be run after the SAVEPOINT command. The transaction can then be rolled back to the savepoint if needed, and the statements before the savepoint are not lost.

ROLLBACK TO SAVEPOINT after_bal_transfer;

Set Transaction Name

In Oracle, you can set the name of the transaction, which serves as a user-readable identifier for the transaction. This is useful if you are querying the data dictionary views to find out more about the transaction, such as V$TRANSACTION.

This command must be the first command in the transaction.

SET TRANSACTION NAME balance_update;

SQL Server Transaction Examples

How do you begin, commit, and rollback a transaction in SQL Server? Let’s take a look at these examples.

The official manual for SQL Server transactions can be found here.

Begin Transaction

A transaction is NOT automatically created when you run an executable SQL statement in SQL Server.

However, you can manually create a new transaction using the BEGIN TRAN or BEGIN TRANSACTION statement:

BEGIN TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;

After these statements, the transaction is in-progress. It has not yet been committed or rolled back.

The same code can be run with the BEGIN TRAN command:

BEGIN TRAN;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;

Set Transaction Name

You can set a transaction name for a new transaction in SQL Server as part of the BEGIN TRANSACTION command:

BEGIN TRANSACTION update_balance;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;

This provides a user-readable name for the transaction, making it easier to identify in the script and in any logs.

Commit

To commit a transaction in SQL Server, simply run the COMMIT command:

BEGIN TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

The changes made as part of the transaction are permanently saved to the database.

Rollback

To roll back a transaction in SQL Server, simply run the ROLLBACK command:

BEGIN TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
ROLLBACK;

The changes from these statements are not saved to the database after the ROLLBACK command is run.

Savepoint

A savepoint can be created with the SAVE TRANSACTION command:

BEGIN TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
SAVE TRANSACTION after_bal_transfer;

SQL commands can be run after the SAVE TRANSACTION command. The transaction can then be rolled back to the savepoint if needed, and the statements before the savepoint are not lost.

ROLLBACK TO SAVEPOINT after_bal_transfer;

Implicit Transactions in SQL Server

SQL Server transactions must be defined explicitly. Unlike Oracle, it does not enable implicit transactions automatically.

However, if you want to run implicit transactions, you can turn this on:

SET IMPLICIT_TRANSACTIONS ON;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;

This means that a transaction is started when you run the first UPDATE statement here, and is not ended until you either COMMIT or ROLLBACK.

This behaviour is not the default in SQL Server so may be unexpected for you. Brent Ozar has written more about it here. The comments in that article are also interesting to read.

MySQL Transaction Examples

In MySQL, statements are automatically committed by default. This means that statements cannot be effectively used within transactions, as they are committed when they are run.

You can disable autocommit for a transaction by starting a transaction.

The official manual for MySQL transactions can be found here.

Begin Transaction

To begin a transaction in MySQL, use the START TRANSACTION command.

START TRANSACTION;

You can also start a transaction using BEGIN or BEGIN WORK. However, START TRANSACTION is standard SQL syntax and permits some modifiers that BEGIN does not.

Commit

To commit a transaction in MySQL, use the COMMIT command.

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

The changes made as part of the transaction are permanently saved to the database.

Rollback

To roll back a transaction in MySQL, use the ROLLBACK command:

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
ROLLBACK;

The changes from these statements are not saved to the database after the ROLLBACK command is run.

Savepoint

A savepoint can be created with the SAVEPOINT command:

BEGIN TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
SAVEPOINT after_bal_transfer;

SQL commands can be run after the SAVEPOINT command. The transaction can then be rolled back to the savepoint if needed, and the statements before the savepoint are not lost.

ROLLBACK TO SAVEPOINT after_bal_transfer;

PostgreSQL Transaction Examples

PostgreSQL also contains the functionality to use transactions in your SQL code. Let’s see some examples.

The official manual for PostgreSQL transactions can be found here.

Begin Transaction

To begin a transaction, run the BEGIN command.

BEGIN;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;

This will start a transaction, but the data is not committed or rolled back. The transaction is in progress.

Commit

To commit the changes for a transaction, run the COMMIT command.

BEGIN;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

The changes from these statements are saved permanently to the database.

Rollback

To rollback changes for this transaction, run the ROLLBACK command.

BEGIN;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
ROLLBACK;

The changes from these statements are not saved to the database after the ROLLBACK command is run.

Savepoint

A savepoint can be created with the SAVEPOINT command:

BEGIN TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
SAVEPOINT after_bal_transfer;

SQL commands can be run after the SAVEPOINT command. The transaction can then be rolled back to the savepoint if needed, and the statements before the savepoint are not lost.

ROLLBACK TO SAVEPOINT after_bal_transfer;

When Does an SQL Transaction End?

An SQL transaction can end, either successfully or unsuccessfully, in many situations:

• A COMMIT command is issued
• A DDL statement is run
• A ROLLBACK command is issued
• The SQL IDE is closed
• The connection to the database is lost
• The database encounters an error

What is “autocommit”?

Some databases have a feature called “autocommit”. This means that SQL statements are automatically committed to the database. No transactions are created and data cannot be rolled back.

SQL statements in SQL Server and MySQL are committed automatically by default, but each database has a way to disable this and begin a transaction.

Several IDEs also have an option to set autocommit on and off, so it could also be an option you change in the IDE rather than the database itself.

What Are Isolation Levels?

Earlier we mentioned that a transaction has several properties: Atomicity, Consistency, Isolation, and Durability.

One of these properties – Isolation – determines how transactions interact with data from other transactions.

Isolation levels are more than just “isolated” and “not isolated”. There are actually four different isolation levels defined in SQL. These four isolation levels are defined by whether or not they can read data in certain situations.

These situations are called “read phenomena”.

What Are Read Phenomena?

Read phenomena can be thought of as situations where data is read by a query in a transaction where you may not want it to be read. They can cause issues with working with data in a transaction.

There are four read phenomena.

We’ll base our examples on this data:

Dirty Read

A dirty read is where a transaction can read a row that has been added or modified by another transaction that is not yet committed.

An example of a dirty read could look like this:

SELECT balance
FROM account
WHERE id = 1;
//Result is 100

UPDATE account
SET balance = balance + 200
WHERE id = 1;
//Balance is set to 300

SELECT balance
FROM account
WHERE id = 1;
//Result is 300

ROLLBACK;

In step 1, the balance is retrieved. Step 2 updates the balance, but the data is not committed.

Step 3 retrieves the balance, but the balance it retrieves is the uncommitted value of 300.

Transaction 2 then performs a ROLLBACK so the update is reversed, but Transaction 1 has a value of 300. This is a Dirty Read.

Lost Update

The Lost Update problem means that changes from one transaction are lost because they are overwritten by another transaction.

Let’s see an example of this:

SELECT balance
FROM account
WHERE id = 1;
//Result is 100

UPDATE account
SET balance = balance + 200
WHERE id = 1;
//Balance is set to 300

UPDATE account
SET balance = balance + 50
WHERE id = 1;

COMMIT;
//Balance is saved as 300

COMMIT;
//Balance is saved as 150

In this example, two transactions are updating the balance of an account. They both start with the same value of 100 but update the balance to a different amount. Transaction 2 commits its changes with a balance of 300, but transaction 1 commits its changes with a balance of 150.

Because Transaction 1 committed last, the balance of the account is saved as 150. The updates performed as part of Transaction 2 are lost.

The correct value for the balance should have been 350.

This is the Lost Update problem.

Non-Repeatable Read

A non-repeatable read is where a transaction retrieves data twice for a row but the data for a row is different on each retrieval.

It’s different to a Dirty Read in that the second transaction in a Non-Repeatable Read is committed, but the second transaction in a Dirty Read is not committed.

An example of a non-repeatable read could look like this:

SELECT balance
FROM account
WHERE id = 1;
//Result is 100

UPDATE account
SET balance = balance + 200;
WHERE id = 1;
//Balance is set to 300
COMMIT;

SELECT balance
FROM account
WHERE id = 1;
//Result is 300
COMMIT;

In this example, the first execution of the SELECT statement in Transaction 1 gets a different value to the second execution of the same query in the same transaction. This is a non-repeatable read.

The occurrence of this depends on the isolation level that is set.

To avoid this, you can set the isolation level to a level that means this does not occur. Or, you can prevent transaction 1 from starting until transaction 2 is complete.

Phantom Read

A Phantom Read is where a row is added to or removed from the results in a transaction because it was inserted or deleted by another transaction.

This is similar to a Non-Repeatable Read but it relates to new or removed rows, rather than changed data.

An example of this is shown below when new rows are inserted:

SELECT *
FROM account;
//Shows 2 rows (id 1 and 2)

INSERT INTO account
(id, balance)
VALUES (3, 50);
COMMIT;

SELECT *
FROM account;
//Shows 3 rows (id 1, 2, and 3)

In this example, a new row is added to the account table by Transaction 2, which is then read by Transaction 1. The results from Transaction 1 are different from its initial query of the rows in the table.

Another example is if rows are deleted:

SELECT *
FROM account;
//Shows 2 rows (id 1 and 2)

DELETE FROM account
WHERE id = 1;
COMMIT;

SELECT *
FROM account;
//Shows 1 row (id 2)

In this example, account id 1 is deleted. In Transaction 1, the second query returns a different result to the first query.

What Are The Available Isolation Levels?

We’ve seen the three different read phenomena. Databases have an “isolation level” property that defines if each of these read phenomena are possible. In many database systems, this can be changed.

Let’s take a look at the different isolation levels.

Read Uncommitted

This is the lowest level of isolation. With this Read Uncommitted isolation level, transactions can read uncommitted changes from other transactions. This means it is possible for:

• Dirty reads
• Lost updates
• Non-repeatable reads
• Phantom reads

Read Committed

With the Read Committed isolation level, transactions can only read data that has been committed. It prevents transactions from seeing changes made by dirty reads (data from uncommitted transactions).

It is possible for:

• Lost updates
• Non-repeatable reads
• Phantom reads

This is the default isolation level for Oracle and SQL Server.

Repeatable Reads

With the Repeatable Reads isolation level, a transaction holds read and write locks on any rows it references. This prevents Non-Repeatable Reads from occurring because data locked by a transaction during a SELECT cannot be updated by another transaction. It also means Lost Updates and Dirty Reads cannot occur.

It is possible for:

• Phantom reads

This is the default isolation level for MySQL.

Serializable

This is the highest level of isolation. With a Serializable isolation level, a transaction holds read and write locks on any rows it references. It also acquires a “range lock” if a WHERE clause is used on a range so that Phantom Reads are avoided.

This isolation level prevents dirty reads, lost updates, non-repeatable reads, and phantom reads from occurring.

This is the default isolation level for PostgreSQL.

Examples of Isolation Levels and Read Phenomena

This table outlines the relationship between the read phenomena and isolation levels, and whether each is possible.

Set the Transaction Isolation Level

You can set the transaction level in your database so it’s different to the default. This is done in most databases by using the following command:

SET TRANSACTION ISOLATION LEVEL iso_level;

For example, to set it to Read Uncommitted:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

This will set the transaction isolation level in your database.

Conclusion

Transactions are an important feature of SQL and its ability to maintain quality data in the database. Transactions adhere to the ACID properties (Atomicity, Consistency, Isolation, Durability). They can be started, committed, and rolled back.

Databases can encounter several different read phenomena which can cause issues when working with data. Isolation levels exist that address these read phenomena and can be changed in the database.