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.