Characterizing and Controlling Concurrency in Database Transactions

Introduction

In a database system, multiple transactions often run at the same time to improve resource utilization and throughput. However, when two or more transactions operate on the same data concurrently, their combined effect may produce inconsistent results unless carefully controlled.

Let’s take an example of a banking environment to illustrate how such problems arise and how concurrency control mechanisms maintain correctness.

Assume we have two transactions operating on a customer’s balance stored in a data item X:

• Transaction T₁: Withdraws 100 from the balance.
• Transaction T₂: Deposits 200 into the same balance.

Initially, balance (X) = 1000.

We will observe how different execution schedules affect the final value of X.

Schedule A :Serial Execution (T₁ → T₂)

Sequence of Operations

T₁: read(X = 1000)
T₁: X := X - 100 → 900
T₁: write(X = 900)

T₂: read(X = 900)
T₂: X := X + 200 → 1100
T₂: write(X = 1100)

Explanation

Transaction T₁ executes fully before T₂ begins. The result after T₁ is 900, and after T₂ is 1100. This schedule is perfectly consistent and equivalent to executing the transactions one after another.

Observation:

• Type: Serial Schedule
• Result: Consistent
• Concurrency: None
• Serializability: Guaranteed

Schedule B :Serial Execution (T₂ → T₁)

Sequence of Operations

T₂: read(X = 1000)
T₂: X := X + 200 → 1200
T₂: write(X = 1200)

T₁: read(X = 1200)
T₁: X := X - 100 → 1100
T₁: write(X = 1100)

Explanation:

Now the order is reversed. T₂ completes its deposit before T₁ performs the withdrawal. The final result (X = 1100) is still consistent, just like Schedule A.

Observation: Both Schedules A and B are serial and correct; however, they execute sequentially and do not allow concurrent access.

Schedule C :Non-Serial Execution (Lost Update Problem)

Sequence of Operations

T₁: read(X = 1000)
T₂: read(X = 1000)

T₁: X := X - 100 → 900
T₂: X := X + 200 → 1200

T₁: write(X = 900)
T₂: write(X = 1200)

Explanation

Both transactions read the same initial balance before either one writes back. When T₁ writes 900 and T₂ later writes 1200, T₁’s update is lost. The final balance is 1200 instead of the correct 1100.

This is the Lost Update Problem.

Observation:

• Type: Non-serial schedule
• Problem: Lost update
• Cause: Concurrent read of same value followed by overwriting writes
• Result: Inconsistent database state

Real-world Example

T₁: read(balance = 1000)      // User 1 withdraws 100
T₂: read(balance = 1000)      // User 2 deposits 200
T₁: update(balance = 900)
T₂: update(balance = 1200)
Final balance = 1200 (withdrawal lost

Problem Statement:

The inconsistency in Schedule C arises because transactions operate independently on the same data item without coordination. Each transaction assumes the data it reads will not be modified by others until it finishes, which is false in concurrent systems.

To prevent such anomalies, database systems enforce Concurrency Control Protocols that guarantee serializability — meaning that the outcome of concurrent execution is equivalent to some serial order.

Concurrency Control in DBMS

In a Database Management System (DBMS), Concurrency control is a mechanism in DBMS that allows simultaneous execution of transactions while maintaining ACID properties — Atomicity, Consistency, Isolation and Durability. It maintains the integrity, accuracy and reliability of data when multiple users or processes perform read/write operations concurrently. It helps manage issues like:

• Conflicting operations on shared data
• Inconsistent database states
• Lost or incorrect updates

Note: By implementing concurrency control techniques such as locking or timestamp ordering, DBMS ensures that transactions are executed safely and independently, even when they overlap in time.

ACID Properties

To prevent these issues, database systems enforce the ACID properties, which define the foundation of reliable transaction processing.

A: Atomicity

A transaction must be all or nothing. If any part of a transaction fails, all changes must be rolled back to ensure the database remains consistent.

C: Consistency

The database must always remain in a valid state before and after a transaction. Constraints, rules, and relationships must be preserved.

I: Isolation

This property ensures that concurrent transactions do not interfere with each other. Intermediate states of one transaction should not be visible to others until it is committed. Techniques such as locking, serialization, and timestamp ordering are used to maintain isolation.

D: Durability

Once a transaction is committed, its changes must persist even if the system crashes immediately afterward.

To understance ACID properly lets use a simple bank transfer transaction:

Transaction T: Transfer 100 from Account A to Account B

read(A)
A = A - 100
write(A)

read(B)
B = B + 100
write(B)
commit

Initially:

A = 1000
B = 500

A: Atomicity

Meaning: Execute the whole transaction, or execute none of it.

Example

During the transfer, suppose the system crashes after deducting 100 from A but before adding it to B.

Steps executed:

A = 1000 → 900     (written)
System crashes before updating B

Without atomicity, database ends up as:

A = 900
B = 500       (money lost)

With Atomicity, DBMS rolls back all partial changes → restore original state:

A = 1000
B = 500

No partial work is allowed.

C :Consistency

Meaning: A transaction must leave the database in a state that follows all rules and constraints.

Example

Banking constraint:

Total Money in System must remain same.

Before transaction:

A + B = 1000 + 500 = 1500

After transaction:

A = 900
B = 600
Total = 1500   (still consistent)

If DBMS allowed incorrect result like:

A = 900
B = 500
Total = 1400   (inconsistent)

Then consistency would be violated.

Consistency ensures:

• No money is created or destroyed.
• Constraints and rules always hold.

I:Isolation

Meaning: A running transaction must not expose its intermediate steps to others.

Example (Unrepeatable Read / Dirty Read prevention/Lost Update/Inconsistent Read)

Transaction T1 (transfer 100) is executing:

A = 1000 → 900 (not committed yet)

Transaction T2 tries to read A at this moment:

T2: read(A)  → sees 900 (uncommitted value)

If T1 later fails and rolls back:

A returns to 1000

Now T2 has used wrong data.

Isolation prevents T2 from seeing A = 900 until T1 commits. So T2 will only read:

A = 1000

And the DBMS ensures that the final result is as if:

T1 ran first
then T2 ran

Even if their operations interleave internally.

D: Durability

Meaning: Once a transaction is committed, its result must not be lost.

Example

T commits:

A = 900
B = 600
commit

Immediately after commit, power outage occurs and the server restarts.

Because of durability, DBMS uses log records to restore the committed state:

After restart:

A = 900
B = 600

The committed transaction cannot be undone by system failures.

Need of Concurrency Control

Concurrency control is essential to:

• Prevent conflicts between simultaneous transactions.
• Maintain data consistency and accuracy in multi-user environments.
• Avoid problems such as dirty reads, lost updates and inconsistent reads.

Example:

• Without concurrency control: Two users update the same record simultaneously and one update overwrites the other.
• With concurrency control: The DBMS uses locks or timestamps to ensure updates occur sequentially and data remains correct.

Concurrency Problems in DBMS

When multiple transactions execute concurrently, several problems may occur:

• Dirty Read: A transaction reads uncommitted data from another transaction that may later roll back.
• Lost Update: Two transactions update the same data and one update overwrites the other.
• Inconsistent Read: A transaction reads the same data multiple times and the data changes in between reads.

Concurrency Control Protocols

Concurrency control protocols define rules to ensure correct and consistent execution of transactions. The main protocols are:

1. Lock-Based Concurrency Control:

• Uses locks to restrict access to data items during a transaction. Common types include shared locks (read) and exclusive locks (write).
• Ensures serializability and prevents conflicts.
• Example: Two-Phase Locking (2PL) guarantees that once a transaction releases a lock, it cannot obtain any new locks.

1.1 Two-Phase Locking (2PL)

Concept

Each transaction must acquire locks before accessing data items:

• A Shared (S) lock before reading.
• An Exclusive (X) lock before writing.

The transaction has:

• A growing phase — acquiring locks.
• A shrinking phase — releasing locks.

Once a transaction releases a lock, it cannot acquire new ones.

Effect

If T₁ holds an exclusive lock on X, T₂ cannot read or write X until T₁ commits. This prevents lost updates and temporary modifications.

Scenario

Initial balance of Account A = 100.

Transaction T1 (Deposit 50)

Transaction T2 (Read Balance)

| Step | T1                                          | T2                                 | Explanation                                              |
| ---- | ------------------------------------------- | ---------------------------------- | -------------------------------------------------------- |
| 1    | T1 **acquires a lock** on A (growing phase) | -                                  | T1 intends to update, so locking is needed.              |
| 2    | T1 reads A = 100                            | -                                  |                                                          |
| 3    | T1 updates A = 150                          | -                                  |                                                          |
| 4    | -                                           | T2 **requests lock** to read A     | T2 must wait because T1 still holds the lock.            |
| 5    | T1 **releases the lock** (shrinking phase)  | -                                  | Now T1 has completed work, cannot acquire new locks now. |
| 6    | -                                           | T2 acquires lock and reads A = 150 | T2 finally gets a consistent value.                      |

Now, no conflicts occur and the final balance is correct.

1.2 Strict Two-Phase Locking

An enhanced version where transactions hold all locks until commit. This ensures no transaction can read uncommitted data, preventing cascading rollbacks.

Scenario

Initial balance of A = 100.

Transaction T1 (Update)

Transaction T2 (Read)

| Step | T1                                   | T2               | Explanation                                           |
| ---- | ------------------------------------ | ---------------- | ----------------------------------------------------- |
| 1    | T1 acquires **write lock** on A      | -                |                                                       |
| 2    | T1 updates A = 200                   | -                |                                                       |
| 3    | T2 tries to read A                   | T2 must wait     | Because T1's write lock is still locked until commit. |
| 4    | T1 **commits** and **releases lock** | -                | Only now data becomes visible.                        |
| 5    | -                                    | T2 reads A = 200 | T2 gets the committed correct value.                  |

2. Timestamp-Based Concurrency Control:

• Each transaction is assigned a timestamp.
• The DBMS uses these timestamps to order transactions and prevent conflicts based on their start time