Avoiding Deadlock Transaction Errors by Using ROWLOCK Hint in T-SQL

When updating a single row data in a table, it may take a nasty while if the table is big and you have a condition in a WHERE clause on a column that is not indexed. In this case SQL Server will have to scan all rows in order to figure out which records to update. The UPDATE statement will lock the table and if other statements are executed at that time, you very well are likely to start getting deadlock errors.

To avoid this unfortunate scenario, you can use the ROWLOCK hint on your UPDATE queries. The hint will override locking the whole table and instead will only lock the row that is being updated. However, if the current isolation scope is SNAPSHOT, you will also need to provide other hints.

Taking an example from the previous article, How to Use NOLOCK and ISOLATION LEVEL to Optimize T-SQL Query Performace, let’s modify the Users table to include Preferred column:

CREATE TABLE Users
(
	UserId INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
	FirstName NVARCHAR(50) NOT NULL,
	LastName NVARCHAR(50) NOT NULL,
	Age INT NOT NULL,
	Preferred BIT NOT NULL DEFAULT 0
)

Now let’s create a stored procedure that sets the Preferred column to 1 for a specific Age:

CREATE PROCEDURE SetPreferred
	@Age INT
AS
BEGIN
	UPDATE Preferred = 1
		WHERE Age = @Age
END

Adding an index on Age will certainly speed up things, but the whole table will still be locked while SQL Server looks for matching records, and if the table is big enough you are likely to still get deadlock errors. Instead of locking the whole table, you can hint the server to lock only those rows that match the criteria with ROWLOCK hint. This will allow your code to execute statements on rows that are not affected by the UPDATE query. However, if you are updating a large number of rows as in the example above assuming a billion records, you will probably still get deadlocks and may consider different approaches. To lock only the rows the query is affecting, the stored procedure above can be modified as follows:

CREATE PROCEDURE SetPreferred
	@Age INT
AS
BEGIN
	UPDATE Preferred = 1 WITH(ROWLOCK)
		WHERE Age = @Age
END

However, the above stored procedure will not work unless all the columns in the WHERE clause have an index on them:

CREATE NONCLUSTERED INDEX IX_Users_Age ON Users
(
	Age ASC
)

The situation in which you start getting deadlock errors may also occur not because of how your queries are written, but also how you connect to the database. In some instances the connection will wrap the execution of a query in a transaction and that transaction will not be committed until the connection is closed. If another connection is trying to execute its own query, it may have to wait on the first connection to close just because the latter will only commit when it closes.

This entry was posted in MSSQL and tagged , , , , , , , , , , , , . Bookmark the permalink.

One Response to Avoiding Deadlock Transaction Errors by Using ROWLOCK Hint in T-SQL

Leave a Reply

Your email address will not be published. Required fields are marked *