CODEWITHSUNDEEP
sql-serverdeadlocknolock
JohnG
JohnG

Reputation: 272

Is the With(NoLock) hint dangerous in my case?

I've read and used with(nolock) hints many times before but I have a question for a specific case.

In my case I have one set of code that refers and updates one database.
This code was meant to run in a single threaded fashion. A few months ago, they decided to make it multi-threaded without changing the code. The way they went about it, is to make each different "code processor" manage a different group of stores.

For example, processor1 takes care of stores 1 to 20, processor2 takes care of stores 21 to 40 and so on.

Everything seemed fine until we started getting deadlocks. The deadlocks are always on pages... if the locks were only on rows we would never get deadlocks since the data from one processor never conflicts with the data from another processor. Based on that theory, I decided to put With(Nolock) hints on every select that doesn't need to lock (hasn't been put in prod yet)... which made my deadlocks disappear in my Lab.

Everything seemed fine and dandy until my colleague came up to me with THIS article which scared the crap out of me... then I read THAT...

Is there a danger to use the With(Nolock) hint in my case... where data will never conflict between processors?

Upvotes: 2

Views: 784

Answers (3)

sam
sam

Reputation: 1304

It looks like your select query is the cause of dead locks. This is what might be happening. You might be selecting and updating records later on. So when updating the records more than 5000 sql server uses lock escalation and locks the complete table instead of locking the rows.

If your procedure is like -

select .......
--some coding here----
update statement

If another thread uses a select statement just before your update statement your update statement will be blocked because of shared locks and your update statement will waiting for the shared lock to be released and at the same time another process issues update statement. second process update statement will be blocked because of shared lock applied by you. Hence deadlock will happen as both the threads will be waiting for each other.

The solution -

  1. Use UPDLOCK hint - This will allow shared locks but not update locks. Convert your select statement to -> select * from mytable with (UPDLOCK,ROWLOCK) RowLOCK hint will keep locking at row level and not at page or table level. This will reduce deadlocking

  2. Use snapshot Isolation

A word of caution is do not use with (NOLOCK) in select statement because this will lead in dirty reads that is uncommitted data which may be wrong.

Upvotes: 1

Robert-Ryan.
Robert-Ryan.

Reputation: 61

Retract your nolock hints and put your database into snapshot isolation level.

READ_COMMITTED_SNAPSHOT database option ON

See https://technet.microsoft.com/en-us/library/ms191242(v=sql.105).aspx for more information. However, be warned your disk that holds your tempDB experiences higher I/O in snapshot isolation.

Upvotes: 2

onupdatecascade
onupdatecascade

Reputation: 3366

Yes, there could well be issues if you are collecting data that has to be accurate - you may grab "dirty" / incorrect data and then persist it. Have you looked at whether indexing can correct the deadlock problem instead? Often a deadlock scenario can be solved by controlling the access pattern to the data pages, which is controlled by the available indexes.

What I like to do is look at the deadlock graph to see where the conflict is, then look at the order of operations in the code, and which indexes are being used to access/modify the data, to see if I can adjust to remove the deadlock risk.

Upvotes: 1

Related Questions