Transactions

Database Systems

Professor Dr. Rafiqul Islam

10 July, 2025

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 1 / 46

What is a Transaction?

A transaction is a sequence of operations performed as a single

logical unit of work.
It must be either fully completed or fully aborted.
Used to maintain data consistency in concurrent and failure-prone
environments.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 2 / 46

Example Transaction T1

Transfer $50 from account A to B:

T1 : BEGIN
READ(A ) ;
A := A − 5 0 ;
WRITE(A ) ;
READ(B ) ;
B := B + 5 0 ;
WRITE(B ) ;
COMMIT;
Ensures: Atomic debit-credit, consistency across accounts.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 3 / 46

ACID Properties

A transaction must satisfy the ACID properties:

Atomicity – All operations complete or none do.
Consistency – Preserves the correctness of the database.
Isolation – Intermediate results are hidden from other transactions.
Durability – Committed results survive system failures.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 4 / 46

Storage Structures: Transaction Reliability

Ensuring Atomicity and Durability requires understanding storage

media.
Storage types differ in:
Speed
Capacity
Resilience to failure
Classified as:
Volatile storage
Nonvolatile storage
Stable storage

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 5 / 46

Volatile Storage

Definition: Loses information on system crashes.

Examples:
Main memory (RAM)
Cache memory
Features:
Fast access
Direct access to data items
Not reliable for persistent data

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 6 / 46

Nonvolatile Storage

Definition: Survives crashes but is vulnerable to hardware failure.

Examples:
Magnetic disks
Flash storage
Optical media and tapes (tertiary)
Slower than volatile — especially random access.
Used for both online and archival storage.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 7 / 46

Stable Storage

Ideal: Information is never lost (theoretically).

Real-world approximation:
Replicate data across independent nonvolatile media.
Careful update procedures to prevent loss.
Extremely low chance of failure.
Essential for recovery systems.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 8 / 46

Transaction Atomicity and Durability

Transactions may abort — unsuccessful execution.

Atomicity: No partial changes from aborted transactions.
Durability: Committed changes persist despite system failures.
Recovery mechanisms ensure these properties.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 9 / 46

Example: Transaction Atomicity and Durability

BEGIN TRANSACTION;

-- Step 1: Debit 500 from Account A

UPDATE accounts
SET balance = balance - 500
WHERE account_id = ’A’;

-- Step 2: Credit 500 to Account B

UPDATE accounts
SET balance = balance + 500
WHERE account_id = ’B’;

COMMIT;

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 10 / 46

Rollback and Logging

Aborted transaction’s changes must be undone — rollback.

Database systems use a log to track modifications.
Each log entry includes:
Transaction ID
Data item ID
Old and new values
Enables undo and redo for recovery.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 11 / 46

Example: Transaction Atomicity and Durability
BEGIN TRANSACTION;
-- Step 1: Debit 500 from Account A
UPDATE accounts SET balance = balance - 500
WHERE account_id = ’A’;
-- Step 2: Credit 500 to Account B
UPDATE accounts SET balance = balance + 500
WHERE account_id = ’B’;
-- Optional integrity check before commit
IF @@ERROR != 0
BEGIN
ROLLBACK;
END
ELSE
BEGIN
COMMIT;
END
Professor Dr. Rafiqul Islam Transactions 10 July, 2025 12 / 46
Commit and Durability

A committed transaction updates the database permanently.

Database reaches a new consistent state.
If failure occurs after commit, changes must persist.
No rollback allowed for committed transactions.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 13 / 46

Handling Mistaken Commits

Only user-defined compensation can undo committed effects.

Example: If 20 added, a new transaction subtracts 20.
Compensation not guaranteed — handled manually by the user.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 14 / 46

Transaction States

Active: Executing.
Partially Committed: Final statement executed.
Failed: Error detected, cannot proceed.
Aborted: Rolled back, no database effect.
Committed: Completed successfully, updates persist.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 15 / 46

Transaction States

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 16 / 46

Transaction Isolation Overview

Modern systems allow transactions to run concurrently.

Concurrency complicates maintaining database consistency.
Easier alternative: run transactions serially (one at a time).
But concurrency offers major benefits.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 17 / 46

Benefits of Concurrent Execution

1 Improved throughput and resource utilization

CPU and disk work in parallel
Multiple transactions use different system components simultaneously
Less idle time for CPU and disks
2 Reduced waiting time
Short transactions don’t wait for long ones
Better average response time
Reduced unpredictability

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 18 / 46

Concurrency and Isolation Risks

Concurrent execution can violate isolation.

Database consistency may break even if each transaction is correct.
Requires control mechanisms:
Schedules: Describe execution order of operations.
Concurrency-control schemes: Ensure correctness.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 19 / 46

Concurrent Transactions Example

T1: Transfer $50 from A to B

T2: Transfer $100 from A to C
Schedule S1 (Interleaved):
READ(A) by T1
READ(A) by T2
A := A - 50 by T1
A := A - 100 by T2
WRITE(A) by T1
WRITE(A) by T2
Issue: Final value of A reflects only T2’s deduction — Lost update.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 20 / 46

Serial vs Concurrent Execution

Serial Schedule: T1 completes first, then T2 begins.

Concurrent Schedule: Operations of T1 and T2 interleave.
Serial execution guarantees isolation and correctness.
Concurrent execution must be serializable to be correct.
Serializability: Result must be equivalent to some serial schedule.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 21 / 46

Schedule Conflict Example

Schedule S:
T1: READ(A), WRITE(A)
T2: READ(A), WRITE(A)
Conflict:
WRITE-WRITE conflict on A
Result depends on order of writes
Solution:
Use locking, timestamp ordering, or serializability checks
Enforce isolated execution without harming concurrency

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 22 / 46

Serializability

Ensures concurrent execution of transactions preserves consistency.

A schedule is serializable if its result is equivalent to some serial
schedule.
Transactions are simplified to read and write operations on data
items.
Focus: Conflict serializability.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 23 / 46

Example Schedule: Serializability

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 24 / 46

Example Schedule: Serializability

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 25 / 46

Swapping Instructions

If two instructions from different transactions access different data

items → safe to swap.
If they access the same data item, swap may affect results.
Analyze based on operation types: read(Q) or write(Q).

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 26 / 46

Example Schedule: Serializability

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 27 / 46

Example Schedule: Serializability

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 28 / 46

Read/Write Conflict Cases

1 read(Q), read(Q) → No conflict.

2 read(Q), write(Q) → Order matters.
3 write(Q), read(Q) → Order matters.
4 write(Q), write(Q) → Final value depends on order.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 29 / 46

What Is a Conflict?

Operations by different transactions on the same data item.

At least one operation is a write.
Such operations cannot be swapped freely.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 30 / 46

Schedule Illustration

Schedule 3: Interleaved Execution

T1 and T2 both read and write A and B.
Conflicts exist: e.g., T1’s write(A) vs T2’s read(A).
Schedule 6: Equivalent Serial Schedule
All T1 operations precede T2.
Equivalent result → conflict-serializable.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 31 / 46

Example Transaction T1

Transfer $50 from account A to B:

T1 : BEGIN
READ(A ) ;
A := A − 5 0 ;
WRITE(A ) ;
READ(B ) ;
B := B + 5 0 ;
WRITE(B ) ;
COMMIT;
Ensures: Atomic debit-credit, consistency across accounts.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 32 / 46

Example Transaction T2

Apply interest on account A and store log:

T2 : BEGIN
READ(A ) ;
A := A ∗ 1 . 0 5 ;
WRITE(A ) ;
INSERT INTO Log ( ’ I n t e r e s t a p p l i e d ’ ) ;
COMMIT;

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 33 / 46

Concurrent Transactions Example

T1: Transfer $50 from A to B

T2: Transfer $100 from A to C
Schedule S1 (Interleaved):
1 READ(A) by T1
2 READ(A) by T2
3 A := A - 50 by T1
4 A := A - 100 by T2
5 WRITE(A) by T1
6 WRITE(A) by T2
Issue: Final A value reflects only T2’s deduction (lost update).

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 34 / 46

Serial vs Concurrent Execution

Serial schedule: T1 completes, then T2 starts.

Concurrent schedule: Operations of T1 and T2 interleave.
Correctness requires serializability – result equivalent to some serial
execution.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 35 / 46

Schedule Example (Conflict)

Schedule S:
T1: Read(A), Write(A)
T2: Read(A), Write(A)
Conflict: Write-Write conflict on A
Solution: Use locking or timestamp ordering to serialize.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 36 / 46

Why Isolation Levels Matter

Ensure consistency in concurrent transactions.

Serializable execution: behaves like one-at-a-time processing.
Trade-off: higher isolation reduces concurrency.
SQL allows choosing weaker isolation levels to boost performance.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 37 / 46

Isolation Levels at a Glance

Feature Read Uncommitted Read Committed Repeatable R

Dirty Read Yes No No
Non-repeatable Read Yes Yes No
Phantom Read Yes Yes Yes
Concurrency High Medium Low
Consistency Weak Medium Strong

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 38 / 46

Read Uncommitted

Reads uncommitted changes from other transactions.

Risks: dirty reads, inaccurate results.
Fastest but least safe.
Useful for long-running transactions that tolerate inconsistency.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 39 / 46

Read Committed

Only reads committed data.

Prevents dirty reads.
Allows non-repeatable reads.
Most common default in many DB systems.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 40 / 46

Repeatable Read

Ensures same value returned when reading the same item twice.
Prevents dirty and non-repeatable reads.
Phantom reads still possible.
May not be serializable with respect to all transactions.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 41 / 46

Serializable

Strictest level: behaves like transactions run one-by-one.

Eliminates dirty, non-repeatable, and phantom reads.
Safest for data integrity.
May reduce system concurrency and performance.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 42 / 46

What Are Transactions in SQL?

A transaction is a sequence of operations performed as a single logical

unit of work.
Transactions ensure ACID properties: Atomicity, Consistency,
Isolation, and Durability.
SQL syntax to control transactions:
BEGIN TRANSACTION
COMMIT
ROLLBACK

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 43 / 46

Phantom Reads with Insert

Query:

SELECT ID, name FROM instructor WHERE salary > 90000;

Concurrent Insert:

INSERT INTO instructor VALUES (’11111’, ’James’, ’Marketing’,

If the insert happens before the query: ”James” appears.

If it happens after: ”James” does not appear.
This is called a phantom read—new data appears unexpectedly.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 44 / 46

Conflicts Due to Update
Query:
SELECT ID, name FROM instructor WHERE salary > 90000;

Concurrent Update:
UPDATE instructor
SET salary = salary * 0.9
WHERE name = ’Wu’;

Wu has a salary of $90,000, gets reduced to $81,000.

If an index on salary allows the query to skip Wu, the conflict goes
unnoticed.
But logically, the update can affect query results.
This leads to the idea of predicate locking.
Professor Dr. Rafiqul Islam Transactions 10 July, 2025 45 / 46
What is Predicate Locking?

Predicate locking locks all rows that could satisfy a predicate—even if

they don’t yet exist.
Ensures no phantom tuples appear or disappear during transaction
execution.
Example conflict:
Lock predicate: salary > 90000
Any update/insertion affecting this predicate is blocked.
However, predicate locking is complex and expensive.
Most systems use alternative approaches like index locking or
MVCC.

Professor Dr. Rafiqul Islam Transactions 10 July, 2025 46 / 46