- Which changes remain after Session 1 rolls back?
Answer: Only employee 20 remains changed because its transaction committed independently.
When you see a question about transactions and rollbacks, the key concept to focus on is transaction isolation — each database transaction manages its own changes independently, and committing or rolling back one transaction has no effect on others.
Here's what actually happened in this scenario: Session 2 updated employee 20 and executed COMMIT, which permanently wrote that change to the database. From that moment forward, Session 2's work is done and locked in — nothing Session 1 does can touch it. When Session 1 later executes ROLLBACK, it only undoes its own uncommitted changes, meaning the update to employee 10 is reversed. Employee 20's record, already committed by a separate session, is completely unaffected. That makes D the correct answer.
A is wrong because it describes a fictional "cascade rollback" behavior. A rollback is scoped strictly to the transaction that issues it — it cannot reach across session boundaries and undo committed work from another session.
B is wrong on two levels: first, sessions don't share transactions by default, and second, Session 2's commit has no authority over Session 1's transaction whatsoever. Each BEGIN/COMMIT/ROLLBACK block is self-contained.
C gets the logic completely backwards. Rolling back removes your own changes — it doesn't preserve them while wiping out others. Session 1's rollback is precisely why employee 10's change is gone, not why it stays.
As a study tip, remember this rule: COMMIT makes changes permanent and visible; ROLLBACK only undoes the issuing session's own uncommitted work. Transaction boundaries never bleed across independent sessions.
- Which sequence of values should the sessions observe?
Answer: Session 1 sees 'READY'; Session 2 sees 'NEW', then 'READY' after the commit.
When a question describes multiple sessions interacting with the same data, you should immediately think about transaction isolation — specifically, what each session can and cannot see based on whether changes have been committed.
The key principle here is that under standard isolation (Read Committed or higher), a session's own uncommitted changes are visible to itself, but not to other sessions. So when Session 1 updates the status to 'READY' inside an open transaction, Session 1 can read its own write and will see 'READY'. Session 2, however, is blocked from seeing that uncommitted change and still sees the last committed value, 'NEW'. Once Session 1 commits, the change becomes part of the permanent record, and Session 2's next query will return 'READY'. This is exactly what answer A describes, making it correct.
Answer B inverts the logic entirely — it has Session 1 blind to its own update while Session 2 somehow sees it before commit. Neither part is accurate. Answer C claims both sessions see 'READY' before the commit, which would only happen under Read Uncommitted isolation (dirty reads), and then reverses the value after commit, which makes no sense — committing doesn't roll data back. Answer D suggests nothing ever changes for either session, as if the update never happened, which ignores that committed changes are always eventually visible to other sessions.
A useful mental model: think of an open transaction as a private "draft." The author (Session 1) can see the draft; everyone else sees the last published version until the author publishes (commits).
- Which transaction design best satisfies the requirement?
Answer: Begin first, perform both changes, commit only after validation, and otherwise roll back.
When a question asks about transaction design for multi-step operations that must succeed or fail together, you're being tested on atomicity — the "all or nothing" guarantee that transactions provide. The key principle: every change that must be reversible together must live inside the same transaction, and the commit should happen only after all validations pass.
Option A is the correct design because it wraps both the order creation and inventory reduction inside a single transaction, delays the commit until validation succeeds, and issues a rollback if anything goes wrong. This guarantees that neither change persists unless the entire workflow is confirmed valid — exactly what the requirement demands.
Option B is flawed because the order is created before the transaction begins, meaning it's already committed to the database before the transaction even starts. If validation later fails and you roll back, the order still exists — atomicity is broken from the start.
Option C commits the order creation immediately before reducing inventory. Once that first commit fires, the order change is permanent and cannot be rolled back. If the inventory step or validation fails, you're left with a dangling order and no way to undo it.
Option D has the logic of commit and rollback backwards — it rolls back after successful validation and commits when things go wrong. This would destroy valid work and permanently save invalid work, which is the exact opposite of correct transaction control.
A useful rule of thumb: one atomic outcome = one transaction. If multiple changes must be undoable together, they must all live between a single BEGIN and a single COMMIT or ROLLBACK.
- Why is the explicit rollback important in this scenario?
Answer: The rollback cancels the successful debit, preventing a partial transfer from remaining.
When working with database transactions, the core principle to keep in mind is atomicity — the idea that a group of operations must either all succeed or all fail together. No partial results should persist.
In this scenario, the transaction has two steps: debit account A and credit account B. The debit succeeds, but the credit silently fails because account B doesn't exist. A zero-row UPDATE is not an error in SQL — the database executes it without complaint and moves on. This means without an explicit ROLLBACK, the debit to account A would be committed alone, leaving the system in an inconsistent state: money vanished from A but never arrived at B. The rollback is critical because it undoes the successful debit, restoring account A to its original balance and ensuring no partial transfer lingers. That makes B the correct answer.
A is wrong because a zero-row UPDATE does not trigger an automatic commit — in fact, no automatic commit occurs mid-transaction in standard SQL behavior. This describes a nonexistent mechanism. C is a misunderstanding of what ROLLBACK does entirely; it undoes database changes, it does not create records or retry operations. D is a tempting distractor because it sounds surgical, but ROLLBACK is not selective — it reverses all uncommitted changes in the transaction, including the successful debit. You cannot roll back only part of a transaction with a plain ROLLBACK.
As a study tip, remember: ROLLBACK is all-or-nothing. Any question suggesting it can undo only some operations within a transaction (without savepoints) is describing incorrect behavior.
- What quantity is stored for
item_id = 8 after the sequence finishes? Answer: 12, because rollback reverses both updates in the transaction.
Whenever you see a question involving BEGIN, COMMIT, and ROLLBACK, you're being tested on transaction atomicity — the "all or nothing" principle that is one of the four ACID properties of relational databases.
A transaction groups multiple SQL statements into a single logical unit. When you issue a ROLLBACK, the database discards every change made since the BEGIN, restoring the data to exactly the state it was in before the transaction started. Neither partial saves nor ordered undos exist — it's a complete rewind.
Starting with quantity = 12, the first UPDATE would bring it to 9, and the second would bring it to 7. But because ROLLBACK is issued before any COMMIT, neither change is permanently written. The database reverts to 12, making D the correct answer.
A is wrong because it confuses execution with persistence. Yes, both updates ran, but running inside an uncommitted transaction means nothing is saved yet. B reflects a common misconception that ROLLBACK works like "undo" on only the last statement — it doesn't; it reverses the entire transaction. C makes the same partial-rollback mistake as B, just applied to the first update instead of the second. Neither B nor C reflects how SQL transactions actually work.
A useful mental model: think of a transaction as a draft document. COMMIT is "save and publish"; ROLLBACK is "discard all drafts." Until you commit, nothing is final. On SQL exams, always trace whether a COMMIT appears before ROLLBACK — if it doesn't, assume all changes vanish.
- Which rows remain after the sequence?
Answer: Only row 1, because autocommit completed it before the explicit transaction began.
When you see a question mixing autocommit with explicit transactions, the key is tracking which statements fall inside a transaction block — because only those are affected by a subsequent ROLLBACK.
With autocommit enabled, every SQL statement that executes outside an explicit transaction is immediately and permanently committed on its own. So the first INSERT (id = 1) runs before BEGIN is issued, meaning the database automatically commits it the moment it succeeds. That row is now permanent — no subsequent ROLLBACK can touch it. Once BEGIN starts the explicit transaction, the second INSERT (id = 2) runs inside that block. When ROLLBACK fires, it undoes everything since BEGIN — so row 2 disappears. Row 1, already committed, survives untouched. Answer A is correct.
Answer B has the logic completely backwards. ROLLBACK undoes work done inside the current transaction (after BEGIN), not work done before it. Statements committed before BEGIN are already permanent.
Answer C is wrong because a successful INSERT inside an open transaction is not permanent — it's only tentative until a COMMIT confirms it. ROLLBACK exists precisely to undo such tentative changes.
Answer D misunderstands transaction scope. ROLLBACK only reaches back to the most recent BEGIN, not to the entire session history. It has no power over already-committed statements.
A handy rule: draw a mental "fence" around BEGIN ... ROLLBACK. Everything inside the fence gets erased; everything outside and already committed is untouchable.
- What should an application conclude after reconnecting?
Answer: The three rows do not remain because the active transaction was never committed.
Whenever you see a question about transaction behavior, anchor your thinking to the ACID properties — specifically atomicity and durability. A transaction is treated as an all-or-nothing unit: either every change commits together, or none of them persist.
Here, the session opened a transaction with BEGIN, inserted three rows, and then lost its connection before issuing COMMIT. Because the transaction was never committed, the database treats it as incomplete. Normal transactional behavior requires the system to roll back any active, uncommitted transaction when its session ends — undoing all changes as if they never happened. This means none of the three rows remain, making C the correct answer.
Answer A confuses executing a statement with committing it. A successful INSERT means the statement ran without error, but durability only kicks in after a COMMIT finalizes the transaction. Rows written during an open transaction are not yet permanent. Answer B invents a fictional rule where BEGIN only protects the first statement — no such behavior exists. All statements within a transaction are equally protected (or equally discarded) together. Answer D introduces another invented rule: that losing a connection auto-commits the last completed statement. In reality, connection loss triggers a rollback, not a partial commit — the database never commits on your behalf due to disconnection.
A useful mental model: think of BEGIN/COMMIT like wrapping a gift. Until you seal it with COMMIT, nothing is truly delivered. If you drop it before sealing, everything inside is lost — not just part of it.
- Which conclusion about the inserted order is correct?
Answer: The order remains because rollback cannot reverse a transaction that was already committed.
When working with SQL transactions, the key concept to understand is transaction finality: once a COMMIT executes, those changes are permanently written to the database and no longer belong to an active transaction.
Here's the core logic: a ROLLBACK can only undo changes that exist within a currently open, uncommitted transaction. In this scenario, the INSERT was wrapped in a transaction that was already COMMITted — meaning the data was durably saved. The subsequent ROLLBACK finds nothing to undo because there is no active transaction with pending changes. The inserted order stays in the database, making B the correct conclusion.
A is wrong because rollback does not blindly reverse the most recent insert regardless of commit status. It only affects uncommitted work. Once committed, an insert is permanent and beyond rollback's reach.
C reflects a fundamental misunderstanding of how COMMIT works. A commit is not a pending state — it is the moment work becomes final. There is no "waiting for the next transaction" behavior; the data is immediately durable after COMMIT.
D is incorrect because commit and rollback do not have "equal priority" or compete with each other. They serve opposite purposes at different points: COMMIT finalizes changes, ROLLBACK discards uncommitted changes. Once one has executed, the other has no retroactive power over it.
A useful rule of thumb: committed = permanent, uncommitted = reversible. On transaction-related questions, always identify whether the data in question is still inside an open transaction before assuming rollback can affect it.
- What is the final stored balance for
account_id = 4? Answer: 120, because the first update was committed and the second was rolled back.
When you see SQL questions involving transactions, the key concept to focus on is transaction isolation: each BEGIN...COMMIT or BEGIN...ROLLBACK block is an independent unit of work, and its outcome doesn't affect other completed transactions.
Walk through the sequence step by step. The first transaction begins with balance = 100, adds 20, and ends with COMMIT — meaning that balance = 120 is permanently written to the database. No subsequent operation can undo a committed transaction. The second transaction then starts from balance = 120, subtracts 5 (making it 115 temporarily), but ends with ROLLBACK — discarding that change entirely. The database reverts to the last committed state: balance = 120. That makes C the correct answer.
A is wrong because a ROLLBACK only undoes changes within its own transaction. It has no power to reverse changes that were already committed in a prior transaction. B is incorrect because it assumes both updates stick, ignoring that ROLLBACK explicitly discards uncommitted changes — the -5 update never becomes permanent. D reflects a fundamental misconception: starting a new transaction has absolutely no effect on previously committed transactions. Transactions are independent; beginning one doesn't "cancel" or reopen anything that already completed.
A good rule of thumb to remember: COMMIT makes it permanent, ROLLBACK makes it disappear — but only within that transaction's scope. On exam questions like this, trace each transaction separately, note whether it ends in COMMIT or ROLLBACK, and carry forward only committed values.
- What counts are returned by the two
SELECT statements? Answer: First 0, then 5, because the session sees its delete before rollback restores the rows.
When you see a question involving transactions, rollback, and visibility, focus on a core principle: a session always sees its own uncommitted changes, but those changes can still be undone by a rollback.
Here's the logic step by step. The session opens a transaction with BEGIN, then deletes the five TEMP rows. Even though the transaction hasn't committed, the delete is real within that session's view. So the first SELECT COUNT(*) returns 0 — the rows are gone from the session's perspective. Then ROLLBACK fires, which undoes the delete entirely, restoring all five rows as if the deletion never happened. The second SELECT, now outside any transaction, sees the restored data and returns 5. That makes A correct.
B is backwards — it claims the delete only becomes visible after rollback, which inverts how transaction visibility works. Your own session sees your own changes immediately, not after undo.
C reflects a dangerous misconception: that rollback is powerless once a statement succeeds. In reality, ROLLBACK is specifically designed to undo all changes made during the transaction, regardless of whether individual statements succeeded. Committed transactions can't be rolled back, but uncommitted ones absolutely can.
D would be true for other sessions (which can't see your uncommitted deletes under standard isolation), but your own session is the exception — you always see your own in-progress changes.
A handy mental model: think of a transaction as a scratch pad. You see everything you've written on it, but if you crumple it up (rollback), the permanent record is untouched.
