Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  SQL Server Administration (2008)  SIU Lock on a page

Author  Topic  TJTodd Starting Member 10 Posts Posted - 2010-09-03 : 15:43:46 Can the following SQL statement cause an SIU lock on a page in the TCaseInfo table? --Query1 DELETE FROM TArchive WHERE CaseNum NOT IN (SELECT casenum FROM TCaseInfo) I'm investigating a deadlock involving a Stored Procedure whose only reference to the table in question is the above statement and the following statement which is executing at the time of the deadlock: --Query2 INSERT INTO Archive_CI SELECT t1.* FROM TCaseInfo t1, TArchive t2 WHERE t1.casenum = t2.DeadCaseNum The deadlock message indicates that the process is holding an SIU lock on one page of the TCaseInfo and requesting an S lock on another page. I would assume that the SIU lock is coming from Query1 and not from an earlier phase of Query2. Does that make sense?