Why SELECT can have SIU lock (SQL Server 2012)?
Today I was looking for a dead end and found a rather strange case (at least it seemed strange to me). I had two parallel operator ( UPDATE
and SELECT
), and they have led to a deadlock scenario. No questions. Below is a DeadLock graph showing my case:
I'm worried about the type of lock it has SELECT
. Why is it SIU
(Share with Intern Update) but not S
(Shared) or IS
(Intent Shared) blocking?
I found a post on the MSDN forum that explains a fairly similar case:
The profiler shows the current cumulative lock of all sessions. After I exported the deadlock event to an xdl file and opened it in a text editor, I found that the process that fetches the data has an S lock and the process that updates the data has an IU lock (and wants an IX lock). And the resource is SIU-locked (S + IU).
To me this sounds like (and it doesn't make any sense):
SELECT has a SIU lock because there is an intention to perform an UPDATE in some other session
Can anyone explain why it SELECT
contains a lock SIU
?
UPDATE: The select statement is autogenerated EF 6.1.2; update is a stored procedure.
source to share
I think it turns out that the DOM is executing under the same transaction. I know you stated that this is not the case, but I really don't see how selects can take write locks.
Attach a profiler and capture all statements and transactions (events TM
). You may find more in the same session and transaction than you expect.
By posting this guess as answers because it will most likely be the solution.
source to share