Skip to main content


Showing posts from February, 2014

Lock Escalation and Snapshot Isolation Level

I recently ran into an interesting situation with SQL Server specifically SQL Server 2012 that involves "Lock Escalation" that usually resulted in dead-lock scenarios. My application is responsible for parsing very large binary/text files and inserting data into SQL tables. My original thought was to handle this large dataset insertion in a single transaction so that data consistency would be achieved. The application has two major components. One of the components is a web application that accesses some of the tables that are used in the bulk insertion I mentioned above for basic verification and validation. When I was running some test cases, I noticed that my application became very un-responsive and it would eventually time out when the time-out values were reached. SQL Server provides some system tables that maintains information about the locks it keeps and any suspended threads due to these locks. You can use some of the following queries below to query your maste