Why transactions are needed in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use transactions in databases?

Imagine you are transferring money between two bank accounts. Why is it important to use a transaction for this operation?

A To allow multiple users to change the same account balance at the same time without issues.

B To ensure both debit and credit happen together or not at all, preventing money loss.

C To speed up the transfer by skipping checks.

D To save the transfer details only after a day.

💡 Hint

Think about what happens if only one part of the transfer happens and the other does not.

Transactions make sure that all steps in a process happen completely or not at all. This avoids problems like losing money if only one step succeeds.

🧠 Conceptual

intermediate

What problem do transactions solve?

Which problem is solved by using transactions in a multi-user database?

A Preventing partial updates that leave data inconsistent.

B Making queries run faster by skipping locks.

C Allowing users to see uncommitted changes from others immediately.

D Automatically backing up data every minute.

💡 Hint

Think about what happens if a process stops halfway through.

Transactions prevent partial changes that could cause wrong or broken data when multiple users work at the same time.

❓ query_result

advanced

What is the output after a failed transaction?

Consider this SQL sequence:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

What is the balance change after this sequence?

A Account 1 decreased by 100, account 2 increased by 100.

B Account 1 balance decreased by 100, account 2 unchanged.

C Account 1 unchanged, account 2 increased by 100.

D No change in balances for both accounts.

💡 Hint

ROLLBACK cancels all changes in the transaction.

ROLLBACK undoes all changes made in the transaction, so balances remain as before.

❓ query_result

advanced

What happens without transactions in concurrent updates?

Two users update the same row at the same time without transactions:

User1: UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 10;
User2: UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 10;

What problem can occur?

A Quantity decreases correctly by 2.

B Both updates fail with an error.

C Both updates succeed but quantity only decreases by 1 instead of 2.

D Database automatically merges updates safely.

💡 Hint

Think about what happens if both read the same initial quantity before updating.

Without transactions and proper locking, both users might read the same quantity and overwrite each other's update, causing lost updates.

🧠 Conceptual

expert

Why is atomicity important in transactions?

Atomicity means a transaction is all-or-nothing. Why is this property crucial for database reliability?

A It prevents partial changes that could corrupt data if a failure occurs mid-operation.

B It speeds up query execution by skipping checks.

C It allows users to see intermediate results during a transaction.

D It automatically backs up data after each query.

💡 Hint

Consider what happens if a system crashes during a multi-step update.

Atomicity ensures that either all steps succeed or none do, so data never ends up in a broken or partial state.