Handling Database Concurrency

In today's world of highly dynamic and scalable applications, handling database concurrency is critical to ensuring data consistency, performance, and reliability. Whether you're building a financial application that processes thousands of transactions per second or a collaborative platform where users update shared data in real-time, understanding how to manage concurrent database access is essential. This blog will explore the fundamental concepts of database concurrency, common challenges developers face, and strategies to ensure your system remains robust under pressure.

What is Concurrency?

Concurrency refers to the ability of a system to handle multiple tasks, operations, or processes at the same time. In the context of databases, concurrency specifically means enabling multiple users or processes to access and manipulate the database simultaneously without negatively affecting the integrity or consistency of the data. The way databases handle this is by using Transactions.

What is a Transaction?

A transaction in the context of databases is a sequence of one or more operations (like reading, writing, or updating data) performed as a single, logical unit of work. A transaction ensures that a database remains in a consistent state, even in the presence of system failures or concurrent operations. A transaction is A.C.I.D(Atomic, Consistent, Isolated and Durable)

Atomicity: The transaction is all-or-nothing. Either all operations in the transaction are successfully executed, or none are executed at all (rolled back).

Consistency: A transaction ensures the database transitions from one valid state to another.

Isolation: Transactions should execute as if they are isolated from one another, even when running concurrently.

Durability: Once a transaction is committed, its changes are permanent, even if the system crashes afterwards.

Transaction Lifecycle/Process

A simple SQL transaction would be this:

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

Isolation Levels

One of the key characteristics of Transactions is Isolation, but it does not mean another transaction cannot read its data. Transactions have Isolation Levels to control access from other transactions. These include:

1. Read Uncommitted: The least strict isolation level. Transactions can read data that has not yet been committed by other transactions.

2. Read Committed: Transactions only read data that has been committed. Uncommitted changes are not visible.

3. Repeatable Read: Ensures that if a transaction reads a row, it will see the same data if it reads it again, even if other transactions modify the data. This can prevent updates to rows from being read by the transaction.

4. Serializable: The strictest isolation level. Ensures full isolation by serializing transactions, making them appear as if they are executed one at a time. It prevents any other transactions from modifying or inserting rows that would affect the current transaction’s results.

Concurrency-related challenges

Dirty Reads

A transaction reads data that another transaction has modified but not yet committed.

This leads to unreliable and inconsistent data

Possible Solutions

1. Set Transaction Isolation Level To Read Committed or Repeatable Read or

   Serializable
   

   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   BEGIN TRANSACTION;
   -- SQL operations here
   COMMIT;
   

Non-Repeatable Reads

A transaction reads the same record twice but gets different results because another transaction modifies the data in between.

The problem here is that Transaction A should still read the balance as 100 but Transaction B has made an update to the row for user 1. This makes the transaction inconsistent.

Possible Solutions

1. Set Transaction Isolation Level Repeatable Read or Serializable
   

   SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   BEGIN TRANSACTION;
   -- SQL operations here
   COMMIT;
   

Phantom Reads

A transaction reads a set of rows matching a condition. Another transaction inserts or deletes rows matching the same condition, and the original transaction sees these "phantom" rows.

The problem here is that Transaction A should still read the same data from all the rows with status = ‘pending’ but Transaction B has made an update in between its operations. This makes the transaction inconsistent.

Possible Solutions

1. Set Transaction Isolation Level to Serializable
   

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   BEGIN TRANSACTION;
   -- SQL operations here
   COMMIT;
   

Deadlocks

When using serializable isolation levels, transactions enter a lock state to prevent other transactions from accessing the data it has access to this could lead to two or more transactions waiting for each other to release resources, causing a standstill or deadlock.

Possible solutions

1. Design transactions to access resources in a consistent order.

2. Batch process to break large transactions into smaller, independent ones to reduce contention

3. Always update Table A before Table B

4. Set Lock Timeouts

Lost Updates

Two transactions overwrite each other's updates because they work on the same data simultaneously without proper synchronization.

The problem here is that, if the balance was at 100 when both transactions started, before Transaction A is done, Transaction B already updates the balance to 70. Therefore losing the update of add 50 to the balance. This leads to inconsistency with data.

Possible Solutions

1. Set Transaction Isolation Level to Serializable

Other challenges can arise due to introducing transactions in queries, these include:

1. Resource Contention:

   Multiple transactions compete for the same resources (e.g., rows, memory, disk). This slows down transaction processing and can degrade overall system performance.

   Possible solutions include:

   • Efficient query design
   • Partitioning and Sharding
   • Load Balancing
   • Batch processing

2. Starvation:

   A transaction waits indefinitely because other higher-priority transactions continually preempt its execution. A low-priority transaction is delayed repeatedly while high-priority transactions keep getting executed. This affects fairness and can block important but low-priority operations

   Possible solutions include:

   • Timeout-Based retries
   • Using deadlock detection mechanisms

3. Transaction Aborts or Rollbacks

   When a transaction is rolled back due to conflicts or errors, other dependent transactions might face failures or inconsistencies. A transaction deletes a row while another reads it, causing the second transaction to fail. This Increases system overhead and complicates error recovery.

   Possible solutions include:

   • Using proper Isolation Levels
   • Validate inputs early
   • Using proper error handling and retry logic

Conclusion

Effective handling of database concurrency is a cornerstone of building robust and scalable applications. By understanding the challenges of concurrent data access and leveraging strategies like locking, isolation levels, and conflict resolution, you can safeguard data integrity while maintaining performance. Whether you're working with relational or non-relational databases, the key is to balance consistency and efficiency according to your application's needs. With thoughtful design and a solid grasp of concurrency principles, you can ensure your system remains resilient, even in the face of heavy demand.

Comments

[Abiodun]

Good job sir!