Increasing deadlocks with NoLock
- by Dave Ballantyne
One on my personnel pet issues is with inappropriate use of the NOLOCK hint (and read uncommitted) . Dont get me wrong, I have used it in exceptional circumstances , but as a general statement it is a bad thing. Mostly , when NOLOCK, is used the discussion is around a single statement, “it runs faster with nolock for XYZ reason”, however ,IMO, this is quite a shorted sighted view. What about the Transaction ? What about other concurrent users ? What is good for one statement in isolation , does not mean that it is good for the system as a whole. I have seen on a number of occasions deadlocks happen, when tasks that would of(and should of) be blocked continue to execute, only for a deadlock to occur at a later data writing (INSERT,UPDATE,DELETE) statement. Writers will block writers regardless of isolation level. By Way of (fairly contrived ) example , lets generate some dummy tables and populate with some data drop table a go drop table b go Create Table a ( col1 integer ) go insert into a values(1) insert into a values(2) go Create Table b ( col1 integer ) go insert into b values(1) insert into b values(2) go Now make two connections. In connection one execute set transaction isolation level read committed
BEGIN TRAN
Select * from a
Select * from b
delete from a
In connection two execute
set transaction isolation level read committed
BEGIN TRAN
Select * from a
Select * from b
delete from b
Right now the ‘select from a’ in connection two is being blocked by the ‘delete from a’ in connection one. This is ,IMO, quite a healthy and natural thing to be happening , some see this as a ‘slow down’, a drop in performance. So, lets reach for our ‘NOLOCK’ magic pill.
Cancel the blocked query and ROLLBACK both transactions, then in connection one execute
set transaction isolation level read uncommitted
BEGIN TRAN
Select * from a
Select * from b
delete from b
and then in connection two execute
set transaction isolation level read uncommitted
BEGIN TRAN
Select * from a
Select * from b
delete from a
We have now solved out performance problem , no more blocking. Lets finish the work required by the transaction, in connection one , execute
delete from a
Oh, ‘ performance problem’ again , its now being blocked. Still, lets complete the work in connection two….
delete from b
DEADLOCK!!
It is important to be clear about the role of the select statements. They do not participate within the deadlock, but are preventing code executing that would of. Additionally, without the select readers to block, a deadlock would occur on the deletes with READ COMMITTED. Naturally, other isolation levels will exhibit different behaviour as to where and when they will and wont block, and I would encourage you to read BOL and satisfy yourself that you really do NEED to NOLOCK.
