Isolation Levels: Deep dive into Read Uncommitted, Read Committed, Repeatable Read, Serializable

Locking Mechanisms: Row-level locks, table-level locks, optimistic locking, pessimistic locking

Deadlock Detection: Wait-for graphs, deadlock prevention strategies, timeout settings

Concurrency Control: MVCC (Multi-Version Concurrency Control), timestamp ordering, optimistic concurrency

Distributed Transactions: Two-phase commit (2PC), saga pattern, eventual consistency

Transaction Log: Write-ahead log (WAL), checkpoint, recovery, log shipping

Performance Tuning: Transaction duration optimization, lock contention reduction, batch processing

Error Handling: TRY-CATCH blocks, exception handling, retry logic, circuit breakers

Write a basic transaction with BEGIN, UPDATE, COMMIT for money transfer

Implement error handling with TRY-CATCH (SQL Server) or EXCEPTION block (PostgreSQL)

Create savepoint pattern for partial rollback (order placement with multiple items)

Implement deadlock retry logic (catch deadlock error, wait random time, retry)

Design transaction with lock ordering to prevent deadlocks (always lock in same order)

Write transaction with balance validation (check balance ≥ amount before debit)

Implement optimistic locking (check version before update, retry if changed)

Create audit logging within transaction (log all operations, successful and failed)

Design distributed transaction with two-phase commit (PREPARE, COMMIT PREPARED)

Optimize transaction performance (keep duration < 1 second, minimize locks)

Explain ACID properties with real-world examples (atomicity, consistency, isolation, durability)

What is a deadlock? How does it occur? Prevention strategies?

Compare isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable)

When would you use SAVEPOINT? Provide example scenario

How do you handle errors in transactions? (TRY-CATCH, EXCEPTION block)

What is optimistic locking vs pessimistic locking? When to use each?

Explain two-phase commit (2PC). What are the trade-offs?

How do you prevent race conditions in concurrent transactions?

What is the difference between COMMIT and ROLLBACK?

How do you optimize transaction performance? (duration, locks, batching)

PostgreSQL Transaction Documentation: https://www.postgresql.org/docs/current/tutorial-transactions.html

MySQL Transaction Documentation: https://dev.mysql.com/doc/refman/8.0/en/commit.html

SQL Server Transaction Documentation: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql

ACID Properties Explained: https://en.wikipedia.org/wiki/ACID

Isolation Levels Guide: https://www.postgresql.org/docs/current/transaction-iso.html

Deadlock Detection and Prevention: https://www.postgresql.org/docs/current/explicit-locking.html

Two-Phase Commit Protocol: https://en.wikipedia.org/wiki/Two-phase_commit_protocol

Saga Pattern for Distributed Transactions: https://microservices.io/patterns/data/saga.html

MVCC (Multi-Version Concurrency Control): https://www.postgresql.org/docs/current/mvcc.html

Transaction Log and Recovery: https://www.postgresql.org/docs/current/wal-intro.html