SQL Quiz: Transactions
10 questions · exam conditions
0:00
TransactionsQuestion 1 of 10

An application begins a transaction and successfully subtracts 40 from account A. It then attempts to add 40 to account B, but the second UPDATE affects zero rows because account B does not exist. The application checks the affected-row count and executes ROLLBACK.

Why is the explicit rollback important in this scenario?

A zero-row update automatically commits the earlier debit unless rollback is issued immediately.
The rollback cancels the successful debit, preventing a partial transfer from remaining.
The rollback creates account B, allowing the credit operation to be attempted again.
The rollback preserves the debit while canceling only the update that affected zero rows.
← Back to quizzes

SQL Quiz

SQL Quiz: Transactions

Practice Transactions in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Transactions, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

An application begins a transaction and successfully subtracts 40 from account A. It then attempts to add 40 to account B, but the second UPDATE affects zero rows because account B does not exist. The application checks the affected-row count and executes ROLLBACK.

Why is the explicit rollback important in this scenario?

  1. A zero-row update automatically commits the earlier debit unless rollback is issued immediately.
  2. The rollback cancels the successful debit, preventing a partial transfer from remaining. (correct answer)
  3. The rollback creates account B, allowing the credit operation to be attempted again.
  4. The rollback preserves the debit while canceling only the update that affected zero rows.
Explanation: 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.

Question 2

Assume the database's normal transaction isolation prevents other sessions from reading uncommitted changes. A row begins with status = 'NEW'.

Session 1 executes BEGIN and updates the status to 'READY' without committing. Session 1 then queries the row, and Session 2 separately queries the same row. Afterward, Session 1 executes COMMIT, and Session 2 queries again.

Which sequence of values should the sessions observe?

  1. Session 1 sees 'READY'; Session 2 sees 'NEW', then 'READY' after the commit. (correct answer)
  2. Session 1 sees 'NEW'; Session 2 sees 'READY' before the commit, then 'NEW' after the commit.
  3. Both sessions see 'READY' before the commit and 'NEW' after the commit completes.
  4. Both sessions see 'NEW' throughout, regardless of whether Session 1 commits or rolls back.
Explanation: 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).

Question 3

A session executes BEGIN and successfully inserts three rows. Before it executes COMMIT, the connection is lost. Assume the database follows normal transactional behavior and rolls back an active transaction when its session terminates.

What should an application conclude after reconnecting?

  1. All three rows remain because successful insert statements are durable before commit.
  2. Only the first row remains because beginning a transaction protects later statements only.
  3. The three rows do not remain because the active transaction was never committed. (correct answer)
  4. Exactly one row remains because connection loss commits the final completed statement.
Explanation: 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.

Question 4

A table contains an inventory row with quantity = 12. The following statements execute successfully in one session with autocommit disabled:

BEGIN; UPDATE inventory SET quantity = quantity - 3 WHERE item_id = 8; UPDATE inventory SET quantity = quantity - 2 WHERE item_id = 8; ROLLBACK;

What quantity is stored for item_id = 8 after the sequence finishes?

  1. 7, because both updates execute before the rollback statement.
  2. 9, because rollback reverses only the most recent update.
  3. 10, because rollback preserves the final successful update.
  4. 12, because rollback reverses both updates in the transaction. (correct answer)
Explanation: 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.

Question 5

Two sessions modify unrelated rows. Session 1 executes BEGIN and updates employee 10, but does not commit. Session 2 updates employee 20 in its own transaction and executes COMMIT. Session 1 then executes ROLLBACK. Both updates initially executed successfully.

Which changes remain after Session 1 rolls back?

  1. Neither update remains because one session's rollback cancels all recent database changes.
  2. Both updates remain because Session 2's commit also completes Session 1's transaction.
  3. Only employee 10 remains changed because rollback applies to other sessions' work.
  4. Only employee 20 remains changed because its transaction committed independently. (correct answer)
Explanation: 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.

Question 6

A session executes BEGIN, inserts an order, and executes COMMIT. It then issues ROLLBACK while no new transaction containing changes has been started. Depending on the database system, that final command may report an error or have no effect.

Which conclusion about the inserted order is correct?

  1. The order is removed because rollback always reverses the session's most recent insert.
  2. The order remains because rollback cannot reverse a transaction that was already committed. (correct answer)
  3. The order is pending because commit does not complete work until the next transaction begins.
  4. The order's status is unknown because commit and rollback have equal priority.
Explanation: 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.

Question 7

A table contains five rows with category = 'TEMP'. One session executes:

BEGIN; DELETE FROM records WHERE category = 'TEMP'; SELECT COUNT(*) FROM records WHERE category = 'TEMP'; ROLLBACK; SELECT COUNT(*) FROM records WHERE category = 'TEMP';

No other session changes the table.

What counts are returned by the two SELECT statements?

  1. First 0, then 5, because the session sees its delete before rollback restores the rows. (correct answer)
  2. First 5, then 0, because deletion becomes visible only when rollback is executed.
  3. First 0, then 0, because rollback cannot restore rows deleted by a successful statement.
  4. First 5, then 5, because uncommitted deletes are invisible even to their own session.
Explanation: 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.

Question 8

An application must create an order and reduce inventory. If either operation fails or a later validation check fails, neither change should remain.

Which transaction design best satisfies the requirement?

  1. Begin first, perform both changes, commit only after validation, and otherwise roll back. (correct answer)
  2. Create the order first, begin before reducing inventory, and roll back if validation fails.
  3. Begin first, create the order, commit it, then reduce inventory and validate afterward.
  4. Begin first, perform both changes, roll back after successful validation, and otherwise commit.
Explanation: 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.

Question 9

A row initially has balance = 100. The following statements execute successfully:

BEGIN; UPDATE accounts SET balance = balance + 20 WHERE account_id = 4; COMMIT; BEGIN; UPDATE accounts SET balance = balance - 5 WHERE account_id = 4; ROLLBACK;

What is the final stored balance for account_id = 4?

  1. 95, because the rollback reverses every change shown in the sequence.
  2. 115, because both updates occur before the final rollback completes.
  3. 120, because the first update was committed and the second was rolled back. (correct answer)
  4. 100, because starting the second transaction cancels the first transaction.
Explanation: 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.

Question 10

Autocommit is enabled initially. A session executes these statements successfully:

INSERT INTO audit_log(id) VALUES (1); BEGIN; INSERT INTO audit_log(id) VALUES (2); ROLLBACK;

Assume each id was absent before the sequence began.

Which rows remain after the sequence?

  1. Only row 1, because autocommit completed it before the explicit transaction began. (correct answer)
  2. Only row 2, because rollback applies to work performed before BEGIN only.
  3. Both rows, because each successful INSERT is permanent even inside a transaction.
  4. Neither row, because rollback includes all statements executed by the current session.
Explanation: 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.