Transaction states in DBMS Theory - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a transaction in a database?

A transaction is a sequence of one or more database operations that are treated as a single unit. It must be completed fully or not at all to keep data consistent.

beginner

Name the five main states of a database transaction.

The five main states are: Active, Partially Committed, Committed, Failed, and Aborted.

beginner

What happens in the Active state of a transaction?

In the Active state, the transaction is executing its operations but has not yet completed all its tasks.

intermediate

Explain the difference between Committed and Aborted states.

Committed means the transaction has completed successfully and changes are saved permanently. Aborted means the transaction failed and all changes are undone to keep data safe.

intermediate

What is the Partially Committed state in a transaction?

Partially Committed is the state after a transaction has executed all its operations but before the changes are permanently saved.

Which transaction state means the transaction is currently running?

A Active

B Committed

C Aborted

D Failed

The Active state means the transaction is executing its operations.

What state follows after a transaction completes all its operations but before saving changes?

A Partially Committed

B Active

C Committed

D Aborted

Partially Committed is the state after all operations are done but before changes are saved.

If a transaction fails and undoes all changes, what state does it enter?

A Committed

B Aborted

C Partially Committed

D Active

Aborted means the transaction failed and rolled back all changes.

Which state indicates that changes made by a transaction are permanently saved?

A Active

B Failed

C Committed

D Aborted

Committed means the transaction completed successfully and changes are saved.

What is the first state of a transaction?

A Committed

B Failed

C Aborted

D Active

The transaction starts in the Active state when it begins execution.

Describe the sequence of states a transaction goes through from start to successful completion.

Think about what happens when a transaction starts, finishes operations, and saves changes.

Explain what happens when a transaction fails and how the database maintains consistency.

Consider how the system handles errors to keep data safe.

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