Transactions & Concurrency

Question 1

What is a transaction in SQL?

Answer 1

A transaction is a sequence of operations performed as a single logical unit of work, using BEGIN, COMMIT, and ROLLBACK.

Question 2

What does COMMIT do in SQL?

Answer 2

COMMIT makes all changes made during the transaction permanent in the database.

Question 3

What does ROLLBACK do in SQL?

Answer 3

ROLLBACK undoes all changes made during the transaction, reverting the database to its previous state.

Question 4

What are savepoints in SQL?

Answer 4

Savepoints allow partial rollbacks within a transaction by marking intermediate points.

Question 5

What are nested transactions?

Answer 5

Nested transactions simulate sub-transactions using savepoints but aren’t fully supported in all DBMSs.

Question 6

What does the ACID acronym stand for?

Answer 6

Atomicity, Consistency, Isolation, Durability — the key properties of a reliable transaction.

Question 7

What is Atomicity in ACID?

Answer 7

Ensures all operations in a transaction succeed or none do, maintaining database integrity.

Question 8

What is Consistency in ACID?

Answer 8

Ensures a transaction transforms the database from one valid state to another, enforcing rules.

Question 9

What is Isolation in ACID?

Answer 9

Ensures concurrent transactions do not interfere with each other’s intermediate states.

Question 10

What is Durability in ACID?

Answer 10

Ensures committed data is permanently stored and survives system failures.

Question 11

What is READ UNCOMMITTED isolation?

Answer 11

Allows reading uncommitted changes, risking dirty reads.

Question 12

What is READ COMMITTED isolation?

Answer 12

Prevents dirty reads but allows non-repeatable reads and phantom reads.

Question 13

What is REPEATABLE READ isolation?

Answer 13

Prevents dirty and non-repeatable reads but may allow phantom reads.

Question 14

What is SERIALIZABLE isolation?

Answer 14

The strictest level — fully isolates transactions as if executed serially, preventing all anomalies.

Question 15

What is a shared lock?

Answer 15

Allows multiple transactions to read a resource but not modify it.

Question 16

What is an exclusive lock?

Answer 16

Prevents any other transaction from reading or writing the locked resource.

Question 17

What causes a deadlock in SQL?

Answer 17

A circular wait condition where two or more transactions are waiting for each other’s resources.

Question 18

What is deadlock prevention?

Answer 18

Techniques like timeout, resource ordering, and wait-die schemes to avoid circular waits.

Question 19

What’s the advantage of using transactions?

Answer 19

They ensure data consistency, integrity, and error recovery.

Question 20

What’s a disadvantage of transactions?

Answer 20

They may increase complexity and cause contention or deadlocks in high-concurrency systems.

Question 21

What is a best practice for transactions?

Answer 21

Keep transactions short, well-scoped, and avoid user interaction during them.

Question 22

What is a common use case for savepoints?

Answer 22

Undoing specific parts of a transaction while preserving others.

Question 23

How do transactions impact system design?

Answer 23

They help ensure consistency but require careful handling in distributed or high-concurrency systems.

Question 24

What are architectural implications of isolation levels?

Answer 24

Higher isolation improves accuracy but reduces concurrency and performance.

Question 25

What is a performance issue with SERIALIZABLE isolation?

Answer 25

It limits parallelism and may cause contention or blocking in high-load systems.

Question 26

What is a way to monitor transaction behavior?

Answer 26

Use logs, slow query analysis, deadlock detection tools, and transaction counters.

Question 27

What is a debugging tip for deadlocks?

Answer 27

Use database logs or SHOW ENGINE INNODB STATUS (MySQL) to identify lock waits.

Question 28

What is a tradeoff between consistency and concurrency?

Answer 28

Stricter consistency often reduces concurrency, while looser models improve performance at risk of anomalies.

Question 29

What’s a gotcha with nested transactions?

Answer 29

ROLLBACK of an inner savepoint doesn’t undo outer operations — full rollback requires top-level control.

Question 30

What’s a real-world example of deadlock?

Answer 30

Two users transferring money between accounts where each locks one account and waits for the other.

Question 31

What is a common interview question about isolation levels?

Answer 31

Explain the difference between READ COMMITTED and REPEATABLE READ with examples.

Question 32

What is an advantage of using savepoints?

Answer 32

They provide finer control over rollbacks within complex transactions.

Question 33

What’s a good practice for avoiding deadlocks?

Answer 33

Access tables and rows in the same order across transactions to prevent circular waits.

Question 34

What is phantom read in SQL?

Answer 34

A row appears in a subsequent query of the same transaction due to another committed insert.

Question 35

What’s the role of locking in concurrency control?

Answer 35

Prevents conflicts and anomalies between simultaneous transactions.