Does with(updlock) reduce deadlocks in select queries?
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!
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.
链接地址: http://www.djcxy.com/p/32842.html上一篇: 什么是表提示锁的范围？