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
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
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.