Concurrency Challenges in Database Transactions: Isolation Levels and Locking Mechanisms

8 min readJan 26, 2024

Picture a scenario where users in a banking system initiate fund transfers simultaneously, potentially leading to conflicts and inconsistencies. To address such challenges, we examine isolation levels and locking mechanisms, exploring their role in preventing conflicts during concurrent data modifications.

Locking in a database transaction is a mechanism used to control access to shared resources, such as database records or tables, to ensure data consistency and integrity when multiple transactions are executed concurrently. The primary goal of locking is to prevent conflicts that may arise when two or more transactions attempt to access or modify the same data simultaneously.

What is Being Locked?

In distributed systems, where data resides across multiple servers, various resources can be locked, including:

Data Records: This is the most common, where specific rows or tables in a database are locked to prevent conflicting modifications.
Files: Shared files, especially those actively being edited, can be locked to avoid corrupted versions from merging.
Resources: Hardware components like printers or network segments can be locked to ensure exclusive use by a specific process.

Types of Locks:

Shared Locks: These locks allow multiple transactions to read a resource concurrently but prevent any of them from writing to it. Shared locks are useful when multiple transactions need to access the same data for read operations simultaneously.
Exclusive Locks: These locks restrict access to a resource to a single transaction, preventing any other transactions from reading or writing to it. Exclusive locks are used when a transaction intends to modify the data.

#Concurrency Control By Isolation:

Transaction isolation level defines the degree to which the operations within a transaction are isolated from the operations of other concurrently executing transactions.

Use When you want to control the visibility of changes made by one transaction to other concurrent transactions.
When you need a consistent view of the data during the entire transaction.
To avoid issues like dirty read, non-repeatable reads or phantom reads.

Read Uncommitted: Transactions can read uncommitted changes made by other transactions. They can read data even if it’s in an intermediate state during another transaction’s update. No locks used. Dirty Read Occurs

Transaction A starts and updates a record. Transaction B, running concurrently, can read the uncommitted changes made by Transaction A.

Read Committed: Read operations wait until a transaction commits before seeing the changes. Shared locks used during reads to prevent dirty reads.

Transaction A starts and updates a record. Transaction B, running concurrently, cannot read the changes by Transaction A until it commits.

Repeatable Read: Uses locking mechanisms to prevent other transactions from modifying data row being read (holding a lock on the row until reading transaction ends), potentially causing other transactions’ reads to block. Ensures that if a transaction reads a record once, it will see the same values in subsequent reads, even if other transactions modify the record.

Transaction A reads a set of records. Even if Transaction B updates or inserts new records, Transaction A will still see the same set of records in subsequent reads until it completes.

** Snapshot Isolation: Snapshot Isolation employs multi-version concurrency control, allowing transactions to read a consistent snapshot of the database without blocking other transactions from reading.

Consistent Snapshot: Regardless of other concurrent transactions modifying the data, the transaction sees a version of the data that is consistent with a specific point in time.
Instead of locking rows, Snapshot Isolation creates multiple versions of a data item to allow concurrent transactions to read and modify data without blocking each other.
Snapshot Isolation supports a high degree of concurrency since reads are non-blocking. Multiple transactions can read and modify data concurrently without waiting for locks to be released. This improves system throughput
Snapshot Isolation typically employs optimistic concurrency control, If a conflict is detected during the transaction’s execution (e.g., due to concurrent modifications of the same data), the transaction may be aborted and retried to ensure consistency.

Serializable: Transactions acquire exclusive locks on accessed data, preventing other transactions from accessing the same data until the transaction is complete. Guarantees that the result of concurrent transactions is equivalent to running them serially.

Use the “Serializable” isolation level in database transactions to ensure that each transaction is executed in a serial order, preventing interference from concurrent transactions. The Serializable isolation level in a database system ensures the highest level of isolation/read-write consistency among transactions, preventing phenomena like dirty reads, non-repeatable reads, and phantom reads.

Serializable Isolation’s Read and Write Locks:

Read Locks: When a transaction reads a data item, it acquires a read lock on that item. This prevents other transactions from acquiring a write lock on the same item concurrently.

Write Locks: When a transaction modifies a data item, it acquires a write lock on that item, preventing other transactions from acquiring read or write locks on the same item

**************************************************************************

#Concurrency Control By Locking:

Used To control access to shared resources explicitly. When you need to prevent conflicts during data modifications.

If high concurrency is crucial and conflicts are expected to be infrequent, optimistic locking might be more appropriate.
If data consistency and controlled access are paramount, pessimistic locking or a higher isolation level may be preferred.

Locking Use Cases:

Inventory Management: Imagine two customers placing orders for the last available souvenir at the same time. Without locking, both orders might be confirmed, leading to an oversell situation. Locking ensures only one order succeeds, maintaining accurate inventory data.

Bank Transactions: Consider a banking system where two users simultaneously attempt to transfer money from their accounts to the same recipient. Without proper locking, conflicts may arise, leading to inconsistencies in account balances. In this scenario, the system can use exclusive locks to ensure that only one transaction at a time can update the sender’s and recipient’s account balances, preventing a race condition and maintaining data integrity.

Collaborative Editing: When multiple users edit a shared document simultaneously, locking specific sections prevents conflicting edits.

Optimistic & Pessimistic Lock:

Optimistic Locking:

Before committing any update, checks if the resource has been modified by another transaction in the meantime, if it does, then rollback the transaction.

Pros: Higher concurrency as resources not locked for read-heavy operation, and where conflicts/concurrent update scenarios are less likely to be happened. Cons: Rollbacks can impact performance and user experience

Pessimistic Locking:

When a transaction wants to access a resource, it locks it to prevent other transactions from accessing it until the lock is released. This ensures that only one transaction can modify the resource at a time, reducing the likelihood of conflicts.

Pros: Guarantees data consistency as resources are not accessible by multiple transactions simultaneously. Cons: Can lead to decreased concurrency and performance due to locks being held for longer periods. Increased potential for deadlocks and contention.

Concurrency Control in Java Persistence API (JPA) with Hibernate: Optimistic vs.

In the context of Java Persistence API (JPA) and Hibernate, optimistic locking and pessimistic locking are two…

dip-mazumder.medium.com

Use Optimistic Locking When:

High Concurrency Is Required: Higher concurrency as resources not locked, It allows multiple transactions to work on data simultaneously until they attempt to commit changes.

Consider a collaborative document editing platform Google Doc, where multiple users can simultaneously work on different sections of a document. Optimistic locking allows multiple transactions/users can read multiple sections not blocking whole documents update

Short Transactions Scenarios with Minimal Conflict Risk: Cases where data rows are less likely to be updated.

In messaging app. Users can send messages concurrently without locking the entire conversation. When editing a sent message, optimistic locking ensures the message hasn’t been altered by another user. Conflicts are rare since multiple users are unlikely to edit the same message simultaneously.

Read-Heavy Scenario with Low Update Frequency: Optimistic locking is well-suited for applications with read-heavy workloads, as it allows concurrent reading without locking.

Consider an e-commerce website where users frequently browse products but only occasionally update their personal information. Most of the interactions involve reading product details, reviews, and browsing categories.

Use Pessimistic Locking When:

a) Critical Data Updates (Prioritizing Consistency):

In a banking system, ensuring consistency during fund transfers is critical. When a user initiates a fund transfer, the system acquires a pessimistic lock on the sender’s account, preventing concurrent transactions (e.g., withdrawals) to ensure consistency. The lock is released upon completing the transaction.

b) Long Transactions Involving Multiple Data Accesses:

Imagine an online reservation system where booking a vacation package involves updating multiple related records, such as flights, accommodations, and activities. Pessimistic locking is applied to relevant records to ensure consistency across the booking process. This prevents concurrent modifications, ensuring the entire reservation is updated consistently.

***************************************************************************

Concurrency example of booking a hotel room:

CREATE TABLE Room (
  id INT PRIMARY KEY,
  room_number INT,
  available BOOLEAN
);

CREATE TABLE Booking (
  id INT PRIMARY KEY,
  room_id INT,
  start_date DATE,
  end_date DATE
);

Now, let’s demonstrate a race condition where Alice and Bob attempt to book the same room concurrently:

-- Alice's transaction
UPDATE Room
SET available = FALSE
WHERE id = 123;

INSERT INTO Booking (room_id, start_date, end_date)
VALUES (123, '2022-01-01', '2022-01-07');

-- Bob's transaction
UPDATE Room
SET available = FALSE
WHERE id = 123;

INSERT INTO Booking (room_id, start_date, end_date)
VALUES (123, '2022-01-03', '2022-01-10');

In this scenario, if both transactions run concurrently, there’s a potential race condition where both Alice and Bob might end up booking the same room.

In Hibernate:

// Alice's transaction
@Transactional
public void bookRoomForAlice() {
    Room room = entityManager.find(Room.class, 123L);
    room.setAvailable(false);

    Booking booking = new Booking();
    booking.setRoom(room);
    booking.setStartDate(LocalDate.parse("2022-01-01"));
    booking.setEndDate(LocalDate.parse("2022-01-07"));
    
    entityManager.persist(booking);
}

// Bob's transaction
@Transactional
public void bookRoomForBob() {
    Room room = entityManager.find(Room.class, 123L);
    room.setAvailable(false);

    Booking booking = new Booking();
    booking.setRoom(room);
    booking.setStartDate(LocalDate.parse("2022-01-03"));
    booking.setEndDate(LocalDate.parse("2022-01-10"));
    
    entityManager.persist(booking);
}

Concurrency Control Techniques

Pessimistic Concurrency Control: “SELECT … FOR UPDATE”:

SELECT * FROM Room WHERE id = 123 FOR UPDATE;

This locks the row with ID 123 in the “Room” table, preventing other transactions from updating or locking it until the lock is released by committing or rolling back the transaction.

Using Hibernate: LockModeType

LockModeType.NONE: Default behavior with no lock.
LockModeType.READ: Shared lock for reading.
LockModeType.WRITE (or LockModeType.UPGRADE_NOWAIT): Exclusive lock for both reading and writing.
LockModeType.PESSIMISTIC_READ: Database-specific shared lock.
LockModeType.PESSIMISTIC_WRITE: Exclusive lock for writing.
LockModeType.PESSIMISTIC_FORCE_INCREMENT: Exclusive lock with version increment (if available).

// Pessimistic concurrency control for Alice's transaction
@Transactional
public void bookRoomForAlicePessimistic() {
    Room room = entityManager.find(Room.class, 123L, LockModeType.PESSIMISTIC_WRITE);
    room.setAvailable(false);

    Booking booking = new Booking();
    booking.setRoom(room);
    booking.setStartDate(LocalDate.parse("2022-01-01"));
    booking.setEndDate(LocalDate.parse("2022-01-07"));
    
    entityManager.persist(booking);
}

Optimistic Concurrency Control: using a “version” column:

ALTER TABLE Room
ADD version INT DEFAULT 1;

UPDATE Room
SET available = FALSE, version = version + 1
WHERE id = 123 AND version = 1;