Wednesday, March 27, 2024

What’s Really Happening Behind a Database Transaction

5 min read

Key points

Isolation Levels Matter: Isolation levels, such as READ COMMITTED, safeguard data integrity by managing concurrent transactions. They're essential in systems with complex transactions, ensuring consistency and preventing data anomalies.
Addressing Transactional Risks: Each isolation level—Read Uncommitted, Read Committed, Repeatable Read, Serializable—targets specific risks, from dirty reads to phantom reads. The choice of level affects how transactions view and impact each other's data.
Optimizing Performance and Accuracy: The right isolation level strikes a balance between data accuracy and database performance. Testing these levels in actual database projects helps identify the best setup for applications, crucial in sectors where data reliability is paramount.

(Updated: Thursday, March 28, 2024)

When managing a database, we often deal with transactions— like moving money between bank accounts. These transactions are crucial because they help keep our data accurate and consistent. But when many transactions happen at once, things can get messy without the right controls. This is where transaction isolation levels come in. They help manage how transactions interact, while making sure our data stays reliable.

What's a Database Transaction?

Think of a transaction as a package of several operations. It’s all or nothing—if one part fails, the whole transaction is rolled back, as if it never happened. This keeps errors from messing up your data.

If you are programming in Java, and with Spring framework as your JPA. the @Transactional annotation is no stranger. However, have you thought through what has been really happening behind the scene?

Let’s start this by looking at an example utilizing the Spring Data JPA, where we may use the @Transactional annotation to define the scope and configuration of a transaction, including the isolation level.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.EntityManager;

public class AccountService {

    private EntityManager entityManager;

    @Transactional(isolation = Isolation.READ_COMMITTED)
    public void transferFunds(Long fromAccountId, Long toAccountId, BigDecimal amount) {
        Account fromAccount = entityManager.find(Account.class, fromAccountId);
        Account toAccount = entityManager.find(Account.class, toAccountId);



        // Other operations..

In this example, the transaction management is declarative. The @Transactional annotation specifies that the transferFunds method should run within a transaction context with the READ_COMMITTED isolation level. Behind the scene, Spring Data JPA handles the transaction's start, commit, and rollback operations based on the method's execution result.

What is Isolation and Why it Matters

Now let’s take a step-back, and look at the specification of the setup here.

What is “isolation”?

Without isolation, transactions could interfere with each other, leading to problems like:

  • Dirty Reads: One transaction reads changes from another that hasn’t finished yet. If the second transaction gets canceled, the first one has read data that doesn’t really exist.
  • Non-Repeatable Reads: You read a record, and while you're still working, another transaction changes or deletes that record. When you look again, the data has changed or gone.
  • Phantom Reads: You read a set of records matching some criteria. Then another transaction adds or removes records that fit those criteria. If you check again, you find different results.

Let's consider an online banking system where a user wants to transfer $100 from their savings account to their checking account. This seemingly simple operation involves several steps:

  • Check the savings account balance to ensure it has at least $100.
  • Withdraw $100 from the savings account.
  • Deposit $100 into the checking account.
  • Record the transaction in the transaction history.

All these operations combined form a single transaction. If any of these steps fail, the entire transaction should be rolled back to prevent inconsistencies in the account balances.

Now, let's see how different isolation levels handle this scenario, focusing on the potential issues they aim to solve. We'll include pseudocode and SQL/Java examples to illustrate each level.

Read Uncommitted

Scenario: While Transaction A is transferring money between accounts, Transaction B can see the intermediate changes before A is completed.

-- Assume account balances are checked and updated here

The issue with this level of isolation, is that Transaction B might see the savings account debited before the checking account is credited, potentially leading to decisions based on uncommitted data (like issuing a loan based on the current, inaccurate account balance).

Read Committed

Scenario: Transaction B can only see changes made by Transaction A after A is fully completed and committed.

SELECT balance FROM accounts WHERE account_id = 1;
-- Other operations

This prevents dirty reads from case 1. However, if Transaction B reads the balance again, it might get a different result due to other completed transactions.

Repeatable Read

Scenario: Once Transaction A reads the balance of an account, that balance cannot change during the transaction. Other transactions can't modify the account balance until A completes.

SELECT balance FROM accounts WHERE account_id = 1;
-- This balance remains constant for the duration of the transaction

This level isolation eliminates non-repeatable reads. Transaction A sees consistent data throughout.


Scenario: Transactions are fully isolated, as if running sequentially. If Transaction A is transferring money, Transaction B must wait until A is done before starting.

SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;
-- Ensures complete isolation by locking the account for the transaction duration

This level of isolation essentially prevents all forms of read anomalies, including phantom reads. The downside of this approach is all transactions are considered serializable, that reducing the throughput of the database application.

Now let's put the isolation levels side-by-side with the transactional risks.

Isolation Level Chart
Isolation Level Chart


We've looked at how different transaction isolation levels affect databases, from the simplest level that allows reading unfinished transactions, to the most secure level that ensures data is completely accurate. Each level balances data correctness with how fast the database works.

Understanding these levels is crucial for designing databases, especially in areas like banking, healthcare, and online shopping, where correct data is critical. Real learning comes from trying these levels out with your own database projects. See how they change the way your database handles lots of transactions at once, and how often it locks up. This will help you find the best setup for your needs.

You might want to check these out ↘

How Your Database Stands Up to the Ultimate Reliability Check: ACID rules

How Your Database Stands Up to the Ultimate Reliability Check: ACID rules

For IT professionals in database management, grasping the core principles that protect our digital transactions is essential. Today, we're exploring the ACID model, a pivotal framework for ensuring transaction reliability and security. This goes beyond mere theoretical knowledge; it's vital for the smooth operation of diverse systems, from financial processes to social media platforms. We'll delve into the ACID principles and their critical role in real-world applications.
Convert String to Date: The Java Ways

Convert String to Date: The Java Ways

Converting strings to dates in programming is a common task that's more complex than it seems. This challenge is rooted in the wide range of date formats and the specifics of time zones. Converting a String to a Date in Java can be done in several ways, depending on the Java version you're using and whether you are incorporating external libraries. Below are the primary methods used in different versions of Java and through some commonly used external libraries.
Cookbook: How to Effectively Revert a Git Commit

Cookbook: How to Effectively Revert a Git Commit

As a software engineer, encountering situations where recent changes need to be undone is not uncommon. It may be due to errors, or in some cases just need a significant rework. When such scenarios arise while using Git as your version control system, there’s a few approaches that can help revert those changes effectively. Let’s take a closer look.
Contact Us

Stay ahead of the curve
on software insights and technology trends.