Transactions & Concurrency

1. Explain the ACID properties of a database transaction.

ACID stands for Atomicity, Consistency, Isolation, and Durability. Together they make database transactions reliable.

Atomicity — all or nothing: A transaction is a group of steps.

Either every step succeeds, or none do.

If the system crashes mid-way, everything rolls back.

Example: transferring money from A to B. Both the debit from A and the credit to B must happen together. If the credit fails, the debit is undone.

Consistency — always valid: The database must move from one valid state to another.

Rules like constraints and keys are always respected.

A transaction can’t leave the data half-broken.

Isolation — no interference: Transactions running at the same time don’t mess with each other.

One transaction’s unfinished changes aren’t visible to others.

Durability — permanent: Once a transaction is committed, the data is saved forever.

Even a power failure won’t lose it.

Key takeaway: ACID is why banks can trust their databases. Atomicity and Isolation handle crashes and concurrency; Consistency keeps data valid; Durability ensures nothing is lost.

2. What is a Deadlock, and how can it be prevented?

A deadlock happens when two transactions wait on each other. Each holds a lock the other needs, so neither can finish.

The classic example: Transaction 1 locks Table A and wants Table B.

Transaction 2 locks Table B and wants Table A.

Each waits for the other to release. Neither can continue. Stuck forever.

How the database handles it: The database detects the deadlock and picks one transaction to cancel (the victim).

That frees the locks, and the other transaction finishes.

The cancelled transaction is rolled back, and the app should retry it.

Prevention tips:

1. Access tables in the same order everywhere: Always lock A then B. Then two transactions can never hold opposite locks.

2. Keep transactions short: The less time a transaction holds locks, the smaller the window for a deadlock.

3. Commit quickly: Don’t pause for user input inside a transaction.

Key takeaway: Deadlocks are a lock-waiting cycle. Prevent them with consistent access order and short transactions, and write app retry logic for the rare cases that slip through.

3. What is the difference between Optimistic and Pessimistic locking?

Pessimistic locking assumes conflicts will happen and locks resources in advance. Optimistic locking assumes no conflict and checks only at the end.

Pessimistic locking: Before reading or writing, the transaction locks the row.

Others are blocked until the lock is released.

This guarantees no conflict, but reduces concurrency.

Used when conflicts are frequent or expensive.

Optimistic locking: Nobody locks anything. Everyone reads and works freely.

At the end, before committing, the transaction checks whether the data changed since it was read.

If it changed, the update is rejected and the app retries.

Used when conflicts are rare.

Example — optimistic version check:

UPDATE flights
SET seats = seats - 1, version = version + 1
WHERE flight_id = 101 AND version = 5;

If another user already changed the version, the update affects zero rows and the app knows to retry.

Key differences table:

	Pessimistic	Optimistic
Locks before working	Yes	No
Blocks other users	Yes	No
Checks at the end	No	Yes
Best when	Conflicts frequent	Conflicts rare
Concurrency	Lower	Higher

Key takeaway: Use pessimistic locking for critical systems with frequent clashes. Use optimistic locking for web apps where conflicts are rare, so users aren’t blocked.

4. What is a Transaction in SQL?

A transaction is a sequence of operations treated as one single unit. Either all of them succeed, or none do.

The idea: Some operations must happen together or not at all.

Example — a money transfer: Debit 5000 from Account A.

Credit 5000 to Account B.

If the credit fails, the debit must not stay.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
COMMIT;

If anything fails before COMMIT, a ROLLBACK undoes it all.

Why it matters: This is Atomicity — one of the ACID properties.

A partial transfer would make the accounts inconsistent.

Key takeaway: A transaction groups statements into one all-or-nothing unit. Use COMMIT to save, ROLLBACK to undo.

5. What is the difference between OLTP and OLAP?

OLTP handles quick, daily transactions. OLAP handles complex analysis over large historical data.

OLTP: Online Transaction Processing.

Banking, shopping, booking.

Small, fast, frequent writes.

Consistency is critical.

OLAP: Online Analytical Processing.

Sales reports, trends, forecasting.

Large, slow, read-heavy queries.

Analyzes accumulated history.

Key differences table:

	OLTP	OLAP
Purpose	Daily operations	Analysis
Queries	Many small writes	Few large reads
Data	Current	Historical
Example	Insert order	Yearly sales report

Key takeaway: OLTP runs the business; OLAP understands it. Different workloads, usually on separate databases.

6. What is a Deadlock and how to prevent it?

A deadlock happens when two transactions each hold a lock the other needs. Both wait forever.

The classic scenario: Transaction 1 locks Table A, wants Table B.

Transaction 2 locks Table B, wants Table A.

Each waits for the other to release. Neither can move.

How databases handle it: The database detects the deadlock and cancels one transaction (the victim).

It rolls back and the other transaction proceeds.

The app should retry the cancelled one.

Prevention tips:

• Access tables in the same order everywhere.
• Keep transactions short.
• Commit quickly.

Key takeaway: Deadlocks are lock-waiting cycles. Prevent them with consistent access order and short transactions, plus retry logic in the app.

7. What is a Database Transaction Isolation Level?

An isolation level controls how one transaction sees changes made by others running at the same time.

The problem: Two transactions running together can interfere.

The isolation level decides how much they see of each other’s unfinished work.

The four standard levels (from weakest to strongest):

1. Read Uncommitted: Transactions can see each other’s uncommitted changes.

Fastest, most problems.

2. Read Committed: Only committed changes are visible.

Prevents “dirty reads”.

3. Repeatable Read: Once a row is read, it stays the same during the transaction.

4. Serializable: Transactions run as if one after another.

Safest, slowest.

Key differences table:

Level	Sees uncommitted data?	Prevents dirty reads?	Speed
Read Uncommitted	Yes	No	Fastest
Read Committed	No	Yes	Fast
Repeatable Read	No	Yes	Slower
Serializable	No	Yes	Slowest

Key takeaway: Higher isolation = safer but slower. Lower isolation = faster but riskier. Pick based on how much concurrency your app can tolerate.