Dbms Advantages - Learning Module

Loading content...

0/241

Concurrent Access: Enabling Multi-User Database Systems

When Two Users Collide

Black Friday, 2023. An e-commerce platform shows a hot product with '1 unit left in stock.' Fifty thousand users click 'Buy Now' within the same second.

Without proper concurrency control, the result would be chaos:

50,000 orders created for 1 item
49,999 angry customers expecting delivery
$2.5 million in refunds and compensation
A PR disaster on social media

This scenario illustrates why concurrent access control is critical. Modern databases serve thousands of simultaneous users, all reading and writing shared data. Without sophisticated mechanisms to coordinate this access, data corruption, lost updates, and inconsistent reads would be constant.

Concurrent access is the ability of a DBMS to handle multiple users or applications accessing the database simultaneously while maintaining data consistency and integrity. It's one of the most technically complex—and most essential—capabilities of modern database systems.

What You Will Learn

By the end of this page, you will understand concurrency problems, locking mechanisms, isolation levels, and how DBMS coordinates simultaneous access. You'll learn why concurrent access was impossible with file-based systems and how modern databases achieve it transparently.

The Concurrency Problem

When multiple transactions access shared data simultaneously, several problems can occur if access isn't properly coordinated. These concurrency anomalies can corrupt data or provide incorrect results to users.

Why does this happen?

Database operations aren't instantaneous. Reading a row, computing a value, and writing back takes time—microseconds to milliseconds. In that window, another transaction can interfere. Without coordination, both transactions operate on stale data, and one overwrites the other's changes.

Concurrency Anomalies
Anomaly	Description	Example	Consequence
Lost Update	Two transactions read same value; both update; one overwrites the other	Two clerks read balance $100, both add $50. Final: $150 not $200.	Data permanently incorrect. Money lost.
Dirty Read	Reading uncommitted data that may be rolled back	Read balance $150 during transfer, but transfer fails and rolls back to $100.	Decisions based on data that never existed.
Non-Repeatable Read	Same query returns different values within one transaction	Read price $99. Someone updates to $129. Read price again: $129.	Inconsistent logic within single operation.
Phantom Read	Query result set changes as other transactions insert/delete	Count orders: 100. Another insert happens. Count again: 101.	Aggregates and reports inconsistent.

Lost Update: The Classic ProblemTwo bank tellers simultaneously process deposits to the same account.

Input

Output

You Can't Test for Concurrency Bugs

Concurrency bugs are notoriously difficult to detect. They depend on precise timing—millisecond differences in when operations execute. A system might work perfectly for months, then fail under peak load when timing windows align. This is why built-in DBMS concurrency control is essential; you can't reliably test your way to correctness.

Locking Mechanisms

The traditional approach to concurrency control is locking. Before a transaction can access data, it must acquire a lock. The lock prevents conflicting access by other transactions until the holding transaction completes.

Lock Types:

Fundamental Lock Types

•Shared Lock (S-Lock / Read Lock) — Allows read access. Multiple transactions can hold shared locks on the same data simultaneously. Prevents writes while any shared lock is held.
•Exclusive Lock (X-Lock / Write Lock) — Allows read and write access. Only one transaction can hold an exclusive lock. Blocks all other locks.
•Update Lock (U-Lock) — Indicates intent to update. Prevents deadlocks when reading before writing. Upgrades to X-lock when write occurs.

Lock Compatibility Matrix
Requesting →	S (Shared)	X (Exclusive)
S (Shared) held	✅ Granted	❌ Wait
X (Exclusive) held	❌ Wait	❌ Wait

locking_example.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Explicit locking to prevent lost update
 
-- SESSION 1: Teller A deposits $200
BEGIN TRANSACTION;
 
-- Acquire exclusive lock on the row
SELECT Balance FROM Accounts WHERE AccountID = 1001 FOR UPDATE;
-- Returns: Balance = $1,000
-- Row is now exclusively locked - Teller B must wait
 
UPDATE Accounts SET Balance = Balance + 200 WHERE AccountID = 1001;
 
COMMIT;
-- Lock released. Teller B can now proceed.
 
 
-- SESSION 2: Teller B deposits $300 (executes concurrently)
BEGIN TRANSACTION;
 
-- Attempt to acquire lock... WAITS here until Session 1 commits
SELECT Balance FROM Accounts WHERE AccountID = 1001 FOR UPDATE;
-- After Session 1 commits, returns: Balance = $1,200 (the updated value!)
 
UPDATE Accounts SET Balance = Balance + 300 WHERE AccountID = 1001;
 
COMMIT;
-- Final balance: $1,500 (correct!)
 
 
-- Different lock levels:
 
-- Shared lock (for read-only access)
SELECT * FROM Accounts WHERE AccountID = 1001 FOR SHARE;
-- Others can also read, but no one can write
 
-- Exclusive lock (for read-then-write)
SELECT * FROM Accounts WHERE AccountID = 1001 FOR UPDATE;
-- No one else can read or write
 
-- Skip locked rows (don't wait)
SELECT * FROM Accounts FOR UPDATE SKIP LOCKED;
-- Process only unlocked rows - useful for job queues
 
-- No wait (fail immediately if locked)
SELECT * FROM Accounts WHERE AccountID = 1001 FOR UPDATE NOWAIT;
-- Throws error immediately if row is locked

Converting Mermaid diagram...

Lock Granularity

Locks can be acquired at different granularities: row-level (finest, most concurrent), page-level, table-level (coarsest, least concurrent). Modern DBMS typically use row-level locking by default, escalating to page or table locks if too many individual row locks are held.

Deadlocks: When Transactions Block Each Other

Locking introduces a new problem: deadlock. A deadlock occurs when two or more transactions are each waiting for locks held by the other, creating a circular dependency where none can proceed.

Example:

Transaction A locks Row 1, needs Row 2
Transaction B locks Row 2, needs Row 1
A waits for B. B waits for A. Both wait forever.

Converting Mermaid diagram...

Deadlock Handling Strategies
Strategy	How It Works	Trade-offs
Deadlock Detection	DBMS periodically checks for cycles. Kills one transaction to break cycle.	Standard approach. Victim transaction must retry.
Deadlock Prevention	Enforce ordering: all transactions must lock resources in same order.	No deadlocks possible, but requires careful programming.
Lock Timeouts	If lock not acquired within timeout, abort transaction.	Simple but may abort transactions that would succeed.
Wait-Die / Wound-Wait	Older transactions get priority. Younger ones abort/wait based on scheme.	Avoids indefinite waiting but may abort many transactions.

deadlock_example.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- DEADLOCK SCENARIO
 
-- SESSION 1
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1001;
-- Acquired X-lock on Account 1001
 
-- SESSION 2 (concurrent)
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 1002;
-- Acquired X-lock on Account 1002
 
-- SESSION 1 (continues)
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 1002;
-- WAITS: Session 2 holds lock on Account 1002
 
-- SESSION 2 (continues)
UPDATE Accounts SET Balance = Balance + 200 WHERE AccountID = 1001;
-- WAITS: Session 1 holds lock on Account 1001
-- DEADLOCK! Both waiting for each other.
 
-- DBMS detects deadlock and kills one transaction:
-- "ERROR: deadlock detected
--  DETAIL: Process 12345 waits for ShareLock on transaction 12346;
--          Process 12346 waits for ShareLock on transaction 12345.
--  HINT: See server log for query details."
 
 
-- PREVENTION: Access resources in consistent order
-- Always lock accounts in ascending AccountID order
 
-- SESSION 1 (correct)
BEGIN TRANSACTION;
SELECT * FROM Accounts WHERE AccountID IN (1001, 1002) 
    ORDER BY AccountID FOR UPDATE;  -- Locks both in order
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1001;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 1002;
COMMIT;
 
-- SESSION 2 (correct)
BEGIN TRANSACTION;
SELECT * FROM Accounts WHERE AccountID IN (1001, 1002)
    ORDER BY AccountID FOR UPDATE;  -- Waits for Session 1 to complete
UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 1002;
UPDATE Accounts SET Balance = Balance + 200 WHERE AccountID = 1001;
COMMIT;
 
-- No deadlock possible: consistent ordering prevents cycles

Don't Fear Deadlocks

Deadlocks are normal in concurrent systems—not bugs per se. The DBMS handles them automatically by killing a victim transaction. Your application must be prepared to retry aborted transactions. Design for idempotency and make retry logic part of your error handling.

Isolation Levels: Balancing Consistency and Performance

Full isolation (where every transaction appears to run alone) is expensive. It requires extensive locking, reducing concurrency and throughput. Isolation levels provide a spectrum of trade-offs between consistency and performance.

SQL standard defines four isolation levels, from least to most restrictive:

SQL Standard Isolation Levels
Level	Dirty Read	Non-Repeatable Read	Phantom Read	Performance
Read Uncommitted	❌ Possible	❌ Possible	❌ Possible	Fastest
Read Committed	✅ Prevented	❌ Possible	❌ Possible	Fast
Repeatable Read	✅ Prevented	✅ Prevented	❌ Possible	Moderate
Serializable	✅ Prevented	✅ Prevented	✅ Prevented	Slowest

Understanding Each Level

•Read Uncommitted — Transactions see other transactions' uncommitted changes. Rarely used. Appropriate only when approximate data is acceptable and performance is critical.
•Read Committed — Transactions only see committed data. Each query sees a fresh snapshot. Default in PostgreSQL, Oracle, SQL Server. Good balance for most applications.
•Repeatable Read — Once a row is read, subsequent reads in the same transaction return the same data. Useful for reports requiring consistency within the transaction.
•Serializable — Transactions behave as if executed sequentially. No anomalies possible. Use for critical operations where correctness overrides performance.

isolation_levels.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- Setting isolation levels
 
-- Per-transaction (PostgreSQL)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- or
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
-- Session-wide (PostgreSQL)
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
 
-- READ COMMITTED behavior (default)
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT salary FROM Employees WHERE id = 100;  -- Returns $50,000
 
-- Meanwhile, another transaction updates and commits:
-- UPDATE Employees SET salary = 55000 WHERE id = 100; COMMIT;
 
SELECT salary FROM Employees WHERE id = 100;  -- Returns $55,000 (new value!)
COMMIT;
 
 
-- REPEATABLE READ behavior
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT salary FROM Employees WHERE id = 100;  -- Returns $50,000
 
-- Meanwhile, another transaction updates and commits:
-- UPDATE Employees SET salary = 55000 WHERE id = 100; COMMIT;
 
SELECT salary FROM Employees WHERE id = 100;  -- Still returns $50,000!
COMMIT;
 
 
-- SERIALIZABLE example: Preventing lost updates automatically
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT balance INTO @bal FROM Accounts WHERE id = 1001;  -- $1,000
-- Application computes: @bal + 200
 
-- Meanwhile, another SERIALIZABLE transaction also reads $1,000 and 
-- tries to update based on that... DBMS will abort one transaction!
 
UPDATE Accounts SET balance = @bal + 200 WHERE id = 1001;
COMMIT;  -- May fail if conflict detected: "could not serialize access"

Choosing the Right Level

Start with Read Committed (the default). Move to Repeatable Read for reports or algorithms requiring internal consistency. Use Serializable only for critical operations where you need absolute correctness. Never use Read Uncommitted unless you fully understand the implications.

Multi-Version Concurrency Control (MVCC)

Modern databases like PostgreSQL, MySQL (InnoDB), Oracle, and SQL Server implement Multi-Version Concurrency Control (MVCC) as an alternative to pure locking. MVCC provides readers don't block writers, writers don't block readers—dramatically improving concurrency.

How MVCC Works:

Instead of overwriting data in place, MVCC creates new versions of rows. Each transaction sees a consistent snapshot based on when it started. Older versions are retained until no transaction needs them.

Converting Mermaid diagram...

MVCC Advantages

•Readers never block writers — Read transactions don't acquire locks on data
•Writers never block readers — Writes create new versions; reads see old versions
•Consistent snapshots — Each transaction sees data as of its start time
•Higher concurrency — Dramatically better throughput than lock-only approach

MVCC Trade-offs

•Storage overhead — Multiple row versions consume space
•Vacuum/cleanup required — Old versions must be garbage collected
•Transaction IDs — Limited counter requires wraparound handling
•Update anomalies still possible — Writers still conflict with writers

mvcc_behavior.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- MVCC in action: Readers don't block writers
 
-- SESSION 1 (long-running report)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) FROM Accounts;  -- Returns $1,000,000
-- Takes 30 seconds to process...
 
-- SESSION 2 (concurrent update)
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance + 1000 WHERE AccountID = 1001;
COMMIT;  -- Succeeds immediately! Not blocked by Session 1.
 
-- SESSION 1 (continues)
SELECT SUM(balance) FROM Accounts;  -- Still returns $1,000,000!
-- MVCC provides consistent snapshot from transaction start
COMMIT;
 
 
-- SESSION 3 (new transaction)
BEGIN TRANSACTION;
SELECT SUM(balance) FROM Accounts;  -- Returns $1,001,000 (sees Session 2's update)
COMMIT;
 
 
-- MVCC and update conflicts
-- When two writers touch the same row:
 
-- SESSION A
BEGIN TRANSACTION;
UPDATE Accounts SET balance = 1000 WHERE AccountID = 5;
 
-- SESSION B  
BEGIN TRANSACTION;
UPDATE Accounts SET balance = 2000 WHERE AccountID = 5;  
-- WAITS: Session A has uncommitted update to same row
 
-- SESSION A
COMMIT;
 
-- SESSION B (now proceeds)
-- Depending on isolation level:
-- READ COMMITTED: Sees Session A's commit, updates to 2000
-- SERIALIZABLE: May abort with serialization failure
 
 
-- PostgreSQL: Viewing row versions (ctid is physical row location)
SELECT ctid, xmin, xmax, * FROM Accounts WHERE AccountID = 1001;
-- ctid: physical location (page, tuple)
-- xmin: transaction ID that created this version
-- xmax: transaction ID that deleted/updated (0 = current version)

MVCC Everywhere

MVCC is so successful that virtually all modern relational databases use it. PostgreSQL, Oracle, MySQL InnoDB, SQL Server (with READ_COMMITTED_SNAPSHOT), and even non-relational databases like MongoDB and CockroachDB implement MVCC. It's the foundation of scalable concurrent database access.

Contrast with File-Based Systems

File-based systems had primitive concurrency support, if any. The challenges that DBMS solves automatically were enormous manual burdens in file-based architectures.

File-Based Concurrency Problems

•Whole-file locking — One user locks entire file, blocking all others
•Application-managed locks — Each program implements own locking, inconsistently
•No transaction support — Partial updates on crash leave files corrupted
•No isolation — Reads see in-progress writes immediately
•Lock leakage — Program crash leaves locks held forever

DBMS Concurrency Solutions

•Row-level locking — Users only block if accessing same rows
•Centralized lock management — DBMS consistently handles all locking
•ACID transactions — All-or-nothing semantics with recovery
•Configurable isolation — Choose consistency vs. performance trade-off
•Automatic cleanup — Locks released on transaction end or connection loss

The 1980s Airline Reservation NightmareBefore proper DBMS concurrency control, airline reservation systems faced constant data corruption.

Input

Output

The DBMS Revolution

The inability of file-based systems to handle concurrent access was a primary driver of DBMS adoption. Modern databases handle thousands of concurrent transactions per second with fine-grained locking, automatic deadlock resolution, and configurable isolation—all transparently.

Practical Concurrency Considerations

Understanding concurrency control theory is important, but applying it correctly in practice requires awareness of common patterns and pitfalls.

Best Practices for Concurrent Database Access

•Keep transactions short — Long transactions hold locks longer, blocking others. Acquire locks, do work, commit. Don't hold transactions open during user interactions.
•Access resources in consistent order — If multiple resources are needed, always acquire them in the same order (e.g., by ID ascending) to prevent deadlocks.
•Use SELECT FOR UPDATE sparingly — Only lock rows you intend to modify. Unnecessary locking reduces concurrency.
•Handle deadlocks gracefully — Catch serialization errors and retry. Implement exponential backoff for retries.
•Choose appropriate isolation level — Stronger isn't always better. Read Committed is fine for most CRUD operations.
•Use optimistic locking for low-conflict scenarios — Check version/timestamp on update rather than locking upfront. Better for read-heavy workloads.

optimistic_locking.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- OPTIMISTIC LOCKING: No upfront locks, check before commit
 
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100),
    Price DECIMAL(10,2),
    Stock INT,
    Version INT DEFAULT 1  -- Version counter for optimistic locking
);
 
-- Read product (no lock)
SELECT ProductID, Name, Price, Stock, Version 
FROM Products 
WHERE ProductID = 100;
-- Returns: ProductID=100, Name='Widget', Price=29.99, Stock=50, Version=7
 
-- Application processes... (could be long, no lock held)
 
-- Update with version check
UPDATE Products 
SET Stock = 45, 
    Version = Version + 1
WHERE ProductID = 100 
  AND Version = 7;  -- The version we read earlier
 
-- Check if update succeeded
-- If 0 rows affected: someone else modified while we worked
-- Retry: re-read, re-process, re-update
 
 
-- TIMESTAMP-BASED OPTIMISTIC LOCKING
CREATE TABLE Documents (
    DocID INT PRIMARY KEY,
    Content TEXT,
    ModifiedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Read
SELECT DocID, Content, ModifiedAt FROM Documents WHERE DocID = 50;
-- Returns: ModifiedAt = '2024-01-15 10:30:00'
 
-- Update with timestamp check  
UPDATE Documents
SET Content = 'New content',
    ModifiedAt = CURRENT_TIMESTAMP
WHERE DocID = 50
  AND ModifiedAt = '2024-01-15 10:30:00';
 
-- If 0 rows: conflict detected. Refresh and retry.
 
 
-- APPLICATION PATTERN (pseudocode):
-- function updateProduct(id, newStock):
--     MAX_RETRIES = 3
--     for attempt in range(MAX_RETRIES):
--         product = SELECT ... WHERE id = :id
--         result = UPDATE ... WHERE id = :id AND version = product.version
--         if result.rowsAffected == 1:
--             return SUCCESS
--         if attempt < MAX_RETRIES - 1:
--             sleep(random(50, 100) * attempt)  # Backoff
--     return CONFLICT_ERROR

Optimistic vs Pessimistic

Pessimistic locking (SELECT FOR UPDATE): Best when conflicts are common. Lock immediately, work, release. Optimistic locking (version checks): Best when conflicts are rare. Read freely, check on write, retry if conflict. Most web applications benefit from optimistic locking because reads far exceed writes.

Summary: Concurrent Access

Concurrent access is what transforms a database from a personal data store into a multi-user system. DBMS provides sophisticated mechanisms to coordinate simultaneous access while maintaining data consistency. Let's consolidate the key concepts:

Key Takeaways

•Concurrency anomalies are dangerous — Lost updates, dirty reads, non-repeatable reads, and phantoms can corrupt data and produce incorrect results.
•Locking prevents conflicts — Shared and exclusive locks coordinate access. Transactions wait for conflicting locks.
•Deadlocks are handled automatically — DBMS detects circular waits and kills victim transactions. Applications must be prepared to retry.
•Isolation levels provide trade-offs — From Read Uncommitted (fastest, least safe) to Serializable (slowest, safest). Choose based on requirements.
•MVCC improves concurrency — Multiple row versions allow readers and writers to operate without blocking each other.
•File systems couldn't compete — Primitive file locking provided nothing close to DBMS concurrency control, driving database adoption.
•Optimistic locking for web apps — Low-conflict scenarios benefit from checking versions on update rather than locking upfront.

What's Next:

We've explored how DBMS maintains correctness during concurrent access. But what about failures—power outages, disk crashes, hardware failures? The next page examines Security and Backup—how DBMS protects data from loss, unauthorized access, and disasters.

Page Complete

You now understand how DBMS enables safe concurrent access—the foundation of multi-user database systems. This capability transformed databases from single-user data stores into the shared infrastructure powering modern applications serving millions of users.

Concurrent Access: Enabling Multi-User Database Systems

When Two Users Collide

Black Friday, 2023. An e-commerce platform shows a hot product with '1 unit left in stock.' Fifty thousand users click 'Buy Now' within the same second.

Without proper concurrency control, the result would be chaos:

50,000 orders created for 1 item
49,999 angry customers expecting delivery
$2.5 million in refunds and compensation
A PR disaster on social media

What You Will Learn

The Concurrency Problem

Why does this happen?

Concurrency Anomalies
Anomaly	Description	Example	Consequence
Lost Update	Two transactions read same value; both update; one overwrites the other	Two clerks read balance $100, both add $50. Final: $150 not $200.	Data permanently incorrect. Money lost.
Dirty Read	Reading uncommitted data that may be rolled back	Read balance $150 during transfer, but transfer fails and rolls back to $100.	Decisions based on data that never existed.
Non-Repeatable Read	Same query returns different values within one transaction	Read price $99. Someone updates to $129. Read price again: $129.	Inconsistent logic within single operation.
Phantom Read	Query result set changes as other transactions insert/delete	Count orders: 100. Another insert happens. Count again: 101.	Aggregates and reports inconsistent.

Lost Update: The Classic ProblemTwo bank tellers simultaneously process deposits to the same account.

Input

Output

You Can't Test for Concurrency Bugs

Locking Mechanisms

Lock Types:

Fundamental Lock Types

•Shared Lock (S-Lock / Read Lock) — Allows read access. Multiple transactions can hold shared locks on the same data simultaneously. Prevents writes while any shared lock is held.
•Exclusive Lock (X-Lock / Write Lock) — Allows read and write access. Only one transaction can hold an exclusive lock. Blocks all other locks.
•Update Lock (U-Lock) — Indicates intent to update. Prevents deadlocks when reading before writing. Upgrades to X-lock when write occurs.

Lock Compatibility Matrix
Requesting →	S (Shared)	X (Exclusive)
S (Shared) held	✅ Granted	❌ Wait
X (Exclusive) held	❌ Wait	❌ Wait

locking_example.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Explicit locking to prevent lost update
 
-- SESSION 1: Teller A deposits $200
BEGIN TRANSACTION;
 
-- Acquire exclusive lock on the row
SELECT Balance FROM Accounts WHERE AccountID = 1001 FOR UPDATE;
-- Returns: Balance = $1,000
-- Row is now exclusively locked - Teller B must wait
 
UPDATE Accounts SET Balance = Balance + 200 WHERE AccountID = 1001;
 
COMMIT;
-- Lock released. Teller B can now proceed.
 
 
-- SESSION 2: Teller B deposits $300 (executes concurrently)
BEGIN TRANSACTION;
 
-- Attempt to acquire lock... WAITS here until Session 1 commits
SELECT Balance FROM Accounts WHERE AccountID = 1001 FOR UPDATE;
-- After Session 1 commits, returns: Balance = $1,200 (the updated value!)
 
UPDATE Accounts SET Balance = Balance + 300 WHERE AccountID = 1001;
 
COMMIT;
-- Final balance: $1,500 (correct!)
 
 
-- Different lock levels:
 
-- Shared lock (for read-only access)
SELECT * FROM Accounts WHERE AccountID = 1001 FOR SHARE;
-- Others can also read, but no one can write
 
-- Exclusive lock (for read-then-write)
SELECT * FROM Accounts WHERE AccountID = 1001 FOR UPDATE;
-- No one else can read or write
 
-- Skip locked rows (don't wait)
SELECT * FROM Accounts FOR UPDATE SKIP LOCKED;
-- Process only unlocked rows - useful for job queues
 
-- No wait (fail immediately if locked)
SELECT * FROM Accounts WHERE AccountID = 1001 FOR UPDATE NOWAIT;
-- Throws error immediately if row is locked

Converting Mermaid diagram...

Lock Granularity

Deadlocks: When Transactions Block Each Other

Locking introduces a new problem: deadlock. A deadlock occurs when two or more transactions are each waiting for locks held by the other, creating a circular dependency where none can proceed.

Example:

Transaction A locks Row 1, needs Row 2
Transaction B locks Row 2, needs Row 1
A waits for B. B waits for A. Both wait forever.

Converting Mermaid diagram...

Deadlock Handling Strategies
Strategy	How It Works	Trade-offs
Deadlock Detection	DBMS periodically checks for cycles. Kills one transaction to break cycle.	Standard approach. Victim transaction must retry.
Deadlock Prevention	Enforce ordering: all transactions must lock resources in same order.	No deadlocks possible, but requires careful programming.
Lock Timeouts	If lock not acquired within timeout, abort transaction.	Simple but may abort transactions that would succeed.
Wait-Die / Wound-Wait	Older transactions get priority. Younger ones abort/wait based on scheme.	Avoids indefinite waiting but may abort many transactions.

deadlock_example.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- DEADLOCK SCENARIO
 
-- SESSION 1
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1001;
-- Acquired X-lock on Account 1001
 
-- SESSION 2 (concurrent)
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 1002;
-- Acquired X-lock on Account 1002
 
-- SESSION 1 (continues)
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 1002;
-- WAITS: Session 2 holds lock on Account 1002
 
-- SESSION 2 (continues)
UPDATE Accounts SET Balance = Balance + 200 WHERE AccountID = 1001;
-- WAITS: Session 1 holds lock on Account 1001
-- DEADLOCK! Both waiting for each other.
 
-- DBMS detects deadlock and kills one transaction:
-- "ERROR: deadlock detected
--  DETAIL: Process 12345 waits for ShareLock on transaction 12346;
--          Process 12346 waits for ShareLock on transaction 12345.
--  HINT: See server log for query details."
 
 
-- PREVENTION: Access resources in consistent order
-- Always lock accounts in ascending AccountID order
 
-- SESSION 1 (correct)
BEGIN TRANSACTION;
SELECT * FROM Accounts WHERE AccountID IN (1001, 1002) 
    ORDER BY AccountID FOR UPDATE;  -- Locks both in order
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1001;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 1002;
COMMIT;
 
-- SESSION 2 (correct)
BEGIN TRANSACTION;
SELECT * FROM Accounts WHERE AccountID IN (1001, 1002)
    ORDER BY AccountID FOR UPDATE;  -- Waits for Session 1 to complete
UPDATE Accounts SET Balance = Balance - 200 WHERE AccountID = 1002;
UPDATE Accounts SET Balance = Balance + 200 WHERE AccountID = 1001;
COMMIT;
 
-- No deadlock possible: consistent ordering prevents cycles

Don't Fear Deadlocks

Isolation Levels: Balancing Consistency and Performance

SQL standard defines four isolation levels, from least to most restrictive:

SQL Standard Isolation Levels
Level	Dirty Read	Non-Repeatable Read	Phantom Read	Performance
Read Uncommitted	❌ Possible	❌ Possible	❌ Possible	Fastest
Read Committed	✅ Prevented	❌ Possible	❌ Possible	Fast
Repeatable Read	✅ Prevented	✅ Prevented	❌ Possible	Moderate
Serializable	✅ Prevented	✅ Prevented	✅ Prevented	Slowest

Understanding Each Level

•Read Uncommitted — Transactions see other transactions' uncommitted changes. Rarely used. Appropriate only when approximate data is acceptable and performance is critical.
•Read Committed — Transactions only see committed data. Each query sees a fresh snapshot. Default in PostgreSQL, Oracle, SQL Server. Good balance for most applications.
•Repeatable Read — Once a row is read, subsequent reads in the same transaction return the same data. Useful for reports requiring consistency within the transaction.
•Serializable — Transactions behave as if executed sequentially. No anomalies possible. Use for critical operations where correctness overrides performance.

isolation_levels.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- Setting isolation levels
 
-- Per-transaction (PostgreSQL)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- or
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
-- Session-wide (PostgreSQL)
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
 
-- READ COMMITTED behavior (default)
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT salary FROM Employees WHERE id = 100;  -- Returns $50,000
 
-- Meanwhile, another transaction updates and commits:
-- UPDATE Employees SET salary = 55000 WHERE id = 100; COMMIT;
 
SELECT salary FROM Employees WHERE id = 100;  -- Returns $55,000 (new value!)
COMMIT;
 
 
-- REPEATABLE READ behavior
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT salary FROM Employees WHERE id = 100;  -- Returns $50,000
 
-- Meanwhile, another transaction updates and commits:
-- UPDATE Employees SET salary = 55000 WHERE id = 100; COMMIT;
 
SELECT salary FROM Employees WHERE id = 100;  -- Still returns $50,000!
COMMIT;
 
 
-- SERIALIZABLE example: Preventing lost updates automatically
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT balance INTO @bal FROM Accounts WHERE id = 1001;  -- $1,000
-- Application computes: @bal + 200
 
-- Meanwhile, another SERIALIZABLE transaction also reads $1,000 and 
-- tries to update based on that... DBMS will abort one transaction!
 
UPDATE Accounts SET balance = @bal + 200 WHERE id = 1001;
COMMIT;  -- May fail if conflict detected: "could not serialize access"

Choosing the Right Level

Multi-Version Concurrency Control (MVCC)

How MVCC Works:

Converting Mermaid diagram...

MVCC Advantages

•Readers never block writers — Read transactions don't acquire locks on data
•Writers never block readers — Writes create new versions; reads see old versions
•Consistent snapshots — Each transaction sees data as of its start time
•Higher concurrency — Dramatically better throughput than lock-only approach

MVCC Trade-offs

•Storage overhead — Multiple row versions consume space
•Vacuum/cleanup required — Old versions must be garbage collected
•Transaction IDs — Limited counter requires wraparound handling
•Update anomalies still possible — Writers still conflict with writers

mvcc_behavior.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- MVCC in action: Readers don't block writers
 
-- SESSION 1 (long-running report)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) FROM Accounts;  -- Returns $1,000,000
-- Takes 30 seconds to process...
 
-- SESSION 2 (concurrent update)
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance + 1000 WHERE AccountID = 1001;
COMMIT;  -- Succeeds immediately! Not blocked by Session 1.
 
-- SESSION 1 (continues)
SELECT SUM(balance) FROM Accounts;  -- Still returns $1,000,000!
-- MVCC provides consistent snapshot from transaction start
COMMIT;
 
 
-- SESSION 3 (new transaction)
BEGIN TRANSACTION;
SELECT SUM(balance) FROM Accounts;  -- Returns $1,001,000 (sees Session 2's update)
COMMIT;
 
 
-- MVCC and update conflicts
-- When two writers touch the same row:
 
-- SESSION A
BEGIN TRANSACTION;
UPDATE Accounts SET balance = 1000 WHERE AccountID = 5;
 
-- SESSION B  
BEGIN TRANSACTION;
UPDATE Accounts SET balance = 2000 WHERE AccountID = 5;  
-- WAITS: Session A has uncommitted update to same row
 
-- SESSION A
COMMIT;
 
-- SESSION B (now proceeds)
-- Depending on isolation level:
-- READ COMMITTED: Sees Session A's commit, updates to 2000
-- SERIALIZABLE: May abort with serialization failure
 
 
-- PostgreSQL: Viewing row versions (ctid is physical row location)
SELECT ctid, xmin, xmax, * FROM Accounts WHERE AccountID = 1001;
-- ctid: physical location (page, tuple)
-- xmin: transaction ID that created this version
-- xmax: transaction ID that deleted/updated (0 = current version)

MVCC Everywhere

Contrast with File-Based Systems

File-based systems had primitive concurrency support, if any. The challenges that DBMS solves automatically were enormous manual burdens in file-based architectures.

File-Based Concurrency Problems

•Whole-file locking — One user locks entire file, blocking all others
•Application-managed locks — Each program implements own locking, inconsistently
•No transaction support — Partial updates on crash leave files corrupted
•No isolation — Reads see in-progress writes immediately
•Lock leakage — Program crash leaves locks held forever

DBMS Concurrency Solutions

•Row-level locking — Users only block if accessing same rows
•Centralized lock management — DBMS consistently handles all locking
•ACID transactions — All-or-nothing semantics with recovery
•Configurable isolation — Choose consistency vs. performance trade-off
•Automatic cleanup — Locks released on transaction end or connection loss

The 1980s Airline Reservation NightmareBefore proper DBMS concurrency control, airline reservation systems faced constant data corruption.

Input

Output

The DBMS Revolution

Practical Concurrency Considerations

Understanding concurrency control theory is important, but applying it correctly in practice requires awareness of common patterns and pitfalls.

Best Practices for Concurrent Database Access

•Keep transactions short — Long transactions hold locks longer, blocking others. Acquire locks, do work, commit. Don't hold transactions open during user interactions.
•Access resources in consistent order — If multiple resources are needed, always acquire them in the same order (e.g., by ID ascending) to prevent deadlocks.
•Use SELECT FOR UPDATE sparingly — Only lock rows you intend to modify. Unnecessary locking reduces concurrency.
•Handle deadlocks gracefully — Catch serialization errors and retry. Implement exponential backoff for retries.
•Choose appropriate isolation level — Stronger isn't always better. Read Committed is fine for most CRUD operations.
•Use optimistic locking for low-conflict scenarios — Check version/timestamp on update rather than locking upfront. Better for read-heavy workloads.

optimistic_locking.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- OPTIMISTIC LOCKING: No upfront locks, check before commit
 
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100),
    Price DECIMAL(10,2),
    Stock INT,
    Version INT DEFAULT 1  -- Version counter for optimistic locking
);
 
-- Read product (no lock)
SELECT ProductID, Name, Price, Stock, Version 
FROM Products 
WHERE ProductID = 100;
-- Returns: ProductID=100, Name='Widget', Price=29.99, Stock=50, Version=7
 
-- Application processes... (could be long, no lock held)
 
-- Update with version check
UPDATE Products 
SET Stock = 45, 
    Version = Version + 1
WHERE ProductID = 100 
  AND Version = 7;  -- The version we read earlier
 
-- Check if update succeeded
-- If 0 rows affected: someone else modified while we worked
-- Retry: re-read, re-process, re-update
 
 
-- TIMESTAMP-BASED OPTIMISTIC LOCKING
CREATE TABLE Documents (
    DocID INT PRIMARY KEY,
    Content TEXT,
    ModifiedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Read
SELECT DocID, Content, ModifiedAt FROM Documents WHERE DocID = 50;
-- Returns: ModifiedAt = '2024-01-15 10:30:00'
 
-- Update with timestamp check  
UPDATE Documents
SET Content = 'New content',
    ModifiedAt = CURRENT_TIMESTAMP
WHERE DocID = 50
  AND ModifiedAt = '2024-01-15 10:30:00';
 
-- If 0 rows: conflict detected. Refresh and retry.
 
 
-- APPLICATION PATTERN (pseudocode):
-- function updateProduct(id, newStock):
--     MAX_RETRIES = 3
--     for attempt in range(MAX_RETRIES):
--         product = SELECT ... WHERE id = :id
--         result = UPDATE ... WHERE id = :id AND version = product.version
--         if result.rowsAffected == 1:
--             return SUCCESS
--         if attempt < MAX_RETRIES - 1:
--             sleep(random(50, 100) * attempt)  # Backoff
--     return CONFLICT_ERROR

Optimistic vs Pessimistic

Summary: Concurrent Access

Key Takeaways

•Concurrency anomalies are dangerous — Lost updates, dirty reads, non-repeatable reads, and phantoms can corrupt data and produce incorrect results.
•Locking prevents conflicts — Shared and exclusive locks coordinate access. Transactions wait for conflicting locks.
•Deadlocks are handled automatically — DBMS detects circular waits and kills victim transactions. Applications must be prepared to retry.
•Isolation levels provide trade-offs — From Read Uncommitted (fastest, least safe) to Serializable (slowest, safest). Choose based on requirements.
•MVCC improves concurrency — Multiple row versions allow readers and writers to operate without blocking each other.
•File systems couldn't compete — Primitive file locking provided nothing close to DBMS concurrency control, driving database adoption.
•Optimistic locking for web apps — Low-conflict scenarios benefit from checking versions on update rather than locking upfront.

What's Next:

Page Complete