Transaction states in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding the Active state in transactions

Which of the following best describes the Active state of a transaction in a database management system?

A The transaction has completed all its operations and is waiting to be committed or rolled back.

B The transaction has been aborted and all its changes have been undone.

C The transaction has been committed and its changes are permanent.

D The transaction is currently executing and has not yet completed all its operations.

💡 Hint

Think about what the transaction is doing before it finishes.

The Active state means the transaction is currently running and performing operations. It has not yet finished or decided to commit or abort.

📋 Factual

intermediate

Identifying the Partially Committed state

What does the Partially Committed state indicate in a transaction lifecycle?

A The transaction has started but not yet executed any operations.

B The transaction has executed all operations but the changes are not yet permanent.

C The transaction has been rolled back due to an error.

D The transaction is waiting for user input to proceed.

💡 Hint

Consider what happens right after all operations finish but before final confirmation.

The Partially Committed state means the transaction has finished all its operations but the changes are not yet saved permanently until commit.

🔍 Analysis

advanced

Analyzing the Failed state impact

If a transaction enters the Failed state, what is the immediate next step the DBMS must take?

A Commit the transaction to save partial changes.

B Keep the transaction active until user decides.

C Rollback the transaction to undo all changes.

D Move the transaction to the Partially Committed state.

💡 Hint

Think about data consistency and what happens when a transaction fails.

When a transaction fails, the DBMS must rollback to undo any changes to keep the database consistent.

❓ Comparison

advanced

Comparing Committed and Terminated states

Which statement correctly distinguishes the Committed state from the Terminated state in transaction processing?

A Committed means changes are permanent; Terminated means transaction is complete and resources are released.

B Committed means transaction is active; Terminated means transaction is waiting to commit.

C Committed means transaction failed; Terminated means transaction is partially committed.

D Committed means rollback is done; Terminated means rollback is pending.

💡 Hint

Consider what happens after a transaction is saved permanently versus when it fully ends.

Committed means the transaction's changes are saved permanently. Terminated means the transaction has finished and all resources are freed.

❓ Reasoning

expert

Determining the number of transaction states

How many distinct states does a typical transaction go through in a DBMS lifecycle, including all intermediate and final states?

A 6

B 3

C 5

D 4

💡 Hint

Count all states: Active, Partially Committed, Committed, Failed, Aborted, Terminated.

A typical transaction lifecycle includes six states: Active, Partially Committed, Committed, Failed, Aborted, and Terminated.

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