2

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?

Community
  • 1
  • 1
JohnG
  • 272
  • 2
  • 14
  • Investigate snapshot isolation. It takes away lots of blocking and is strictly more consistent than read committed for read-only access. – usr Dec 15 '15 at 19:53

3 Answers3

2

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.

Robert-Ryan.
  • 61
  • 5
1

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.

onupdatecascade
  • 3,336
  • 22
  • 35
  • My theory is that Reading dirty data in this case shouldn't be a problem since no processor touches data from another processor. What scares me from the first article is "Missing rows", "Reading rows twice" and "Reading multiple versions of the same row". It's the same code that runs multiple times at the same time. so the same indexes will be affected. Due to this, I'm getting many deadlocks. mostly one X lock with up to 3 S Locks. usually the S locks are the victims. – JohnG Dec 15 '15 at 19:53
  • Missing rows, reading them twice and reading multiple versions are all examples of dirty data – onupdatecascade Dec 15 '15 at 20:46
  • Also +1 to the suggestion to use RCSI. Test first, but it's practically always a win. – onupdatecascade Dec 15 '15 at 20:51
  • After reading, RCSI won't seem to pass here, since we need to modify DBs on servers that belong to customers. I think I have to go back to the drawing board and analyze the indexes used in the deadlocks. – JohnG Dec 15 '15 at 21:05
1

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.

sam
  • 1,242
  • 3
  • 12
  • 31
  • I will be sitting with the developers to understand the sequence of events and transactions. In my mind the With(nolock) was a viable solution until this thread. I haven't tried the UPDLOCK but when I tried the ROWLOCK hint, I still saw some page locking happening, that's why I didn't pursue. Ideally, the ROWLOCK hint would solve all my problems but It's not with certainty that the Row Locking happens. – JohnG Dec 17 '15 at 20:00