This post describes an interesting situation were SQL Server locking might occur on a table that has not been indexed carefully, and how you can resolve this issue.

Essentially the scenario is as follows:

You have a table which has a surrogate incremental ID key as the Primary Key, but also has a natural key (say of type Bigint), this natural key is not currently indexed and is being used heavily when searching this table for records.

So a simple version of that table might look like this:

The applications using this table will search it in the following manner:

Now the problem stems from the fact that this table is being heavily used by an application using Entity Framework as the data access abstraction layer, and the fact that the SARG expression is being conducted on a column that has not been indexed, this means that every time a request is made to that table using the query above, it will have to perform a SQL Server SCAN of the entire table before returning the results, locking each row that it needs to return as part of the result in the process.

This means if we have two instances of the application trying to SCAN the entire table, they will lock each other out as both are trying to access the same locked resources when performing their full table SCANs.

To resolve this issue, ideally you would create a covering index on the NaturalID column, resulting in table SEEKs, which means the EF selects no longer have to SCAN the entire table (while locking the rows it selects out), instead, it will SEEK out these rows and pick them from the index directly, no locks, no problems.

The interesting behaviour comes into play when you add a TOP X to your query, so it would look like this:

In this case, SQL Server will SCAN the table upto the first result, by that point it will terminate to return the results to the requesting application, this again means that although it is performing a table SCAN (which is slow), it is actually not locking anything, because as soon as it sees a result instead of locking the row and continuing to SCAN the table, it will terminate and return the row (due to the TOP 1).

As you increase the value of the TOP value in the SELECT query above, the number of locked rows increases as SQL Server continues to scan more and more of the underlying table.

What is even more interesting is using the UNIQUE directive on the NaturalID column (if table logic dictates), so the table would look like this:

When adding the SQL Server UNIQUE DDL directive on that column, SQL Server query optimiser knows it does not need to go further than finding the first row (through a table SCAN), because the column NaturalID is unique to that table, and any SARG expression on that table using NaturalID in an EQUALS filter will return only 1 row, and so resulting in no locks either.

So what does this tell us:

• Using DDLs, describe your tables and database objects as comprehensively as possible, anything you miss out might reduces the opportunity of the SQL Query Optimiser understanding and speeding up your queries.
• Make sure your table is SARG-able, by creating any missing covering indexes. You could always review the missing indexes DMV to find-out which indexes are being reported as missing, and how much benefit gained from implementing that index.
• The TOP statement terminates as soon as it satisfies its criteria.
• Setting up the correct Transaction Isolation Level will help avoid some DB locking issues, while also allowing application to handle deadlocks rather than DB.

Some of these points are maybe abit obvious, but its interesting to see how the SQL engine behaves in action.