2

We have tables that are written to and read from simultaneously in our SQL Server 2008 DB (normal isolation levels).

One colleague was wondering if the query hint with(updlock) on the select queries against that table would reduce deadlocks, but I am not quite sure what to make of this.

I am thinking that if a normal shared read lock would cause a timeout, then surely an update-lock would cause a deadlock as well in that situation?! Or am I missing something?

Thanks all!

ManOnAMission
  • 1,023
  • 1
  • 12
  • 31
  • 1
    There's an important distinction between a *long block* and a *deadlock*. I can imagine scenarios where adding `with (updlock)` could both cause a deadlock, and prevent a deadlock... there's a few questions that would need to be answered in order to provide you with anything other than speculation, and the easiest way to answer these questions would be to show the Sql commands that are involved in the deadlock, as well as any indexes on these tables. – Michael Fredrickson Mar 06 '13 at 22:48

1 Answers1

0

Update lock is not compatible with Shared lock so basically fewer SELECT statements could run simultaneously under certain circumstances. I believe this would not help your problem.

Have you considered to turn on Read Commited Snapshot (RCSI) database option? This is something you would want to test in your test environment first. It brings some overhead on tempdb for version storing but your database throughput should get higher thanks to optimistic locking of RSCI.

Chris B. Behrens
  • 6,255
  • 8
  • 45
  • 71
Jirka Hubáček
  • 187
  • 1
  • 7