CODEWITHSUNDEEP
sqlsql-serverdatabaselockingbiztalk
user3482471
user3482471

Reputation: 227

SQL database locking difference between SELECT and UPDATE

I am re-writing an old stored procedure which is called by BizTalk. Now this has the potential to have 50-60 messages pushed through at once. I occasionally have an issue with database locking when they are all trying to update at once. I can only make changes in SQL (not BizTalk) and I am trying to find the best way to run my SP. With this in mind what i have done is to make the majority of the statement to determine if an UPDATE is needed by using a SELECT statement.

What my question is - What is the difference regarding locking between an UPDATE statement and a SELECT with a NOLOCK against it?

I hope this makes sense - Thank you.

Upvotes: 1

Views: 852

Answers (2)

PSK
PSK

Reputation: 17943

You use nolock when you want to read uncommitted data and want to avoid taking any shared lock on the data so that other transactions can take exclusive lock for updating/deleting.

You should not use nolock with update statement, it is really a bad idea, MS says that nolock are ignored for the target of update/insert statement.

Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them. Source

Regarding your locking problem during multiple updates happening at the same time. This normally happens when you read data with the intention to update it later by just putting a shared lock, the following UPDATE statement can’t acquire the necessary Update Locks, because they are already blocked by the Shared Locks acquired in the different session causing the deadlock.

To resolve this you can select the records using UPDLOCK like following

DECLARE @IdToUpdate INT
SELECT @IdToUpdate =ID FROM [Your_Table] WITH (UPDLOCK) WHERE A=B

UPDATE [Your_Table]
SET X=Y
WHERE ID=@IdToUpdate

This will take the necessary Update lock on the record in advance and will stop other sessions to acquire any lock (shared/exclusive) on the record and will prevent from any deadlocks.

Upvotes: 3

Moinul Islam
Moinul Islam

Reputation: 469

NOLOCK: Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. NOLOCK is equivalent to READUNCOMMITTED.

Thus, while using NOLOCK you get all rows back but there are chances to read Uncommitted (Dirty) data. And while using READPAST you get only Committed Data so there are chances you won’t get those records that are currently being processed and not committed.

For your better understanding please go through below link.

https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/ https://www.mssqltips.com/sqlservertip/4468/compare-sql-server-nolock-and-readpast-table-hints/ https://social.technet.microsoft.com/wiki/contents/articles/19112.understanding-nolock-query-hint.aspx

Upvotes: 1

Related Questions