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.