Transactions & Acid Properties Interview Questions

The four ACID properties are: Atomicity (transactions are all-or-nothing), Consistency (transactions maintain database integrity), Isolation (concurrent transactions don't interfere with each other), and Durability (committed transactions are permanent).

Atomicity ensures that all operations in a transaction either complete successfully or roll back entirely. It's maintained through transaction logs and rollback mechanisms that undo partial changes if any part of the transaction fails.

The standard isolation levels are: READ UNCOMMITTED (lowest), READ COMMITTED, REPEATABLE READ, and SERIALIZABLE (highest). Each level provides different protection against read phenomena like dirty reads, non-repeatable reads, and phantom reads.

A deadlock occurs when two or more transactions are waiting for each other to release locks. Prevention strategies include consistent access order, minimizing transaction duration, using appropriate isolation levels, and implementing deadlock detection.

Pessimistic concurrency control locks resources when accessed, preventing concurrent modifications. Optimistic concurrency allows multiple users to access data and checks for conflicts at commit time. Each approach has different performance and concurrency implications.

A dirty read occurs when a transaction reads data that hasn't been committed by another transaction. READ COMMITTED and higher isolation levels prevent dirty reads by ensuring transactions only read committed data.

SNAPSHOT isolation provides transaction-consistent views of data using row versioning. It allows readers to see a consistent snapshot of data as it existed at the start of the transaction, without blocking writers.

Durability ensures that committed transactions survive system failures. It's guaranteed through write-ahead logging (WAL), where transaction logs are written to stable storage before changes are considered complete.

A phantom read occurs when a transaction re-executes a query and sees new rows that match the search criteria. SERIALIZABLE isolation level prevents phantom reads by using range locks on the query predicates.

Savepoints mark a point within a transaction that can be rolled back to without affecting the entire transaction. They allow partial rollback of transactions while maintaining atomicity of the overall transaction.

Lock escalation converts many fine-grained locks into fewer coarse-grained locks to reduce system overhead. While it conserves resources, it can reduce concurrency by holding broader locks than necessary.

Distributed transactions use two-phase commit protocol: prepare phase ensures all participants can commit, commit phase finalizes changes. Additional coordination and recovery mechanisms handle network failures and participant unavailability.

Long-running transactions can hold locks for extended periods, reducing concurrency, increasing deadlock probability, and consuming system resources. They can also impact transaction log space and recovery time.

Row versioning maintains multiple versions of data rows, allowing readers to see consistent data without blocking writers. It's used in SNAPSHOT isolation and READ COMMITTED SNAPSHOT, improving concurrency at the cost of additional storage.

SQL Server uses shared (S), exclusive (X), update (U), intent, and schema locks. Each type serves different purposes in controlling concurrent access to resources while maintaining transaction isolation.

Transaction timeouts can be handled using SET LOCK_TIMEOUT, implementing application-level timeouts, monitoring long-running transactions, and implementing retry logic with appropriate error handling.

A non-repeatable read occurs when a transaction reads the same row twice and gets different values due to concurrent updates. REPEATABLE READ and higher isolation levels prevent this by maintaining read locks until transaction completion.

Constraint violations trigger automatic rollback of the current transaction to maintain database consistency. Error handling should catch these exceptions and manage the rollback process appropriately.

Transaction logging records all database modifications in a sequential log file. It's crucial for maintaining ACID properties, enabling rollback operations, and recovering from system failures.

Implement retry logic by catching specific error conditions, using exponential backoff, setting appropriate timeout values, and ensuring idempotency. Consider deadlock victims and transient failures separately.

The transaction coordinator manages the two-phase commit protocol, ensures all participants either commit or roll back, handles recovery from failures, and maintains transaction state information.

SQL Server supports nested transactions through @@TRANCOUNT, but only the outermost transaction is physically committed or rolled back. Inner transactions only affect the transaction count and rollback behavior.

Higher isolation levels provide stronger consistency guarantees but can reduce concurrency and performance. Lower levels offer better concurrency but risk data anomalies. Choose based on application requirements.

Use system views like sys.dm_tran_locks, extended events, SQL Profiler, monitor transaction logs, analyze deadlock graphs, and track lock waits. Implement appropriate alerts and monitoring strategies.

Checkpointing writes dirty buffer pages to disk and records the operation in transaction logs. It reduces recovery time after system failure and manages log space by allowing log truncation.

Use appropriate batch sizes, implement checkpoint logic, consider isolation level impact, manage transaction log growth, and implement error handling with partial commit capability when appropriate.

Break into smaller transactions when possible, use appropriate isolation levels, implement progress monitoring, consider batch processing, and ensure proper error handling and recovery mechanisms.

Use appropriate isolation levels, implement optimistic concurrency when suitable, minimize transaction duration, use proper indexing strategies, and consider row versioning for read-heavy workloads.

Explicit transactions are manually controlled using BEGIN, COMMIT, and ROLLBACK statements. Implicit transactions automatically commit after each statement or are controlled by connection settings. Explicit transactions offer more control but require careful management.