Database Transactions, ACID Properties, and Deadlocks

Posted on Jun 27, 2026 in Computers

Transaction and ACID Properties (8 Marks)

A transaction is a sequence of database operations that performs a single logical unit of work. A transaction may consist of one or more SQL statements such as INSERT, UPDATE, DELETE, and SELECT.

A transaction must be completed entirely; otherwise, all changes made by it are cancelled.

Example: Bank Fund Transfer

  • Debit ₹1000 from Account A.
  • Credit ₹1000 to Account B.

Both operations together form a transaction. If one operation fails, the entire transaction is rolled back.

Transaction States

  • Active: The transaction is being executed.
  • Partially Committed: The last statement has executed.
  • Committed: The transaction completed successfully.
  • Failed: The transaction cannot proceed.

ACID Properties of Transactions

1. Atomicity

Atomicity means “All or Nothing.” A transaction is treated as a single unit. Either all operations are performed successfully or none are performed. If any operation fails, the transaction is rolled back.

2. Consistency

Consistency ensures that the database remains in a valid state before and after the transaction. All integrity constraints must be satisfied. A transaction cannot violate database rules.

3. Isolation

Isolation ensures that concurrent transactions do not interfere with each other. Each transaction executes independently. Intermediate results of one transaction are not visible to other transactions.

4. Durability

Durability ensures that once a transaction is committed, its changes become permanent. Data remains stored even after power failure or system crash. Committed transactions are never lost.

Understanding Deadlocks in DBMS

A deadlock is a situation in which two or more transactions wait indefinitely for each other to release resources, and none of them can proceed further.

Example:

  • Transaction T1 locks Resource A and requests Resource B.
  • Transaction T2 locks Resource B and requests Resource A.

Now T1 waits for T2 and T2 waits for T1. Neither can continue, resulting in a deadlock.

Deadlock Detection

Deadlock detection is the process of identifying whether a deadlock has occurred in the system.

Wait-For Graph Method

  • Each transaction is represented by a node.
  • An edge from Ti → Tj indicates that Ti is waiting for a resource held by Tj.
  • If the graph contains a cycle, a deadlock exists.

Steps for Detection:

  1. Construct the Wait-For Graph.
  2. Check for cycles in the graph.
  3. If a cycle is found, a deadlock is detected.
  4. Resolve the deadlock by aborting one of the transactions and releasing its resources.

Methods of Deadlock Prevention

1. Resource Ordering

Assign a fixed order to all resources. Transactions must request resources only in that specific order. This prevents circular waiting.

2. Wait-Die Scheme

In this scheme, an older transaction waits for a younger transaction. A younger transaction requesting a resource held by an older transaction is aborted and restarted.

3. Wound-Wait Scheme

In this scheme, an older transaction requesting a resource held by a younger transaction forces the younger transaction to abort. A younger transaction waits if the resource is held by an older transaction.

4. Preemption

Resources may be taken away from a transaction and allocated to another transaction to resolve potential conflicts.