Which isolation level should I use?

READ COMMITTED is the default in most databases — prevents dirty reads, allows non-repeatable reads and phantom reads. REPEATABLE READ prevents non-repeatable reads. SERIALIZABLE prevents all anomalies but has the lowest concurrency. Use READ COMMITTED for most cases; SERIALIZABLE for critical consistency (financial transfers).

Two transactions each hold a lock the other needs, causing both to wait forever. The database detects this and aborts one (ROLLBACK). Prevent deadlocks by acquiring locks in a consistent order, keeping transactions short, and using appropriate isolation levels. Handle deadlocks by retrying the aborted transaction.

TASK

Use database transactions correctly

Transactions group operations into atomic units — all succeed or all fail. Learn ACID properties and isolation levels.

1

A transaction is an atomic unit of work: BEGIN, operations, COMMIT or ROLLBACK. ACID: Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes). Isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — higher isolation = more correctness but less concurrency.
2

SQL Formatter →
Format transaction statements (BEGIN, COMMIT, ROLLBACK, SAVEPOINT). Clear formatting helps verify that every BEGIN has a matching COMMIT or ROLLBACK. Missing commits can cause locks to be held indefinitely.

Frequently asked questions

Which isolation level should I use?: READ COMMITTED is the default in most databases — prevents dirty reads, allows non-repeatable reads and phantom reads. REPEATABLE READ prevents non-repeatable reads. SERIALIZABLE prevents all anomalies but has the lowest concurrency. Use READ COMMITTED for most cases; SERIALIZABLE for critical consistency (financial transfers).
What is a deadlock?: Two transactions each hold a lock the other needs, causing both to wait forever. The database detects this and aborts one (ROLLBACK). Prevent deadlocks by acquiring locks in a consistent order, keeping transactions short, and using appropriate isolation levels. Handle deadlocks by retrying the aborted transaction.