Transaction states in DBMS Theory - Mini Project: Build & Apply

Understanding Transaction States in DBMS

📖 Scenario: You are learning how database transactions work. A transaction is a sequence of operations performed as a single logical unit of work. It can be in different states such as Active, Partially Committed, Committed, Failed, and Aborted.Imagine you are managing a bank system where money transfers must be done carefully to avoid errors.

🎯 Goal: Build a simple representation of transaction states using a dictionary to understand how a transaction moves through different states.

📋 What You'll Learn

Create a dictionary named transaction_states with keys as state names and values as their descriptions.

Add a variable named current_state to hold the starting state of the transaction.

Use a loop to simulate moving through the states in the correct order.

Add a final step to mark the transaction as Committed or Aborted based on a condition.

💡 Why This Matters

🌍 Real World

Understanding transaction states helps ensure data integrity and consistency in systems like banking, e-commerce, and reservation systems.

💼 Career

Database administrators and developers must understand transaction states to design reliable and fault-tolerant applications.

1

Create the transaction states dictionary

Create a dictionary called transaction_states with these exact entries: 'Active' mapped to 'Transaction is executing', 'Partially Committed' mapped to 'Transaction has executed but not yet committed', 'Committed' mapped to 'Transaction has been committed', 'Failed' mapped to 'Transaction execution failed', and 'Aborted' mapped to 'Transaction has been rolled back'.

DBMS Theory

# Create the transaction_states dictionary with the specified entries
# Your code here

Hint

Use curly braces {} to create a dictionary with keys and values as strings.

2

Set the initial transaction state

Create a variable called current_state and set it to the string 'Active' to represent the starting state of the transaction.

DBMS Theory

transaction_states = {
    'Active': 'Transaction is executing',
    'Partially Committed': 'Transaction has executed but not yet committed',
    'Committed': 'Transaction has been committed',
    'Failed': 'Transaction execution failed',
    'Aborted': 'Transaction has been rolled back'
}
# Set current_state to 'Active'
# Your code here

Hint

Assign the string 'Active' to the variable current_state.

3

Simulate transaction state changes

Use a for loop with the variable state to iterate over the list ['Active', 'Partially Committed', 'Committed'] and update current_state to each state in the loop.

DBMS Theory

transaction_states = {
    'Active': 'Transaction is executing',
    'Partially Committed': 'Transaction has executed but not yet committed',
    'Committed': 'Transaction has been committed',
    'Failed': 'Transaction execution failed',
    'Aborted': 'Transaction has been rolled back'
}

current_state = 'Active'

# Use a for loop to update current_state through the states
# Your code here

Hint

Use a for loop to assign each state to current_state.

4

Complete transaction with commit or abort

Add a variable called transaction_successful and set it to True. Then use an if statement to set current_state to 'Committed' if transaction_successful is True, otherwise set current_state to 'Aborted'.

DBMS Theory

transaction_states = {
    'Active': 'Transaction is executing',
    'Partially Committed': 'Transaction has executed but not yet committed',
    'Committed': 'Transaction has been committed',
    'Failed': 'Transaction execution failed',
    'Aborted': 'Transaction has been rolled back'
}

current_state = 'Active'

for state in ['Active', 'Partially Committed', 'Committed']:
    current_state = state

# Add transaction_successful variable and update current_state accordingly
# Your code here

Hint

Use a boolean variable and an if-else statement to decide the final state.

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