Why transactions are needed in SQL - Visual Breakdown

Concept Flow - Why transactions are needed

Start Transaction

↓

Execute Multiple Steps

↓

Check for Errors?

Yes→Rollback Transaction

↓

Undo Changes

↓

Commit Transaction

↓

End Transaction

A transaction groups multiple steps. If all succeed, changes are saved. If any fail, all changes are undone to keep data safe.

Execution Sample

SQL

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

This transfers 100 units from account 1 to account 2 as one safe operation.

Execution Table

Step	Action	State Before	State After	Notes
1	BEGIN TRANSACTION	No changes	Transaction started	Ready to group steps
2	UPDATE account 1 balance	balance=500	balance=400	Deduct 100 from account 1
3	UPDATE account 2 balance	balance=300	balance=400	Add 100 to account 2
4	COMMIT	Pending changes	Changes saved	Transaction successful
5	END TRANSACTION	Transaction committed	No active transaction	Process complete

💡 Transaction ends after commit, all changes saved together

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
account1_balance	500	400	400	400
account2_balance	300	300	400	400
transaction_state	none	active	active	committed

Key Moments - 2 Insights

Why do we need to group multiple updates in a transaction?

Because if one update fails, the transaction can rollback all changes to avoid partial updates, as shown in steps 2 and 3 in the execution_table.

What happens if an error occurs before COMMIT?

The transaction can rollback, undoing all changes made during the transaction, preventing inconsistent data.

Visual Quiz - 3 Questions

Look at the execution_table, what is the account1_balance after step 2?

A 400

B 500

C 300

D 100

💡 Hint

Check the 'State After' column for step 2 in execution_table

At which step are all changes saved permanently?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Look for the COMMIT action in execution_table

If an error occurs after step 2, what should happen to keep data safe?

A Ignore the error and continue

B Commit the transaction anyway

C Rollback the transaction

D Restart the database

💡 Hint

Refer to concept_flow where error leads to rollback

Concept Snapshot

Transactions group multiple database steps.
If all succeed, COMMIT saves changes.
If any fail, ROLLBACK undoes all changes.
This keeps data consistent and safe.
Use BEGIN TRANSACTION to start and COMMIT or ROLLBACK to end.

Full Transcript

A transaction in a database is like a safety net that groups several steps into one. When you start a transaction, you can make multiple changes. If all changes work well, you save them all at once with COMMIT. But if something goes wrong, you undo all changes with ROLLBACK. This way, the data stays correct and no partial updates happen. For example, transferring money between accounts needs two updates: subtracting from one and adding to another. If one update fails, the transaction rolls back so no money is lost or created wrongly. This process ensures data safety and consistency.

Practice

1. Why do we need transactions in a database?
BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;

easy

A. To make sure all steps succeed or none do, keeping data correct

B. To speed up the database queries

C. To allow multiple users to read data at the same time

D. To permanently delete data from the database

Solution

1. Step 1: Understand the purpose of transactions

   Transactions group multiple steps so they all succeed or all fail together, preventing partial updates.

2. Step 2: Analyze the example code

   The example moves money between accounts; if one update fails, the transaction rolls back to keep balances correct.

3. Final Answer:

   To make sure all steps succeed or none do, keeping data correct -> Option A

4. Quick Check:

   Transactions ensure data integrity = A [OK]

Hint: Transactions keep data safe by grouping steps [OK]

Common Mistakes:

• Thinking transactions speed up queries
• Confusing transactions with data deletion
• Believing transactions only allow multiple readers

2. Which of the following is the correct way to start a transaction in SQL?

easy

A. OPEN TRANSACTION;

B. CREATE TRANSACTION;

C. START TRANSACTION;

D. BEGIN TRANSACTION;

Solution

1. Step 1: Recall SQL syntax for transactions

   The standard way to start a transaction is using START TRANSACTION; or BEGIN TRANSACTION;.

2. Step 2: Check each option

   START TRANSACTION; is the most widely supported and standard syntax. CREATE TRANSACTION and OPEN TRANSACTION are invalid. BEGIN TRANSACTION is valid in some systems but START TRANSACTION is more standard.

3. Final Answer:

   START TRANSACTION; -> Option C

4. Quick Check:

   Start transaction command = START TRANSACTION [OK]

Hint: Use START TRANSACTION to start safely [OK]

Common Mistakes:

• Using CREATE TRANSACTION which is invalid
• Confusing START TRANSACTION with BEGIN TRANSACTION
• Trying OPEN TRANSACTION which doesn't exist

3. Consider this SQL transaction:

BEGIN TRANSACTION;
UPDATE inventory SET quantity = quantity - 5 WHERE product_id = 10;
UPDATE sales SET total = total + 5 WHERE sale_id = 3;
ROLLBACK;

What will be the final effect on the database?

medium

A. Both updates will be saved permanently

B. No changes will be saved to the database

C. Only the first update will be saved

D. Only the second update will be saved

Solution

1. Step 1: Understand the effect of ROLLBACK

   ROLLBACK cancels all changes made in the current transaction, undoing updates.

2. Step 2: Analyze the transaction steps

   Two updates happen, but ROLLBACK is called before COMMIT, so none of the changes are saved.

3. Final Answer:

   No changes will be saved to the database -> Option B

4. Quick Check:

   ROLLBACK cancels changes = C [OK]

Hint: ROLLBACK undoes all changes in a transaction [OK]

Common Mistakes:

• Assuming updates save without COMMIT
• Thinking partial updates remain after ROLLBACK
• Confusing ROLLBACK with COMMIT

4. This SQL code tries to transfer money but has an error:

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

What is the error and how to fix it?

medium

A. Missing semicolon after COMMIT; add ; to fix

B. Wrong order of updates; swap the two UPDATE statements

C. Should use ROLLBACK instead of COMMIT

D. BEGIN TRANSACTION is invalid syntax; use START TRANSACTION

Solution

1. Step 1: Check SQL statement syntax

   Each SQL statement must end with a semicolon. The COMMIT statement is missing a semicolon.

2. Step 2: Fix the syntax error

   Add a semicolon after COMMIT to complete the transaction properly.

3. Final Answer:

   Missing semicolon after COMMIT; add ; to fix -> Option A

4. Quick Check:

   SQL statements end with ; = B [OK]

Hint: End SQL statements with semicolon ; [OK]

Common Mistakes:

• Ignoring missing semicolon causing syntax error
• Thinking ROLLBACK replaces COMMIT here
• Believing transaction start syntax is wrong

5. You want to transfer $200 from account A to account B safely. Which transaction sequence ensures no money is lost if the second update fails?

1. BEGIN TRANSACTION;
2. UPDATE accounts SET balance = balance - 200 WHERE id = A;
3. UPDATE accounts SET balance = balance + 200 WHERE id = B;
4. COMMIT;

What should you add to handle failure properly?

hard

A. Use DELETE instead of UPDATE for account balances

B. Remove BEGIN TRANSACTION to speed up updates

C. Add COMMIT before step 3 to save first update early

D. Add ROLLBACK after step 3 if the second update fails

Solution

1. Step 1: Understand transaction failure handling

   If the second update fails, the transaction should be undone to avoid losing money.

2. Step 2: Use ROLLBACK to undo partial changes

   Adding ROLLBACK after detecting failure cancels all changes, keeping data consistent.

3. Step 3: Avoid premature COMMIT or removing transaction

   Committing early or removing transaction risks partial updates and data loss.

4. Final Answer:

   Add ROLLBACK after step 3 if the second update fails -> Option D

5. Quick Check:

   Use ROLLBACK on failure = A [OK]

Hint: Rollback if any step fails to keep data safe [OK]

Common Mistakes:

• Committing before all steps succeed
• Removing transaction causing partial updates
• Using DELETE instead of UPDATE for balances