Transaction states in DBMS Theory - Full Explanation

Introduction

Imagine you are making a bank transfer. The system must carefully track each step to ensure the money moves correctly or not at all. This tracking happens through different transaction states that show the progress and safety of the operation.

Explanation

Active

This is the first state when a transaction starts. The system is processing commands like reading or writing data. The transaction is not finished and can still be changed or stopped.

Active means the transaction is currently running and not yet complete.

Partially Committed

After the last operation in the transaction is executed, but before the changes are saved permanently, the transaction enters this state. It shows that the transaction is almost done but not yet final.

Partially Committed means the transaction finished its tasks but is not yet permanent.

Committed

In this state, all changes made by the transaction are saved permanently in the database. The transaction is successfully completed and cannot be undone.

Committed means the transaction’s changes are permanently saved.

Failed

If an error or problem occurs during the transaction, it moves to this state. The transaction cannot continue and must be stopped.

Failed means the transaction encountered a problem and cannot finish.

Aborted

When a transaction fails or is canceled, it enters the aborted state. All changes made during the transaction are undone to keep the database consistent.

Aborted means the transaction was stopped and all its changes were reversed.

Real World Analogy

Think of ordering a meal at a restaurant. You place your order (active), the kitchen starts cooking (partially committed), the meal is served and you pay (committed). If the kitchen runs out of ingredients, the order fails, and you cancel it (failed and aborted).

Active → Placing your order and the kitchen starting to prepare it

Partially Committed → The meal is almost ready but not yet served

Committed → Meal served and payment completed

Failed → Kitchen runs out of ingredients and cannot finish the meal

Aborted → Order canceled and no meal served or charged

Diagram

Diagram

┌─────────┐     ┌───────────────────┐     ┌───────────┐
│ Active  │ → → │ Partially Committed│ → → │ Committed │
└─────────┘     └───────────────────┘     └───────────┘
      ↓                 ↑                      
      │                 │                      
      ↓                 │                      
┌─────────┐             │                      
│ Failed  │ ← ← ← ← ← ← ┘                      
└─────────┘                                    
      ↓                                         
┌─────────┐                                    
│ Aborted │                                    
└─────────┘                                    

This diagram shows the flow of transaction states from Active to Committed or to Failed and Aborted.

Key Facts

Active → The transaction is currently executing operations.

Partially Committed → The transaction has finished operations but changes are not yet permanent.

Committed → The transaction’s changes are permanently saved in the database.

Failed → The transaction encountered an error and cannot continue.

Aborted → The transaction was stopped and all changes were undone.

Code Example

DBMS Theory

import sqlite3

conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER)')
cur.execute('INSERT INTO accounts (balance) VALUES (100)')

try:
    conn.execute('BEGIN')  # Active state
    cur.execute('UPDATE accounts SET balance = balance - 50 WHERE id = 1')
    # Simulate error
    raise Exception('Simulated failure')
    conn.commit()  # Committed state
except Exception as e:
    conn.rollback()  # Aborted state
    print('Transaction aborted:', e)

cur.execute('SELECT balance FROM accounts WHERE id = 1')
print('Balance after transaction:', cur.fetchone()[0])

Common Confusions

Thinking that a transaction is committed as soon as it finishes its last operation.

Thinking that a transaction is committed as soon as it finishes its last operation. A transaction is only committed after changes are permanently saved; finishing operations puts it in the partially committed state first.

Believing that failed transactions keep their changes in the database.

Believing that failed transactions keep their changes in the database. Failed transactions must be aborted, which means all their changes are undone to keep data consistent.

Summary

Transactions move through states: Active, Partially Committed, Committed, Failed, and Aborted to manage data changes safely.

Only when a transaction reaches the Committed state are its changes saved permanently.

If a transaction fails, it must be Aborted to undo any partial changes and keep the database consistent.

Practice

1. Which of the following is NOT a valid state of a database transaction?

easy

A. Running

B. Waiting

C. Committed

D. Failed

Solution

1. Step 1: List common transaction states

   Common transaction states include Running (active), Waiting (blocked), Committed (successfully completed), and Aborted (rolled back).

2. Step 2: Identify invalid state

   "Failed" is not a standard transaction state; instead, "Aborted" or "Rolled back" is used for unsuccessful transactions.

3. Final Answer:

   Failed -> Option D

4. Quick Check:

   Standard states exclude "Failed" [OK]

Hint: Remember standard states: Running, Waiting, Committed, Aborted [OK]

Common Mistakes:

• Confusing 'Failed' with 'Aborted'
• Thinking 'Waiting' is not a state
• Assuming 'Failed' is official terminology

2. Which SQL command is used to move a transaction from the 'Running' state to the 'Committed' state?

easy

A. ROLLBACK

B. COMMIT

C. SAVEPOINT

D. BEGIN

Solution

1. Step 1: Understand transaction commands

   ROLLBACK undoes changes, SAVEPOINT sets a restore point, BEGIN starts a transaction, and COMMIT finalizes changes.

2. Step 2: Identify command to commit

   COMMIT is the command that moves a transaction from Running (active) to Committed (completed) state.

3. Final Answer:

   COMMIT -> Option B

4. Quick Check:

   COMMIT finalizes transaction [OK]

Hint: COMMIT saves changes permanently, ROLLBACK cancels [OK]

Common Mistakes:

• Confusing ROLLBACK with COMMIT
• Using SAVEPOINT to commit
• Thinking BEGIN commits

3. Consider the following sequence of SQL commands in a transaction:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

What is the final state of this transaction after execution?

medium

A. Committed

B. Aborted

C. Running

D. Waiting

Solution

1. Step 1: Analyze the transaction commands

   The transaction starts with BEGIN, performs two updates, then ends with COMMIT.

2. Step 2: Determine transaction state after COMMIT

   COMMIT finalizes the transaction successfully, so the state becomes Committed.

3. Final Answer:

   Committed -> Option A

4. Quick Check:

   COMMIT means transaction is Committed [OK]

Hint: COMMIT means transaction completed successfully [OK]

Common Mistakes:

• Thinking transaction remains Running after COMMIT
• Confusing Aborted with Committed
• Assuming Waiting state after COMMIT

4. Identify the error in this transaction sequence:

BEGIN;
UPDATE inventory SET quantity = quantity - 5 WHERE product_id = 10;
ROLLBACK;
COMMIT;

medium

A. COMMIT after ROLLBACK is invalid

B. ROLLBACK should come after COMMIT

C. BEGIN is missing

D. No error, sequence is correct

Solution

1. Step 1: Understand ROLLBACK and COMMIT order

   ROLLBACK cancels the transaction, so no changes remain to commit afterwards.

2. Step 2: Identify invalid command sequence

   After ROLLBACK, issuing COMMIT is invalid because the transaction is already aborted.

3. Final Answer:

   COMMIT after ROLLBACK is invalid -> Option A

4. Quick Check:

   Cannot COMMIT after ROLLBACK [OK]

Hint: ROLLBACK cancels; no COMMIT allowed after rollback [OK]

Common Mistakes:

• Thinking COMMIT can follow ROLLBACK
• Assuming ROLLBACK should come after COMMIT
• Missing BEGIN statement

5. A transaction is in the 'Waiting' state because it is blocked by another transaction holding a lock. Which of the following actions will NOT help the waiting transaction to proceed?

hard

A. The blocking transaction commits

B. The blocking transaction rolls back

C. The waiting transaction issues a COMMIT

D. The blocking transaction releases the lock

Solution

1. Step 1: Understand 'Waiting' state cause

   Waiting occurs when a transaction is blocked due to locks held by another transaction.

2. Step 2: Analyze actions that unblock waiting

   If the blocking transaction commits, rolls back, or releases the lock, the waiting transaction can proceed.

3. Step 3: Identify action that does NOT help

   The waiting transaction issuing COMMIT while blocked cannot proceed because it must first acquire the lock.

4. Final Answer:

   The waiting transaction issues a COMMIT -> Option C

5. Quick Check:

   Waiting means blocked; only blocking transaction actions help [OK]

Hint: Only blocking transaction actions unblock waiting transaction [OK]

Common Mistakes:

• Thinking waiting transaction can commit while blocked
• Assuming waiting state ends without lock release
• Confusing roles of blocking and waiting transactions