Schedules in DBMS: Types and Concurrency Anomalies

4
0

Introduction

A database often handles several transactions at once. One application might update an account balance while another reads it or prepares a report. Allowing these transactions to overlap improves resource use, but their operations must be coordinated.

A schedule describes the order of these operations. Understanding schedules helps explain why some concurrent executions produce correct results while others cause missing updates, changing query results, or reads of unfinished work.

What Is a Schedule in DBMS?

A schedule is an ordered sequence of operations from one or more transactions. It preserves the order of operations within each individual transaction, even when operations from different transactions are interleaved.

A transaction is a logical unit of work that ends with either COMMIT, which makes its changes permanent, or ROLLBACK, which cancels its uncommitted changes.

Schedule notation commonly uses:

  • R1(A): Transaction T1 reads data item A.

  • W1(A): Transaction T1 writes data item A.

  • C1: Transaction T1 commits.

  • A1: Transaction T1 aborts.

Here, the number identifies the transaction. The letter inside parentheses identifies the data item.

Concurrency does not require operations to run at exactly the same instant. Transactions are concurrent when their execution periods overlap, even if the database alternates between their operations.

Example Table

The examples use an Account table. Each row stores an account identifier, its holder’s name, and its current balance.

Account: initial data

AccountID

HolderName

Balance

1

Asha

1000.00

2

Ravi

500.00

3

Meera

700.00

AccountID is the primary key. In the schedule examples, A refers to Asha’s balance and B refers to Ravi’s balance.

Each conceptual example starts with these initial balances. T1 and T2 represent transactions running through separate database connections.

Serial and Non-Serial Schedules

A serial schedule completes one transaction before starting another. A non-serial schedule interleaves operations from multiple transactions.

Consider T1 adding 100 to Asha’s account and T2 adding 50 to Ravi’s account.

Step

Serial schedule

Non-serial schedule

1

T1 reads Asha’s balance

T1 reads Asha’s balance

2

T1 writes 1100.00

T2 reads Ravi’s balance

3

T1 commits

T1 writes 1100.00

4

T2 reads Ravi’s balance

T2 writes 550.00

5

T2 writes 550.00

T1 commits

6

T2 commits

T2 commits

Result in both cases:

AccountID

HolderName

Balance

1

Asha

1100.00

2

Ravi

550.00

3

Meera

700.00

The interleaving is safe here because the transactions update different accounts and have no shared decision or rule linking those updates.

A serializable schedule has the same effect as some serial execution of the transactions. It may still interleave their operations. Therefore, non-serial does not automatically mean incorrect.

Operations can conflict when they belong to different transactions, access the same data item, and at least one is a write. Two reads alone do not conflict.

Serial and None-serial schedules

Serial and None-serial schedules

Common Concurrency Anomalies

A concurrency anomaly is an unexpected interaction between overlapping transactions. Whether an anomaly can occur depends on the queries, isolation level, and database implementation.

1. Lost Update

A lost update occurs when one transaction overwrites another transaction’s change using an outdated value.

Objective: Add 100 and 200 to Asha’s balance through two transactions. The correct final balance is 1000 + 100 + 200 = 1300.

Step

T1: Add 100

T2: Add 200

1

Reads 1000.00

2

Reads 1000.00

3

Calculates 1100.00

4

Calculates 1200.00

5

Writes 1100.00 and commits

6

Writes its previously calculated 1200.00 and commits

Outcome:

Account

Expected balance

Balance after the lost update

Asha

1300.00

1200.00

T2 overwrites T1’s increase. The problem comes from writing a value calculated from an earlier read, not merely from two transactions updating the same row.

For simple increments, an update that calculates directly from the stored balance can avoid this stale-value pattern. More complex read-and-update decisions may require locking or conflict detection.

2. Dirty Read

A dirty read occurs when a transaction reads changes made by another transaction before those changes commit.

Objective: Read Asha’s balance while another transaction has an unfinished deduction.

Step

T1: Deduct 200

T2: Read balance

1

Updates Asha’s balance to 800.00 without committing

2

Reads the uncommitted 800.00

3

Rolls back, restoring 1000.00

Outcome:

Value read by T2

Committed balance after T1 rolls back

800.00

1000.00

T2 used a value that never became committed data. Reading another transaction’s uncommitted value is a dirty read even if that transaction eventually commits.

Lost update and Dirty read

Lost update and Dirty read

3. Non-Repeatable Read

A non-repeatable read occurs when a transaction reads the same row twice and sees a different value because another transaction committed a change between the reads.

Objective: Read Asha’s balance twice within one transaction.

Step

T1: Read balance twice

T2: Add 200

1

Reads 1000.00

2

Updates the balance to 1200.00 and commits

3

Reads 1200.00

Outcome:

First read by T1

Second read by T1

1000.00

1200.00

Both values were committed when read. This distinguishes a non-repeatable read from a dirty read.

4. Phantom Read

A phantom read occurs when repeating a query within a transaction returns a different set of matching rows because another transaction committed changes.

Objective: Find accounts whose balance is at least 700.

Step

T1: Find qualifying accounts

T2: Add an account

1

Finds Asha and Meera

2

Inserts Kabir with a balance of 900.00 and commits

3

Repeats the query and finds Asha, Meera, and Kabir

First result:

AccountID

HolderName

Balance

1

Asha

1000.00

3

Meera

700.00

Second result:

AccountID

HolderName

Balance

1

Asha

1000.00

3

Meera

700.00

4

Kabir

900.00

The new matching row is the phantom. Deletes and updates that change which rows satisfy the condition can also change the matching set.

A non-repeatable read focuses on a previously read row. A phantom read focuses on the set of rows satisfying a condition.

Changed value or New row ?

Changed value or New row ?

Other Important Concurrency Problems

Some problems involve multiple rows or unfinished writes rather than a repeated read of one account.

Problem

Meaning

Example using the account data

Dirty write

A transaction overwrites another transaction’s uncommitted write.

T2 overwrites Asha’s balance before T1 commits or rolls back.

Incorrect summary or read skew

A calculation combines values from different database states.

T1 reads Asha’s 1000.00, T2 transfers 200 from Asha to Ravi and commits, then T1 reads Ravi’s 700.00. T1 calculates 1700.00 although their combined balance remains 1500.00.

Write skew

Transactions make decisions from a shared view of data, then update different rows in a way that violates a shared rule.

Suppose Asha and Ravi must retain a combined balance of at least 500. Each transaction checks the initial total of 1500. One withdraws 800 from Asha, while another withdraws 400 from Ravi. Each withdrawal appears safe alone, but together they leave only 300.

MySQL and PostgreSQL coordinate conflicting writes, so the dirty-write sequence is not an ordinary permitted execution.

The incorrect-summary example uses separate reads. It does not imply that a single ordinary SUM query at READ COMMITTED in these systems mixes committed states during that statement.

Schedules and Safe Recovery

A schedule must also handle transactions that fail. If one transaction depends on another’s unfinished changes, a rollback can affect more than one transaction.

Schedule property

Basic rule

Recoverable

If T2 reads a value written by T1, T2 must not commit before T1 commits.

Cascadeless

Transactions read values written by other transactions only after those writers commit.

Strict

Other transactions cannot read or overwrite an item written by a transaction until that transaction commits or aborts.

If T2 reads T1’s uncommitted changes and T1 aborts, T2 may also need to abort. This is a cascading rollback.

Every strict schedule is cascadeless, and every cascadeless schedule is recoverable. These properties concern safe recovery. Serializability concerns equivalence to a serial execution.

How Concurrency Anomalies Are Controlled

Databases use isolation rules, locks, and data versions to coordinate concurrent work.

  • Isolation levels: Define which interactions transactions may observe. READ COMMITTED prevents dirty reads but can allow non-repeatable reads and phantoms.

  • Row locks: A locking read such as SELECT ... FOR UPDATE can protect existing rows during a read-and-update decision. It must run inside a transaction.

  • Atomic updates: Expressions such as Balance = Balance + 100 calculate from the row being updated instead of replacing it with a stale application value.

  • Version checks: An update can verify that a row has not changed since it was read. A mismatch tells the application to reconsider its work.

  • Serializable execution: Provides the effect of a serial order for successfully committed transactions. Applications may need to retry transactions rejected because of conflicts.

REPEATABLE READ behavior is database-specific. PostgreSQL prevents phantom reads at this level.

A Compact Example

Objective: Observe a non-repeatable read and a phantom read within the same transaction at READ COMMITTED.

The example uses two database connections:

  • Session A: Reads the data, pauses, and then reads again.

  • Session B: Updates Asha’s balance and inserts Kabir’s account between Session A’s reads.

Run the numbered steps in order. Session A must remain open while Session B performs its work.

1. Create and populate the table

Consider the following queries to create the three initial accounts.

CREATE TABLE Account (
AccountID INT PRIMARY KEY,
HolderName VARCHAR(50) NOT NULL,
Balance DECIMAL(10, 2) NOT NULL
);
START TRANSACTION;
INSERT INTO Account (AccountID, HolderName, Balance)
VALUES
(1, 'Asha', 1000.00),
(2, 'Ravi', 500.00),
(3, 'Meera', 700.00);
COMMIT;

The table now contains:

AccountID

HolderName

Balance

1

Asha

1000.00

2

Ravi

500.00

3

Meera

700.00

2. Session A: Start a transaction and read

Consider the following queries to read Asha’s balance and list accounts with a balance of at least 700.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT Balance
FROM Account
WHERE AccountID = 1;
SELECT AccountID, HolderName
FROM Account
WHERE Balance >= 700
ORDER BY AccountID;
-- Pause here. Keep this transaction open.

First query result:

Balance

1000.00

Second query result:

AccountID

HolderName

1

Asha

3

Meera

The >= operator means “greater than or equal to.” ORDER BY AccountID displays the matching accounts in identifier order.

3. Session B: Change and commit the data

Consider the following queries to add 200 to Asha’s balance and create Kabir’s account.

START TRANSACTION;
UPDATE Account
SET Balance = Balance + 200
WHERE AccountID = 1;
INSERT INTO Account (AccountID, HolderName, Balance)
VALUES (4, 'Kabir', 900.00);
COMMIT;

Committed table contents:

AccountID

HolderName

Balance

1

Asha

1200.00

2

Ravi

500.00

3

Meera

700.00

4

Kabir

900.00

4. Session A: Repeat the reads

Consider the following queries to repeat the original reads before ending Session A’s transaction.

SELECT Balance
FROM Account
WHERE AccountID = 1;
SELECT AccountID, HolderName
FROM Account
WHERE Balance >= 700
ORDER BY AccountID;
COMMIT;

First query result:

Balance

1200.00

Second query result:

AccountID

HolderName

1

Asha

3

Meera

4

Kabir

Asha’s balance changed from 1000.00 to 1200.00 between reads, demonstrating a non-repeatable read. Kabir appeared in the repeated search, demonstrating a phantom read.

Neither observation is a dirty read because Session B committed before Session A repeated its queries. At READ COMMITTED, each ordinary query can see changes committed before that query begins.

Summary

A schedule records how transaction operations are ordered. Serial schedules execute transactions one at a time, while non-serial schedules interleave their operations. An interleaved schedule can still be correct when its effect is equivalent to a serial execution.

Concurrency anomalies include lost updates, dirty reads, non-repeatable reads, and phantom reads. Recovery rules, suitable isolation levels, atomic updates, and carefully chosen locks help control these interactions without requiring every transaction to run alone.

CS Core

Read Similar Blogs

Comments0