Transactions in DBMS

What is a Transaction?

A transaction is a logical unit of work that consists of one or more database operations (INSERT, UPDATE, DELETE, SELECT) that must be executed as a single, indivisible unit. Either all operations succeed (commit) or all fail (rollback).

Classic Example — Bank Transfer: Transferring $500 from Account A to Account B involves two operations:

Debit $500 from Account A
Credit $500 to Account B

Both operations must succeed together. If the debit succeeds but the credit fails, the money would disappear — which is unacceptable. A transaction ensures both happen or neither happens.

ACID Properties

ACID is an acronym for the four key properties that guarantee reliable transaction processing:

Property	Description	Example
Atomicity	All operations in a transaction succeed, or none do. "All or nothing."	Bank transfer: both debit and credit happen, or neither does.
Consistency	A transaction brings the database from one valid state to another. All integrity constraints must be satisfied.	Total money in the bank remains the same before and after a transfer.
Isolation	Concurrent transactions execute as if they were serial. Intermediate states are not visible to other transactions.	Two simultaneous transfers don't interfere with each other.
Durability	Once a transaction is committed, it remains committed even in case of system failure (power outage, crash).	After a committed transfer, the new balances persist even if the server crashes.

Transaction States

A transaction goes through the following states during its lifecycle:

Active: The transaction is currently executing. Initial state.
Partially Committed: The last operation has been executed but not yet committed. Changes are in memory.
Committed: All operations completed successfully. Changes are permanently saved to disk.
Failed: An error occurred during execution. The transaction cannot proceed.
Aborted: The transaction has been rolled back. Database is restored to its state before the transaction began.

State transitions: Active → Partially Committed → Committed (success path) | Active → Failed → Aborted (failure path)

Schedules and Serializability

When multiple transactions execute concurrently, their operations are interleaved in a schedule.

Serial Schedule: Transactions execute one after another, with no interleaving. Always consistent but poor performance.
Non-Serial Schedule: Operations from multiple transactions are interleaved. Better performance but may cause inconsistencies.
Serializable Schedule: A non-serial schedule that produces the same result as some serial schedule. This is the goal — concurrent execution with serial correctness.

Concurrency Problems

Problem	Description	Example
Lost Update	Two transactions read the same value, both update it, and one update overwrites the other.	T1 and T2 both read balance=1000. T1 adds 100 (1100), T2 adds 200 (1200). T2 commits last, T1's update is lost.
Dirty Read	A transaction reads data written by an uncommitted transaction.	T1 updates salary to 5000 (not committed). T2 reads 5000. T1 rolls back. T2 used invalid data.
Unrepeatable Read	A transaction reads the same row twice and gets different values because another transaction modified it in between.	T1 reads salary=4000. T2 updates salary to 5000 and commits. T1 reads salary again and gets 5000.
Phantom Read	A transaction re-executes a query and finds new rows that weren't there before (inserted by another transaction).	T1 counts employees in dept=1 (gets 5). T2 inserts a new employee in dept=1. T1 counts again and gets 6.

Previous Next