CODEWITHSUNDEEP
sql-serversql-server-2008-r2
lledr
lledr

Reputation: 552

Inconsistent results when subquery is used

Given the following running transaction:

begin tran t
select 1 from T(updlock) where id = 'id1'
waitfor delay '00:00:10'
update T set value = 'new' where id = 'id1'
commit tran t

If within the waitfor delay I launch the following request:

select value from T(updlock) where id = 'id1'

This request blocks until transaction t completes, and then returns the result new as expected.

But if instead I launch the following request:

select value from T where id in (select id from T(updlock) where id = 'id1')

This request is also blocked by t, but then the result is old.

Why is that?

Annex: execution plans

Upvotes: 2

Views: 244

Answers (1)

Martin Smith
Martin Smith

Reputation: 452988

This is entirely possible. I'm not sure why you think it wouldn't be. S locks are not blocked by U locks

SELECT T1.value
FROM   T T1
WHERE  T1.id IN (SELECT T2.id
                 FROM   T(updlock) T2
                 WHERE  T2.id = 'id1')

has an implied predicate though that is not relevant to the behaviour you see. The execution plan is the same as below (with the seek on 'id1' copied to the T1 branch and an uncorrelated sub query)

SELECT T1.value
FROM   T T1
WHERE  T1.id = 'id1'
       AND EXISTS (SELECT *
                   FROM   T(updlock) T2
                   WHERE  T2.id = 'id1')

enter image description here

Your SELECT query in the first window takes an UPDLOCK on the row and holds it for 10 seconds - however this is compatible with the S lock required by the seek on T1 in window 2 so that seek emits a row with the value column to the nested loops operator. Then the nested loops requests a row from its second input (the seek on T2). This has the UPDLOCK hint so is blocked.

Eventually the first window converts its UPDLOCK to an X lock, releases its lock and the seek on T2 is unblocked. It then emits a row (with no columns so completely unaffected by the fact that value has changed). This means that the semi join condition (EXISTS predicate) is met and the original row is passed along to the root iterator and output.

Upvotes: 1

Related Questions