[Solved] You want to transfer $200 from account A (id=1) to account B (id=2) safely. Which SQL transaction block correctly ensures the transfer is atomic and consistent? | SQL

SQL - Transactions and Data Integrity

You want to transfer $200 from account A (id=1) to account B (id=2) safely. Which SQL transaction block correctly ensures the transfer is atomic and consistent?

A

<pre>BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;</pre>

B

<pre>UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;</pre>

C

<pre>BEGIN TRANSACTION;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;</pre>

D

<pre>BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
ROLLBACK;</pre>

Step-by-Step Solution

Solution:

Step 1: Understand the need for atomic transfer
Both debit and credit updates must happen together or not at all to keep balances consistent.

Step 2: Evaluate each option

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;

uses BEGIN TRANSACTION and COMMIT to group both updates safely.

UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;

lacks transaction, risking partial update.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;

only credits without debit.

BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
ROLLBACK;

rolls back, so no change occurs.

Final Answer:

BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; COMMIT;
-> Option A

Quick Check:
Atomic transfer needs BEGIN TRANSACTION + COMMIT [OK]

Quick Trick: Use BEGIN TRANSACTION and COMMIT to group related updates [OK]

Common Mistakes:

Not grouping both updates in a transaction
Forgetting COMMIT to save changes
Rolling back changes unintentionally

Master "Transactions and Data Integrity" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

You want to transfer $200 from account A (id=1) to account B (id=2) safely. Which SQL transaction block correctly ensures the transfer is atomic and consistent?

Step 1: Understand the need for atomic transfer

Step 2: Evaluate each option

Final Answer:

Quick Check:

Want More Practice?