Top 10 SQL Server Concurrency and Locking Interview Questions

SQL THE ONE

There are total 51 questions are answered under SQL Server Concurrency and Locking category in the book “SQL THE ONE”. In this chapter we can see questions and answers on Deadlocks, Transactions, Locking and Isolation Levels. Below are the Top 10 SQL Server Concurrency and Locking Interview Questions:

1. Can you be able to explain how deadlock detection algorithm works in SQL Server?

2. Let’s say we have enabled a trace flag to log deadlock information in SQL Server error log. What kind of information we can get from the log?

3. I have been using SQL Server backup statement inside an explicit transaction. If I rollback the transaction what will happen to my backup?

4. What are the different types of lock modes in SQL Server?

5. My requirement is to disable lock escalation only for a specific table. Is it possible?

6. Does a WITH NOLOCK issues any lock?

7. Can you demonstrate “Locking,” “Blocking” and “Deadlock” with an example?

8. What is the difference between pessimistic and optimistic locking?

9. What is your experience in handling blocking alerts in your current working environment?

10. There is an application connecting to my database and the application is using an ISOLATION LEVEL set in application side. How can we know which isolation level the application is using?

Summary:

These Top 10 SQL Server Concurrency and Locking Interview Questions are the sample questions randomly collected from the book “SQL THE ONE”.

Please share your feedback and comments on below comments section or reach me on sqltheone@gmail.com. We wish you all the best for the next interview.

Here you can find more Details about the book “SQL THE ONE”.