Home / Database / Concurrency Control

Concurrency Control

Overview

Concurrency Control manages simultaneous access to a database by multiple users or applications. It ensures that concurrent transactions execute correctly and maintain data consistency without interfering with each other.

Without proper concurrency control, simultaneous database operations can lead to lost updates, dirty reads, and inconsistent data states.

Why Concurrency Control is Needed

In multi-user environments, concurrent transactions can cause problems:

1. Lost Update Problem

When two transactions update the same data simultaneously, one update may overwrite the other.

Lost Update Example
Initial Balance: $1000

Transaction 1:            Transaction 2:
Read: $1000              Read: $1000
Add: $100                Subtract: $50
Write: $1100            Write: $950

Result: $950 (incorrect!)
- Transaction 1's $100 addition is lost
- Should be: $1000 + $100 - $50 = $1050

2. Dirty Read Problem

A transaction reads data that has been modified by another uncommitted transaction.

Dirty Read Example
Transaction 1:            Transaction 2:
BEGIN                    BEGIN
UPDATE Balance           SELECT Balance
SET Balance = 500        -- Reads 500 (uncommitted!)
(Not yet committed)

ROLLBACK                -- Transaction 1 rolls back
-- Balance restored     -- Transaction 2 saw
-- to original value    -- incorrect data!

3. Non-Repeatable Read

A transaction reads the same data twice and gets different values because another transaction modified it.

Non-Repeatable Read Example
Transaction 1:            Transaction 2:
BEGIN                    BEGIN
SELECT Balance           UPDATE Balance
-- Reads $1000           SET Balance = 1500
                         COMMIT

SELECT Balance
-- Reads $1500 (different!)
-- Same query, different result

4. Phantom Read

A transaction re-executes a query and finds additional rows that were inserted by another committed transaction.

Phantom Read Example
Transaction 1:            Transaction 2:
BEGIN                    BEGIN
SELECT COUNT(*)          INSERT INTO Orders
FROM Orders              VALUES (...)
-- Returns 100           COMMIT

SELECT COUNT(*)
FROM Orders
-- Returns 101 (new row appeared!)

Locking Mechanisms

Locks prevent concurrent transactions from accessing the same data simultaneously:

1. Shared Lock (Read Lock)

Allows multiple transactions to read data simultaneously, but prevents writes.

Shared Lock
Transaction 1:            Transaction 2:
Acquire S-Lock           Acququire S-Lock
READ data                READ data
(OK - both can read)     (OK - both can read)

No transaction can WRITE while shared locks exist

2. Exclusive Lock (Write Lock)

Allows only one transaction to read and write data, preventing other transactions from accessing it.

Exclusive Lock
Transaction 1:            Transaction 2:
Acquire X-Lock           
READ & WRITE data        

                         Try to READ
                         -- WAIT (blocked)
                         
Transaction 1 COMMITs
-- Lock released
                         -- Now can proceed

3. Lock Granularity

Locks can be applied at different levels:

Isolation Levels

Isolation levels control the degree to which transactions are isolated from each other. Higher isolation prevents more concurrency problems but may reduce performance.

1. Read Uncommitted

Lowest isolation level. Allows dirty reads, non-repeatable reads, and phantom reads.

Read Uncommitted
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Can read uncommitted changes
-- No locks on reads
-- Fastest but least safe

2. Read Committed

Prevents dirty reads but allows non-repeatable reads and phantom reads. Most databases default to this level.

Read Committed
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Can only read committed data
-- Prevents dirty reads
-- Still allows non-repeatable reads

3. Repeatable Read

Prevents dirty reads and non-repeatable reads but may allow phantom reads.

Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Same query returns same results
-- Prevents non-repeatable reads
-- May still have phantom reads

4. Serializable

Highest isolation level. Prevents all concurrency problems but may significantly reduce performance due to increased locking.

Serializable
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Transactions execute as if serially
-- Prevents all concurrency problems
-- Most restrictive, slowest performance

Isolation Levels Comparison

Isolation Level Dirty Read Non-Repeatable Phantom Read
Read Uncommitted Possible Possible Possible
Read Committed Prevented Possible Possible
Repeatable Read Prevented Prevented Possible
Serializable Prevented Prevented Prevented

Deadlocks

A deadlock occurs when two or more transactions are waiting for each other to release locks, creating a circular wait condition.

Deadlock Example
Transaction 1:            Transaction 2:
Lock Row A               Lock Row B
Wait for Row B           Wait for Row A
    ↓                         ↓
    └─────── DEADLOCK ────────┘

Both transactions are blocked
Neither can proceed
Database must detect and resolve

Deadlock Prevention

Optimistic vs Pessimistic Concurrency

Pessimistic Concurrency Control

Assumes conflicts are likely, so locks are acquired immediately and held until transaction completes.

Pessimistic Locking
BEGIN TRANSACTION;

SELECT * FROM Accounts 
WHERE AccountID = 'A'
FOR UPDATE;  -- Lock immediately

UPDATE Accounts SET Balance = ...
COMMIT;  -- Release lock

Optimistic Concurrency Control

Assumes conflicts are rare. Checks for conflicts at commit time using version numbers or timestamps.

Optimistic Locking
-- Read with version
SELECT Balance, Version 
FROM Accounts 
WHERE AccountID = 'A';

-- Later, update with version check
UPDATE Accounts 
SET Balance = 1500, Version = Version + 1
WHERE AccountID = 'A' 
  AND Version = @original_version;

-- If Version changed, another transaction modified it
-- Update fails, must retry

Best Practices

Common Issues

Next Steps

Learn about improving database speed with Performance Tuning, or understand transaction basics in Transactions.

← Transactions Performance →