Transaction-state MCQs look short but mix three decisions: what a state means, which transition is immediate, and what COMMIT or ROLLBACK changes. A familiar word can still be wrong when the question asks for a named state.
The lifecycle has six named states, and two of them look like success: PARTIALLY COMMITTED means the last statement has run, while COMMITTED means the effects survive a crash. Most wrong answers pick one where the question wants the other.
Use one method: locate the transaction's last completed event, decide whether its effects are durable, then follow one legal outgoing transition from its current state. For the wider syllabus, start with GATE CS courses and test preparation.
Transaction states in DBMS: build the state map first
ACTIVE means instructions are executing. The final statement moves the transaction to PARTIALLY COMMITTED, where durability is not guaranteed. Durable success means COMMITTED. A transaction that cannot continue is FAILED; after undo, it is ABORTED. TERMINATED means cleanup is complete.
State | What has happened | Durable yet? |
|---|---|---|
ACTIVE | Instructions are executing | No |
PARTIALLY COMMITTED | The final statement has executed | Not guaranteed |
COMMITTED | Successful effects are permanent | Yes |
FAILED | Execution cannot continue | No normal progress |
ABORTED | Undo is complete | Effects undone |
TERMINATED | Cleanup is complete | Lifecycle finished |
Trace transaction T. Its balance starts at 1500, and savepoint S records that value. T adds 100, so 1500 + 100 = 1600, provisionally. ROLLBACK TO S restores 1500. COMMIT moves T from ACTIVE to PARTIALLY COMMITTED, then COMMITTED when its record is durable, and finally TERMINATED after cleanup.
If a crash prevents durability after the final statement, PARTIALLY COMMITTED moves to FAILED. Recovery restores 1500 and marks T ABORTED. ACTIVE never jumps directly to COMMITTED, and FAILED is not ABORTED because undo may still be pending. Practise the wider topic with DBMS Transaction MCQs on ACID and locking.
Transaction basics and immediate state transitions MCQs
A named state has exactly one spelling in the syllabus, and each state has a fixed set of legal successors. Do not substitute an everyday word for a named state, and do not skip an intermediate one.
Q1. What consists of query and update statements?
A _________ consists of a sequence of query and/or update statements.
(A) Transaction
(B) Commit
(C) Rollback
(D) Flashback
Answer: (A) Transaction.
A transaction is the logical unit containing queries and updates. COMMIT and ROLLBACK control that unit; flashback is not its generic name.
Q2. Identify the non-state term
Which of these does NOT refer to a transaction state in DBMS?
(A) Finish
(B) Active
(C) Partially committed
(D) Aborted
Answer: (A) Finish.
The formal terminal state is TERMINATED, not "Finish". ACTIVE, PARTIALLY COMMITTED and ABORTED are standard named states.
Q3. Next states after ACTIVE
UGC NET 2021
A transaction may be in one of the following states during its execution life cycle in concurrent execution environment.
A. FAILED
B. TERMINATED
C. PARTIALLY COMMITTED
D. COMMITTED
E. ACTIVE
Given a transaction in active state during its execution, find its next transitioned state from the options given below:
(A) A only
(B) Either A or C only
(C) C only
(D) D only
Answer: (B) Either A or C only. In the stem's list, A is FAILED and C is PARTIALLY COMMITTED.
An ACTIVE transaction can fail and move to FAILED, or finish its last statement and become PARTIALLY COMMITTED. COMMITTED and TERMINATED are later outcomes, not immediate successors.
COMMIT MCQs: permanence and the success path
The boundary matters: finishing the last update produces PARTIALLY COMMITTED; making the commit information durable produces COMMITTED.
If account A starts at 900 and T adds 60, the working value is 900 + 60 = 960. Finishing the update means PARTIALLY COMMITTED. Once COMMIT information is durable, T is COMMITTED and recovery preserves 960; before then, failure may require undo.
Q4. Command that makes updates permanent
BPSC 2023, PGT Tier-1
____ command makes the updates performed by the transaction permanent in the database.
(A) ROLLBACK
(B) COMMIT
(C) TRUNCATE
(D) More than one of the above
(E) None of the above
Answer: (B) COMMIT.
COMMIT makes the transaction's updates permanent. ROLLBACK reverses uncommitted work; TRUNCATE is not the transaction-success command asked for.
Q5. Primary purpose of COMMIT
Beltron Programmer 2025, Shift-3
What is the primary purpose of the COMMIT operation in a database management transaction?
(A) To permanently apply all changes made during the transaction
(B) To temporarily save the changes for testing
(C) To rollback the transaction to its initial state
(D) To check the consistency of the database schema
Answer: (A) To permanently apply all changes made during the transaction.
COMMIT establishes durability for a successful transaction. Temporary testing, rollback and schema checking are separate operations.
ROLLBACK and SAVEPOINT MCQs: undo the right amount
ROLLBACK restores the relevant earlier consistent state. Full rollback abandons uncommitted work; ROLLBACK TO S keeps work before S and reverses work after it.
Q6. Primary role of ROLLBACK
Beltron Programmer 2025, Shift-3
What is the primary role of a rollback operation in transaction processing?
(A) To make the changes of a transaction permanent in the database
(B) To delay the execution of a transaction until system resources are available
(C) To reset the database system to its initial installation state
(D) To undo all changes made by a transaction and restore the database to its previous consistent state
Answer: (D) To undo all changes made by a transaction and restore the database to its previous consistent state.
Rollback undoes work that must not become durable. COMMIT provides permanence; resource waits and installation resets are unrelated.
Q7. SAVEPOINT, rollback and final balance
IBPS 2025
Transaction sequence: A bank account starts with balance 1500.
A SAVEPOINT is set.
100 is added to the account.
ROLLBACK TO SAVEPOINT is performed.
COMMIT is issued.
What is the final account balance?
(A) Account increases
(B) Account decreases
(C) Account remains same (1500)
(D) Account becomes 0
(E) None of the above
Answer: (C) Account remains same (1500).
The savepoint records 1500. Then 1500 + 100 = 1600, and ROLLBACK TO SAVEPOINT restores 1500. COMMIT makes 1500 durable, so the final value equals the starting value.
FAILED transactions and recovery logs MCQs
A failure stops normal execution. The transaction log supplies evidence for undo and redo.
Q8. State reached when an ACTIVE transaction aborts
UPPSC Polytechnic Lecturer 2022
If a transaction is aborted during its active state, it will go in a ___
(A) Failed state
(B) Terminated state
(C) Committed state
(D) Partially committed state
Answer: (A) Failed state.
When ACTIVE execution must stop, the transaction first becomes FAILED. After recovery undoes its effects, it is ABORTED and may restart or terminate.
Q9. Record used for database recovery
ISRO 2009
Which of the following contains complete record of all activity that affected the contents of a database during a certain period of time?
(A) Transaction log
(B) Query language
(C) Report writer
(D) Data manipulation language
Answer: (A) Transaction log.
With A=900, the log records <T1 start> and <T1, A, 900, 700> before writing A=700; <T1 commit> later proves success. Without a durable commit record after a crash, the old value 900 supports undo. Query languages and report writers are not recovery histories.
Connect logs and failure states to the wider DBMS MCQs practice set.
Recoverability and atomic commit protocol MCQs
Recoverability depends on the order of reads, writes, commits and aborts. Distributed commit also requires non-faulty participants to decide compatibly under valid conditions.
Q10. Dirty reads and an irrecoverable schedule
Which of the following scenarios may lead to an irrecoverable error in a database system?
(A) A transaction writes a data item after it is read by an uncommitted transaction.
(B) A transaction reads a data item after it is read by an uncommitted transaction.
(C) A transaction reads a data item after it is written by a committed transaction.
(D) A transaction reads a data item after it is written by an uncommitted transaction.
Answer: (D) A transaction reads a data item after it is written by an uncommitted transaction.
Let x start at 50. T1 writes provisional x=80; T2 reads 80 and commits. If T1 aborts and restores 50, T2 has committed using a disappearing value, so the schedule is irrecoverable. This dirty read may cause the error when the reader commits first.
Q11. Incorrect atomic commit property
UPPSC Polytechnic Lecturer 2022
Which of the following is incorrect for atomic commit protocol to solve a variation of the consensus problem?
(A) Termination
(B) Agreement
(C) Validity
(D) Sincerity
Answer: (D) Sincerity.
Termination, agreement and validity are recognised requirements. If coordinator C asks S1 and S2 to vote, two YES votes can support COMMIT; if S2 votes NO, the valid global decision is ABORT. SINCERITY is not a named property here.
Transaction states MCQs: the short revision rule and next step
Use this six-line ladder when you revise:
Executing means ACTIVE.
Last statement done but not yet durable means PARTIALLY COMMITTED.
Durable success means COMMITTED.
Cannot continue means FAILED.
Undo complete means ABORTED.
Cleanup complete means TERMINATED.
Add the command pair: COMMIT preserves successful effects; ROLLBACK removes relevant uncommitted effects. Q1 and Q2 test definitions, Q3 and Q8 transitions, Q4 to Q7 commands, Q9 and Q10 recovery, and Q11 protocol vocabulary.
Redraw the graph and recompute 1500 -> 1600 -> 1500 unaided. Next, try DBMS Normalization MCQs. For structured GATE CS preparation, GATE Guidance by Sanchit Sir builds the same state-by-state reasoning across the full syllabus.
