SQL Server Interview Topic 17: Isolation, Locking, and Deadlocks
This topic checks whether you understand what happens when many users use the database at the same time.
You should be able to explain locking and isolation in simple words from a developer point of view.
Q120. What is locking in SQL Server?
Quick interview answer:
Locking is how SQL Server protects data when users read or change it. If one transaction is updating a row, SQL Server may lock it so another transaction does not change it at the same time.
Study in detail: Transactions and Concurrency - This lesson explains multi-user data safety.
Q121. What is blocking?
Quick interview answer:
Blocking happens when one query waits because another transaction is holding a lock. Short blocking is normal, but long blocking can slow the application.
Study in detail: Monitoring and Performance - This article explains performance checks.
Q122. What is a dirty read?
Quick interview answer:
A dirty read happens when a query reads data that another transaction has not committed yet. If that transaction rolls back, the first query has read data that never really became final.
Study in detail: Transactions and Concurrency - This lesson explains transaction behavior.
Q123. What is an isolation level?
Quick interview answer:
An isolation level controls how much one transaction can see changes made by another transaction. Higher isolation can protect data more, but may increase waiting and blocking.
Study in detail: Transactions and Concurrency - This article introduces isolation at a practical level.
Q124. How do you reduce deadlocks?
Quick interview answer:
Keep transactions short, access tables in a consistent order, update only needed rows, and use proper indexes. Deadlocks are not only database issues; application flow can also cause them.
Study in detail: Transactions and Concurrency - This lesson explains deadlock prevention basics.
Do not over-explain isolation levels unless asked. Start with the simple idea: transactions must protect shared data.