Why transactions ensure data integrity in MySQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why do transactions help keep data accurate?

Imagine you are transferring money between two bank accounts. Why does using a transaction help keep the data correct?

A Because transactions make sure all steps happen together or none happen, so money isn't lost or duplicated.

B Because transactions speed up the transfer by skipping checks.

C Because transactions allow multiple users to change the same data at the same time without waiting.

D Because transactions automatically fix any mistakes made during the transfer.

💡 Hint

Think about what happens if only part of the transfer happens.

Transactions group multiple steps so they all succeed or all fail. This prevents partial updates that could cause errors like money disappearing or being counted twice.

❓ query_result

intermediate

What is the output after a failed transaction?

Given this SQL code, what will be the final balance in the accounts table? BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; ROLLBACK; SELECT id, balance FROM accounts WHERE id IN (1, 2);

MySQL

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;
SELECT id, balance FROM accounts WHERE id IN (1, 2);

A [{id: 1, balance: 1000}, {id: 2, balance: 500}]

B [{id: 1, balance: 900}, {id: 2, balance: 600}]

C Syntax error due to missing COMMIT

D Empty result set

💡 Hint

ROLLBACK undoes all changes made in the transaction.

ROLLBACK cancels all changes made since BEGIN, so balances remain as before the transaction started.

📝 Syntax

advanced

Identify the correct transaction syntax

Which SQL snippet correctly starts a transaction, updates a table, and commits the changes?

A

TRANSACTION START;
UPDATE users SET points = points + 10 WHERE id = 5;
COMMIT TRANSACTION;

B

START TRANSACTION;
UPDATE users SET points = points + 10 WHERE id = 5;
COMMIT;

C

BEGIN TRANSACTION
UPDATE users SET points = points + 10 WHERE id = 5
END;

D

BEGIN;
UPDATE users SET points = points + 10 WHERE id = 5;
ROLLBACK;

💡 Hint

Look for the standard MySQL transaction commands.

In MySQL, START TRANSACTION begins a transaction, and COMMIT saves changes. Other options use incorrect or incomplete syntax.

❓ optimization

advanced

How to improve transaction performance without losing integrity?

You have a transaction that updates many rows but runs slowly. Which approach improves speed while keeping data safe?

A Use a transaction but disable all indexes during the update.

B Remove the transaction and update rows one by one without grouping.

C Break the transaction into smaller transactions for batches of rows.

D Run the transaction with autocommit mode enabled.

💡 Hint

Think about balancing transaction size and locking time.

Smaller transactions reduce lock time and resource use, improving speed while preserving atomicity and consistency.

🔧 Debug

expert

Why does this transaction cause a deadlock?

Two transactions run: Transaction 1: BEGIN; UPDATE accounts SET balance = balance - 50 WHERE id = 1; UPDATE accounts SET balance = balance + 50 WHERE id = 2; COMMIT; Transaction 2: BEGIN; UPDATE accounts SET balance = balance - 30 WHERE id = 2; UPDATE accounts SET balance = balance + 30 WHERE id = 1; COMMIT; Why might these cause a deadlock?

A Because autocommit mode is enabled, causing conflicts.

B Because transactions do not use COMMIT to finish.

C Because the updates are on different tables, so locks conflict.

D Because both transactions try to lock the same rows but in different order, causing a cycle.

💡 Hint

Think about how locks are acquired and the order of operations.

Deadlocks happen when two transactions wait for each other’s locks. Here, each locks one row and waits for the other’s locked row, causing a cycle.