ACID properties in MySQL - Step-by-Step Execution

Concept Flow - ACID properties

Start Transaction

↓

Perform Operations

↓

Check Atomicity

↓

Check Consistency

↓

Check Isolation

↓

Check Durability

↓

Commit or Rollback

↓

End

This flow shows how a database transaction follows ACID properties step-by-step to ensure reliable data handling.

Execution Sample

MySQL

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

This transaction transfers 100 units from account 1 to account 2, ensuring ACID properties.

Execution Table

Step	Action	Atomicity	Consistency	Isolation	Durability	Result
1	START TRANSACTION	Transaction started as a single unit	Database state unchanged yet	Transaction isolated from others	Changes not yet durable	Transaction open
2	UPDATE accounts SET balance = balance - 100 WHERE id = 1	Partial update done, still part of transaction	Balance remains consistent (no negative if checked)	Other transactions cannot see this change yet	Change not durable until commit	Balance of account 1 decreased by 100 in transaction
3	UPDATE accounts SET balance = balance + 100 WHERE id = 2	Still atomic, both updates part of one transaction	Balances consistent after update	Isolation maintained	Not durable yet	Balance of account 2 increased by 100 in transaction
4	COMMIT	All changes applied together or none	Database state consistent after commit	Changes visible to other transactions now	Changes saved permanently	Transaction committed successfully
5	END	Transaction finished	Database consistent	Isolation no longer applies	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 (Commit)
balance_account_1	1000	900	900	900
balance_account_2	500	500	600	600
transaction_state	none	open	open	committed

Key Moments - 3 Insights

Why does the balance of account 1 not permanently change after step 2?

Because the transaction is still open (see execution_table step 2), changes are not durable or visible outside until COMMIT.

How does isolation protect the transaction during updates?

Isolation ensures other transactions cannot see partial changes (execution_table steps 2 and 3), preventing inconsistent reads.

What happens if the transaction is rolled back instead of committed?

All changes are undone together, maintaining atomicity and consistency by not applying partial updates.

Visual Quiz - 3 Questions

Look at the execution table, what is the state of 'transaction_state' after step 3?

A committed

B none

C open

D rolled back

💡 Hint

Check variable_tracker column 'After Step 3' for 'transaction_state'

At which step do changes become permanent and visible to other transactions?

A Step 3

B Step 4

C Step 2

D Step 5

💡 Hint

Look at execution_table step 4 under Durability and Isolation columns

If the transaction was rolled back after step 3, what would happen to the balances?

A Balances revert to start values

B Only account 1 balance reverts

C Balances remain changed as in step 3

D Only account 2 balance reverts

💡 Hint

Atomicity means all or nothing; see key_moments explanation about rollback

Concept Snapshot

ACID properties ensure reliable transactions:
Atomicity: all or nothing changes
Consistency: valid data state
Isolation: transactions don't interfere
Durability: changes saved permanently
Use START TRANSACTION, COMMIT, ROLLBACK in SQL

Full Transcript

This visual execution traces a simple money transfer transaction in MySQL demonstrating ACID properties. The transaction starts, performs two updates, then commits. Atomicity ensures both updates succeed or fail together. Consistency keeps balances valid. Isolation hides partial changes from others. Durability saves changes permanently after commit. Variable tracking shows balances and transaction state at each step. Key moments clarify why changes are not permanent before commit and how rollback would undo all changes. The quiz tests understanding of transaction states and ACID effects.

Practice

1. Which of the following is NOT one of the ACID properties in databases?

easy

A. Durability and Speed

B. Consistency

C. Isolation

D. Atomicity

Solution

1. Step 1: Recall the ACID properties

   ACID stands for Atomicity, Consistency, Isolation, and Durability.

2. Step 2: Identify the incorrect option

   Durability and Speed adds "Speed" which is not part of ACID properties.

3. Final Answer:

   Durability and Speed -> Option A

4. Quick Check:

   ACID excludes Speed [OK]

Hint: Remember ACID = Atomicity, Consistency, Isolation, Durability [OK]

Common Mistakes:

• Confusing performance terms with ACID properties
• Thinking Speed is part of ACID
• Mixing up Isolation with Integrity

2. Which SQL statement correctly starts a transaction in MySQL?

easy

A. BEGIN TRANSACTION;

B. START TRANSACTION;

C. OPEN TRANSACTION;

D. INIT TRANSACTION;

Solution

1. Step 1: Recall MySQL transaction syntax

   MySQL uses START TRANSACTION; to begin a transaction.

2. Step 2: Check other options

   Options A, B, and D are not valid MySQL commands for starting transactions.

3. Final Answer:

   START TRANSACTION; -> Option B

4. Quick Check:

   MySQL uses START TRANSACTION [OK]

Hint: Use START TRANSACTION to begin in MySQL [OK]

Common Mistakes:

• Using BEGIN TRANSACTION which is not valid in MySQL
• Confusing with other SQL dialects
• Typing incorrect keywords like OPEN or INIT

3. Consider this sequence in MySQL:

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

What ACID property does this sequence mainly demonstrate?

medium

A. Consistency - data types are checked

B. Isolation - changes are visible immediately

C. Durability - changes are temporary

D. Atomicity - all changes happen or none

Solution

1. Step 1: Understand the transaction sequence

   The two updates are grouped inside a transaction with COMMIT at the end.

2. Step 2: Identify the ACID property

   Atomicity ensures either both updates happen or none do, preventing partial changes.

3. Final Answer:

   Atomicity - all changes happen or none -> Option D

4. Quick Check:

   Transaction groups changes atomically [OK]

Hint: Atomicity means all or nothing in transactions [OK]

Common Mistakes:

• Confusing Isolation with Atomicity
• Thinking Durability means temporary changes
• Assuming Consistency only checks data types

4. You wrote this MySQL transaction:

START TRANSACTION;
UPDATE inventory SET quantity = quantity - 5 WHERE product_id = 10;
COMMIT;

But sometimes the quantity becomes negative. What ACID property is likely violated?

medium

A. Consistency

B. Isolation

C. Durability

D. Atomicity

Solution

1. Step 1: Analyze the problem

   Quantity becoming negative means data rules are broken.

2. Step 2: Identify violated ACID property

   Consistency ensures data stays valid and follows rules; here it is violated.

3. Final Answer:

   Consistency -> Option A

4. Quick Check:

   Consistency keeps data valid [OK]

Hint: Consistency means data must follow rules always [OK]

Common Mistakes:

• Thinking Atomicity prevents negative values
• Confusing Isolation with data validity
• Assuming Durability fixes data rules

5. In a multi-user MySQL environment, two transactions run:

Transaction 1:
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- waits before COMMIT

Transaction 2:
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

Which ACID property ensures Transaction 2 does not see the uncommitted balance change from Transaction 1?

hard

A. Atomicity

B. Durability

C. Isolation

D. Consistency

Solution

1. Step 1: Understand concurrent transactions

   Transaction 1 updates but delays commit; Transaction 2 reads the same data.

2. Step 2: Identify ACID property controlling visibility

   Isolation ensures uncommitted changes are hidden from other transactions.

3. Final Answer:

   Isolation -> Option C

4. Quick Check:

   Isolation hides uncommitted changes [OK]

Hint: Isolation hides uncommitted changes from others [OK]

Common Mistakes:

• Confusing Isolation with Atomicity
• Thinking Durability controls visibility
• Assuming Consistency hides uncommitted data