BEGIN TRANSACTION syntax in SQL - Step-by-Step Execution

Concept Flow - BEGIN TRANSACTION syntax

Start

↓

BEGIN TRANSACTION

↓

Execute SQL Statements

↓

COMMIT or ROLLBACK

↓

End Transaction

This flow shows starting a transaction, running SQL commands, then either saving changes with COMMIT or undoing with ROLLBACK.

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 code starts a transaction, transfers 100 from account 1 to 2, then saves changes.

Execution Table

Step	Action	Transaction State	Effect on Data	Notes
1	BEGIN TRANSACTION	Transaction started	No data changed yet	Transaction begins, changes will be grouped
2	UPDATE accounts SET balance = balance - 100 WHERE id = 1	Transaction active	Account 1 balance decreased by 100 (pending)	Change is not yet permanent
3	UPDATE accounts SET balance = balance + 100 WHERE id = 2	Transaction active	Account 2 balance increased by 100 (pending)	Change is not yet permanent
4	COMMIT	Transaction ended	All changes saved permanently	Changes become visible to others
5	End	No active transaction	Data is consistent and saved	Transaction complete

💡 Transaction ends after COMMIT, changes are saved permanently

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4
Transaction State	No transaction	Active	Active	No transaction
Account 1 Balance	Original	Original - 100 (pending)	Original - 100 (pending)	Original - 100 (committed)
Account 2 Balance	Original	Original	Original + 100 (pending)	Original + 100 (committed)

Key Moments - 2 Insights

Why don't the balance changes appear permanent immediately after the UPDATE statements?

Because the transaction is still active (see steps 2 and 3 in execution_table), changes are pending and not visible outside until COMMIT.

What happens if we do not run COMMIT after BEGIN TRANSACTION?

The transaction remains open and changes are not saved; they can be undone with ROLLBACK or will be lost if connection closes.

Visual Quiz - 3 Questions

Look at the execution_table, what is the transaction state after step 3?

A No active transaction

B Transaction started but idle

C Transaction active

D Transaction committed

💡 Hint

Check the 'Transaction State' column at step 3 in execution_table

At which step do the balance changes become permanent?

A Step 3

B Step 4

C Step 2

D Step 5

💡 Hint

Look for when COMMIT happens in execution_table

If ROLLBACK was used instead of COMMIT at step 4, what would happen to the balances?

A Changes would be undone, balances stay original

B Changes would be saved permanently

C Only account 1 balance changes saved

D Only account 2 balance changes saved

💡 Hint

Recall that ROLLBACK cancels all changes in the transaction

Concept Snapshot

BEGIN TRANSACTION starts a group of SQL commands as one unit.
Changes inside are temporary until COMMIT saves them.
ROLLBACK cancels all changes in the transaction.
Use transactions to keep data consistent and safe.

Full Transcript

This lesson shows how BEGIN TRANSACTION starts a transaction in SQL. After starting, you can run multiple SQL commands. These changes are not permanent until you run COMMIT, which saves all changes together. If you run ROLLBACK instead, all changes are undone. This helps keep data safe and consistent by grouping related changes. The execution table shows each step: starting the transaction, updating balances, and committing changes. The variable tracker shows how the transaction state and account balances change step by step. Key moments explain why changes are pending until commit and what happens if commit is skipped. The quiz tests understanding of transaction states and effects of commit or rollback.