ACID properties mental model in SQL - Step-by-Step Execution

Concept Flow - ACID properties mental model

Start Transaction

↓

Perform Operations

↓

Atomicity

↓

Commit or Rollback

↓

End

This flow shows a transaction starting, operations running, checking ACID properties, then committing or rolling back.

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 transfers 100 units from account 1 to account 2 within a transaction ensuring ACID properties.

Execution Table

Step	Action	Atomicity	Consistency	Isolation	Durability	Result
1	BEGIN TRANSACTION	Transaction started as a single unit	Database state is stable before changes	No other transactions interfere yet	Changes not yet permanent	Transaction open
2	UPDATE account 1 balance -100	Change recorded but not final	Balance remains consistent (no negative if checked)	Other transactions cannot see partial change	Change in memory, not on disk	Balance decreased in transaction
3	UPDATE account 2 balance +100	Change recorded but not final	Total money conserved, consistent state	Isolation maintained, no dirty reads	Change in memory, not on disk	Balance increased in transaction
4	COMMIT	All changes applied or none	Database moves to new consistent state	Other transactions now see changes	Changes saved permanently on disk	Transaction committed successfully
5	END	Transaction ends	Database stable	Isolation lifted	Durability ensured	Transaction complete

💡 Transaction ends after commit, ensuring all ACID properties are met

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
account_1_balance	1000	900	900	900	900
account_2_balance	500	500	600	600	600
transaction_state	none	open	open	committed	closed

Key Moments - 3 Insights

Why does the transaction not show partial changes to other users before commit?

Because of Isolation (see execution_table rows 2 and 3), changes are hidden from other transactions until commit.

What happens if the transaction fails before commit?

Atomicity ensures all changes are rolled back, so no partial updates remain (refer to execution_table step 4 commit importance).

How is data saved permanently after commit?

Durability guarantees changes are saved to disk and survive crashes (see execution_table step 4 durability column).

Visual Quiz - 3 Questions

Look at the execution_table, what is the account_1_balance after step 3?

A 1000

B 900

C 800

D 1100

💡 Hint

Check the variable_tracker for account_1_balance after step 3

At which step does the transaction become permanent and visible to others?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look at execution_table step 4 commit action and durability column

If the transaction is rolled back instead of committed, what happens to the balances?

A Balances revert to start values

B Balances remain changed

C Only account_1_balance changes

D Only account_2_balance changes

💡 Hint

Refer to key_moments about Atomicity and rollback behavior

Concept Snapshot

ACID properties ensure reliable transactions:
Atomicity: all or nothing
Consistency: valid data state
Isolation: no interference
Durability: changes saved
Use BEGIN, COMMIT, ROLLBACK to control transactions

Full Transcript

This visual execution shows a database transaction starting with BEGIN TRANSACTION. It performs two updates: subtracting 100 from account 1 and adding 100 to account 2. Throughout, Atomicity ensures these steps are treated as one unit, so either both succeed or none do. Consistency keeps the database valid, for example, total money conserved. Isolation hides these changes from other users until commit. Durability saves changes permanently on disk after COMMIT. The transaction ends successfully, leaving the database in a stable state. Variables like account balances and transaction state change step-by-step, showing how ACID properties protect data integrity.