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.

Practice

1. Which ACID property ensures that a transaction is completed fully or not at all, like finishing a whole recipe or not starting it?

easy

A. Isolation

B. Consistency

C. Atomicity

D. Durability

Solution

1. Step 1: Understand Atomicity meaning

   Atomicity means a transaction is all-or-nothing, like a recipe that must be fully done or not started.

2. Step 2: Compare with other properties

   Consistency means data rules are followed, Isolation means transactions don't interfere, Durability means changes are saved permanently.

3. Final Answer:

   Atomicity -> Option C

4. Quick Check:

   All or nothing = Atomicity [OK]

Hint: All or nothing means Atomicity [OK]

Common Mistakes:

• Confusing Consistency with Atomicity
• Thinking Isolation means all-or-nothing
• Mixing Durability with Atomicity

2. Which of the following SQL transaction commands is used to make all changes permanent, supporting the Durability property?

easy

A. ROLLBACK

B. COMMIT

C. SAVEPOINT

D. BEGIN TRANSACTION

Solution

1. Step 1: Identify command for saving changes

   COMMIT saves all changes permanently, ensuring Durability.

2. Step 2: Understand other commands

   ROLLBACK undoes changes, SAVEPOINT marks a point to rollback to, BEGIN TRANSACTION starts a transaction.

3. Final Answer:

   COMMIT -> Option B

4. Quick Check:

   Permanent save = COMMIT [OK]

Hint: COMMIT makes changes permanent [OK]

Common Mistakes:

• Choosing ROLLBACK to save changes
• Confusing SAVEPOINT with COMMIT
• Thinking BEGIN TRANSACTION saves data

3. Consider two transactions running at the same time: Transaction 1 adds 100 to an account balance, Transaction 2 subtracts 50. Which ACID property ensures the final balance is correct as if transactions ran one after another?

medium

A. Isolation

B. Consistency

C. Atomicity

D. Durability

Solution

1. Step 1: Understand concurrent transactions

   When two transactions run together, Isolation ensures they don't interfere and produce correct results.

2. Step 2: Differentiate from other properties

   Atomicity is about all-or-nothing, Consistency is about data rules, Durability is about saving changes permanently.

3. Final Answer:

   Isolation -> Option A

4. Quick Check:

   Transactions separated = Isolation [OK]

Hint: No interference means Isolation [OK]

Common Mistakes:

• Mixing Consistency with Isolation
• Thinking Atomicity handles concurrency
• Confusing Durability with Isolation

4. A developer notices that after a system crash, some committed transactions are lost. Which ACID property is likely violated, and what should be fixed?

medium

A. Atomicity; ensure transactions are all-or-nothing

B. Consistency; fix data validation rules

C. Isolation; improve transaction locking

D. Durability; improve data saving and recovery

Solution

1. Step 1: Identify property related to crash recovery

   Durability ensures committed transactions survive crashes and are saved permanently.

2. Step 2: Match problem to property

   Lost committed transactions after crash means Durability is violated and saving/recovery must be improved.

3. Final Answer:

   Durability; improve data saving and recovery -> Option D

4. Quick Check:

   Crash survival = Durability [OK]

Hint: Lost data after crash means Durability issue [OK]

Common Mistakes:

• Confusing Atomicity with crash recovery
• Thinking Isolation fixes lost data
• Mixing Consistency with durability problems

5. A bank system must ensure that transferring money from one account to another never leaves the database in an incorrect state, even if the system crashes or many users act at once. Which combination of ACID properties best guarantees this?

hard

A. Atomicity, Consistency, and Durability

B. Consistency and Isolation

C. Isolation and Durability

D. Atomicity and Durability

Solution

1. Step 1: Identify properties needed for correctness and crash safety

   Atomicity ensures full transfer or none, Consistency keeps data valid, Durability saves changes permanently.

2. Step 2: Understand why Isolation alone is not enough

   Isolation prevents interference but does not guarantee correctness or crash safety alone.

3. Step 3: Combine properties for full guarantee

   Atomicity + Consistency + Durability together ensure safe, correct, and permanent transfers.

4. Final Answer:

   Atomicity, Consistency, and Durability -> Option A

5. Quick Check:

   Safe + correct + permanent = Atomicity + Consistency + Durability [OK]

Hint: All but Isolation ensure correctness and crash safety [OK]

Common Mistakes:

• Ignoring Consistency in correctness
• Thinking Isolation alone is enough
• Missing Durability for crash safety