Troubleshooting problems with locking in SQL Server 2014 (Part 1)
If you have problems with SQL Server 2014 locking, review this troubleshooting checklist to find
potential solutions.
1. Install the latest SQL Server 2014 service pack.
Because many locking bugs were fixed in SQL Server 2014 service packs, you should
install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2014 service pack was
service pack 1. You can download the SQL Server 2014 service pack 1 at here:
https://www.microsoft.com/en-us/download/details.aspx?id=46694
2. Keep transactions as short as possible.
This can be used to reduce locking, because problems with locking typically occurs when several
long-running transactions execute concurrently in the same database. The shorter the transaction,
the shorter the exclusive or update locks are held. For example, you can break down long
transactions into groups of smaller transactions.
3. Avoid writing transactions that wait for user input.
Application without user interaction in transactions is much faster than those, which wait
for user input. The longer the transaction, the longer the locks are held and the higher
probability of deadlock.
4. If you need to use cursor, try to use read only cursor.
Using read only cursors, can reduce locking contention in comparison with updatable cursors
and should be used whenever possible.
5. To understand the deadlock reason, turn on trace flag 1222.
You can turn on this trace flag by using the DBCC TRACEON (1222, -1) command or by adding -T1222
as a SQL Server 2014 startup parameter. The trace flag 1222 is a much improved version of the
trace flag 1204. So, you should use the trace flag 1222 instead of the trace flag 1204 if you
work with SQL Server 2014.
6. If you need to use updatable cursor, try to use the OPTIMISTIC cursor option.
The OPTIMISTIC cursor option specifies that positioned updates or deletes through the
cursor can be made only if the row has not been updated since it was read into the cursor.
When the OPTIMISTIC cursor option is used, SQL Server does not lock rows as they are read
into the cursor. So, if you need to use updatable cursor, try to use the OPTIMISTIC cursor
option whenever possible.
7. Write your Data Manipulation Language (DML) code so, that your SELECT, UPDATE and
DELETE statements will use indexes to access data.
Using indexes reduce the amount of time the SELECT, UPDATE and DELETE statements will be
executed and reduce the time the data will be locked.
8. If your application uses the same query over and over on the same table, consider
creating a covering index including columns from this query.
A covering index is an index, which includes all of the columns referenced in the query. So,
creating covering index can improve performance because all the data for the query is contained
within the index itself and only the index pages, not the data pages, will be used to retrieve
the data. Using covering indexes can reduce the amount of time the queries will be executed and
reduce the time the data will be locked.
9. Deadlocks may occur when you run multiple applications together with Multiple Active
Result Sets (MARS) enabled in SQL Server 2014.
This is SQL Server 2014 bug. This bug was first fixed in Cumulative Update package 1 for
SQL Server 2014. You can download the Cumulative Update package 1 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693
10. When you run a complex UPDATE statement together with a NOLOCK hint against a table
in SQL Server 2014, nonclustered index corruption may occur.
In this case, the error 8646 may be logged in the SQL Server error log. This problem occurs
because the NOLOCK hint causes the query to incorrectly read values in the table when the query
reads the same values multiple times. This bug was first fixed in Cumulative Update package 1
for SQL Server 2014. You can download the Cumulative Update package 1 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693
11. Deadlock errors may occur when you run concurrent update and Create Table As Select (CTAS)
query with limited log space in SQL Server 2014 Parallel Data Warehouse (PDW).
This is SQL Server 2014 bug. This bug was first fixed in Cumulative Update package 2 for
SQL Server 2014. You can download the Cumulative Update package 2 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2967546
12. Schema lock occurs on a table with large data when you delete lots of rows from it.
This problem occurs when you delete lots of rows from a very large table with a full-text
index. This bug was first fixed in Cumulative Update package 4 for SQL Server 2014. You can
download the Cumulative Update package 4 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2999197