ACID properties in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Atomicity in Transactions

Which statement best describes the Atomicity property in database transactions?

A All parts of a transaction must complete successfully, or none do.

B Data must be visible to all users immediately after a transaction.

C Once a transaction is committed, changes can be undone automatically.

D Transactions must be isolated from each other to prevent interference.

💡 Hint

Think about whether a transaction can be partially done.

Atomicity means a transaction is all or nothing: either every step succeeds or the database stays unchanged.

🧠 Conceptual

intermediate

Isolation Levels and Their Effects

Which isolation level allows a transaction to read uncommitted changes made by other transactions?

A Repeatable Read

B Serializable

C Read Committed

D Read Uncommitted

💡 Hint

Consider the lowest isolation level that permits dirty reads.

Read Uncommitted isolation level allows transactions to see uncommitted changes, which can cause dirty reads.

❓ query_result

advanced

Effect of COMMIT on Durable Data

Consider this sequence of SQL commands in MySQL:

START TRANSACTION;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
COMMIT;
SELECT balance FROM accounts WHERE id = 1;

What will the SELECT query return?

A NULL

B 1000

C 0

D An error because the transaction is not closed

💡 Hint

Think about what COMMIT does to the inserted data.

COMMIT makes the inserted data permanent and visible, so the SELECT returns 1000.

🔧 Debug

advanced

Diagnosing a Lost Update Problem

Two transactions run concurrently on the same row in a table. Transaction A reads a value, Transaction B updates it and commits, then Transaction A updates the value based on its old read and commits.

What ACID property is violated here?

A Isolation

B Consistency

C Atomicity

D Durability

💡 Hint

Think about whether transactions interfere with each other.

This is a classic lost update problem caused by lack of proper isolation between transactions.

📝 Syntax

expert

Correct Use of SAVEPOINT and ROLLBACK

Which of the following MySQL transaction sequences correctly uses SAVEPOINT and ROLLBACK to undo part of a transaction without aborting the entire transaction?

-- Assume a transaction is started

A

SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
ROLLBACK;
COMMIT;

B

SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
ROLLBACK TO sp2;
COMMIT;

C

SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
ROLLBACK TO sp1;
COMMIT;

D

SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
ROLLBACK TO sp1;

💡 Hint

ROLLBACK TO must refer to an existing savepoint and happens before COMMIT.

Option C correctly creates a savepoint, rolls back to it (undoing the update), then commits the rest.

Option C rolls back the entire transaction, not just to the savepoint.

Option C tries to rollback to a non-existent savepoint sp2.

Option C tries to rollback after commit, which is invalid.

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