Cannot figure out how to read this deadlock

2012年4月23日 3:26

In one process the DiabloSTG.Diablo.LongLegal table is being switched out to an empty copy (after that a stage copy will switched into it) and from another session DiabloSTG.Diablo.LongLegal is consumed during and update of [tTaxMat].[db255_4842310_Property] table with a join.

From what I can visualize this should be a schema lock blocking or being blocked by a select, but instead its generating a deadlock (attached). Doesn't a deadlock require a lock held from at least two sessions and trying to hold another impossible lock?

Any idea how this concluded as a deadlock

Thank you

<deadlock-list>
 <deadlock victim="process43227708">
  <process-list>
   <process id="process43227708" taskpriority="0" logused="0" waitresource="OBJECT: 8:1037465574:28 " waittime="5839" ownerId="45072025" transactionname="user_transaction" lasttranstarted="2012-04-20T02:40:19.930" XDES="0x2d51498520" lockMode="Sch-M" schedulerid="40" kpid="8948" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-04-20T02:39:33.043" lastbatchcompleted="2012-04-20T02:39:33.037" clientapp="LoadLongLegal_20120420_020004_A4B39425" hostname="FACLSNA01VAPP34" hostpid="5248" loginname="FIRSTAMDATA\FACL-SA-SQL11SRVC" isolationlevel="read committed (2)" xactid="45072025" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="38" stmtstart="2946" stmtend="3078" sqlhandle="0x020000001a974630c05801f5a83f06faa04ca0a8cf7abf20">
ALTER TABLE Diablo.LongLegal SWITCH TO Diablo.LongLegal_SWITCH;     </frame>
    </executionStack>
    <inputbuf>
BEGIN TRY

	IF OBJECT_ID(&apos;Diablo.LongLegal&apos;)IS NOT NULL
		BEGIN

			IF EXISTS(SELECT *
					  FROM sys.indexes
					  WHERE object_id = OBJECT_ID(N&apos;[Diablo].[LongLegalNew]&apos;)
						AND name = N&apos;LongLegal_UNIQ&apos;)
				DROP INDEX LongLegal_UNIQ ON Diablo.LongLegal WITH(ONLINE = OFF);

			CREATE UNIQUE CLUSTERED INDEX LongLegal_UNIQ ON Diablo.LongLegalNew(CntyCd ASC, Edition ASC, PclId ASC, PclSeqNbr ASC)
				WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
				ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DEFAULT]; 

			IF OBJECT_ID(&apos;[Diablo].[LongLegal_SWITCH]&apos;)IS NOT NULL
				DROP TABLE
					 Diablo.LongLegal_SWITCH;
			SELECT TOP 0
				   * INTO
						  Diablo.LongLegal_SWITCH
			FROM Diablo.LongLegal;

			CREATE UNIQUE CLUSTERED INDEX LongLegal_SWITCH_UNIQ ON Diablo.LongLegal_SWITCH(CntyCd, Edition, PclId, PclSeqNbr);

			IF EXISTS(SELECT *
					  FROM sys.indexes
					  WHERE object_id = OBJECT_ID(N&apos;[Diablo].[LongLegal]&apos;)
		    </inputbuf>
   </process>
   <process id="process38631dc8" taskpriority="0" logused="0" waitresource="OBJECT: 8:1037465574:26 " waittime="3247" ownerId="45072182" transactionname="Lookup" lasttranstarted="2012-04-20T02:40:22.520" XDES="0x13f5e51c60" lockMode="Sch-S" schedulerid="29" kpid="9944" status="suspended" spid="137" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2012-04-20T02:40:02.873" lastbatchcompleted="2012-04-20T02:40:02.873" clientapp="LoadProperty_db255_4842310_20120420_023521_DD9720A4" hostname="FACLSNA01VAPP34" hostpid="15344" loginname="FIRSTAMDATA\FACL-SA-SQL11SRVC" isolationlevel="read committed (2)" xactid="45055018" currentdb="14" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="2" sqlhandle="0x02000000abea0110253b88235ccb2fddd7e9353b0d8bd049">
UPDATE dp
SET
    dp.CntyLegalTxt = sl.Legal
FROM [tTaxMat].[db255_4842310_Property] AS dp INNER JOIN DiabloStg.Diablo.LongLegal AS sl
   ON dp.CntyCd = sl.CntyCd
  AND dp.PclId = sl.PclId
  AND dp.PclSeqNbr = sl.PclSeqNbr
  AND sl.Edition = ( SELECT TOP 1
                                vlc.Edition
                         FROM [Diablo].[Control].[vLoadControlDetail] AS vlc
                         WHERE vlc.ProcessType = &apos;MAT&apos;
                           AND vlc.FileType = &apos;PCL&apos;
                           AND vlc.LoadControlId = 16105 )
WHERE dp.CntyLegalTxt != sl.Legal     </frame>
     <frame procname="adhoc" line="102" stmtstart="5504" stmtend="5544" sqlhandle="0x020000001625132dfdb724cd655a1dd7d8cdf637cb3130fa">
EXEC ( @SQL ) ;     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P1 varchar(13),@P2 varchar(6),@P3 varchar(7),@P4 smallint,@P5 smallint,@P6 varchar(5))
/* PARAMETERS:
 * _parDataFolder = ?	eg. &apos;db010_243&apos;
 * _gDiabloDbName = ?	eg. &apos;Diablo&apos;
 * targetSchema = ?		eg. &apos;tTaxMat&apos;
 * _parLoadControlId = ?	eg. 20
 * _parProcessTypeId = ?	eg. 10 (MAT), 20 (PROP)
 * _parCntyCd = ?		eg. &apos;06037&apos;
 */
DECLARE
   @parDataFolder VARCHAR ( 20 ) = @P1;
DECLARE
   @gDiabloDbName VARCHAR ( 20 ) = @P2;
DECLARE
   @targetSchema VARCHAR ( 20 ) = @P3;
DECLARE
   @parLoadControlId INT = @P4;
DECLARE
   @parProcessTypeId TINYINT = @P5;
DECLARE
   @CntyCd CHAR ( 5 ) = @P6
DECLARE
   @SQL VARCHAR ( MAX );

/***************
 * Propagation *
 ***************/
IF @parProcessTypeId = 20
    BEGIN

        SET @SQL =
&apos;IF OBJECT_ID ( &apos;&apos;tempdb..#Editions&apos;&apos; ) IS NOT NULL
    DROP TABLE
         #Editions
CREATE TABLE #Editions (
             CntyCd CHAR ( 5 ) NOT NULL
                               PRIMARY KEY CLUSTERED,
             Edition TINYINT NOT NULL, )

INSERT INTO #Editions (
            CntyCd,    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="28" objid="1037465574" subresource="FULL" dbid="8" objectname="DiabloSTG.Diablo.LongLegal" id="lock319ce0800" mode="Sch-S" associatedObjectId="1037465574">
    <owner-list>
     <owner id="process38631dc8" mode="Sch-S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process43227708" mode="Sch-M" requestType="wait"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="26" objid="1037465574" subresource="FULL" dbid="8" objectname="DiabloSTG.Diablo.LongLegal" id="lock32fc52a00" mode="Sch-M" associatedObjectId="1037465574">
    <owner-list>
     <owner id="process43227708" mode="Sch-M"/>
    </owner-list>
    <waiter-list>
     <waiter id="process38631dc8" mode="Sch-S" requestType="wait"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>

Gokhan Varol

全部回复

2012年4月23日 4:44

答复者

0
```
Sch-M (Schema Modification)
```
Those are very restrictive locks

All queries require and take a Sch-S lock while they're running, to prevent the table from being modified while a query against it is executing.

http://blog.sqlauthority.com/2010/06/21/sql-server-transaction-dml-and%C2%A0schema%C2%A0locks/
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
- 已建议为答案 JRStern 2012年4月23日 6:26
- 取消建议作为答案 Gokhan Varol 2012年4月23日 12:03
- 回复
- 引用
2012年4月23日 5:00

0

The resources are waiting for object

Database id =8

Object id=1037465574

to me the culprit is "ALTER TABLE Diablo.LongLegal SWITCH TO Diablo.LongLegal_SWITCH.The data switch process is blocked by update statement

Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
- 回复
- 引用
2012年4月23日 6:34

0

I guess the question is why it deadlocks instead of just blocking.

I think there's a simple rule - don't even try to run DDL code and DML code on the same objects at the same time.

Josh
- 回复
- 引用
2012年4月23日 12:05

0

how is this causing a deadlock, how to interpret the deadlock from the deadlock graph, why wouldn't one of the session simply wait for the other session to be released? This remained unanswered .

Thank you
Gokhan Varol
- 回复
- 引用
2012年4月23日 12:38

答复者

0

>>>>why wouldn't one of the session simply wait for the other session to be released? This remained unanswered .

It does wait till SQL Server decides to kill one session ( a victim) due to incompatibly of locks
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
- 回复
- 引用
2012年4月23日 13:01

0
still confused. If I have a session
```
BEGIN TRANSACTION
CREATE TABLE test(id int)
```
and a second session after that
```
SELECT * from test
```
I can monitor that the second session will wait indefinetely till first session commits or rolls back.

How is this scenario above any different, why is one of the sessions becoming a deadlock victim instead of waiting. I cannot find explanation to that. Where is what you just mention "It does wait till SQL Server decides to kill one session ( a victim) due to incompatibly of locks " documented about a deadlock?

Thank you

Gokhan Varol
- 回复
- 引用
2012年4月23日 15:03

0

DiabloSTG.Diablo.LongLegal is not partitioned but we are switching into it (instead or sp_rename we are using partition switching)

Gokhan Varol
- 回复
- 引用
2012年4月24日 12:43

0

here you go

Gokhan Varol
- 回复
- 引用
2012年4月24日 14:54

0
Hi,

It looks to me like you are doing a SELECT and an UPDATE on the same table.

If the SELECT is using a non-covering Non-Clustered index it will then need to connect to the clustered index.

Meanwhile the UPDATe is changing the Clustered index and as a consequence will also be updating the non-clustered index.

UPDATE has lock on Clusterered index but SELECT wants it.

SELECT has lock on Non-clustered index but UPDATE wants it.

You could make the non-clustered index a covering index. Or you could put a lock on the clustered index as part of the SELECT batch. This will simply block teh Update until complete.

Or you could use NoLock on the Select.

Seth

http://lqqsql.wordpress.com
- 已编辑 Seth Lynch 2012年4月24日 14:54
- 回复
- 引用
2012年4月24日 16:06

0

there is a select and at the same time partition switch going on against the table. This one has nothing todo with covered indexes or indexes it's about schema locks and why there is a deadlock caused by them instead of a simple wait forever lock.
Gokhan Varol
- 回复
- 引用
2012年4月27日 18:47

0
The sequence that leads to the deadlock is as follows:

1. Transaction T1 acquires a Sch-S lock on the table. Lock partitioning is enabled in your system (because you have at least 16 CPUs), and this lock is taken on partition 28.

2. Transaction T2 executes DDL and needs a Sch-M lock. This type of lock must be acquired on all partitions (see http://msdn.microsoft.com/en-us/library/ms187504.aspx). In this case the lock is successfully acquired on partitions 0, 1, 2, ..., 26 and 27, and then the transaction blocks on the Sch-S held by T1 on partition 28.

3. Transaction T1 tries to acquire a Sch-S lock on partition 26 and runs into a deadlock because the Sch-M is already held there.

Based on the deadlock graph, I can't say why T1 is taking Sch-S locks on two partitions. This can happen for example with parallel execution plans, but I don't know if this is the case here.
- 已建议为答案 Jon Gurgul 2012年5月3日 10:56
- 已标记为答案 Peja TaoModerator 2012年5月4日 1:13
- 回复
- 引用
2012年4月27日 19:21

0

The table is not partitioned at all, there is only single partition on this table (again it's not partitioned)
Gokhan Varol
- 回复
- 引用
2012年4月27日 19:23

0

I'm talking about lock partitioning, not table partitioning. Please take a look at: http://msdn.microsoft.com/en-us/library/ms187504.aspx
- 回复
- 引用
2012年4月27日 19:40

0

aaaahhhhhhhhhh :-)

thank you so much
Gokhan Varol
- 回复
- 引用
2012年4月27日 20:45

0

I'm talking about lock partitioning, not table partitioning. Please take a look at: http://msdn.microsoft.com/en-us/library/ms187504.aspx

The 16 cpu trigger is logical cpus? e.g a 2 socket quad core with hyper threading = 16?

Gokhan Varol can I ask how many cpus you have?
Jon
- 回复
- 引用
2012年4月28日 0:25

0

We have 8 physical 48 core total
Gokhan Varol
- 回复
- 引用
2012年4月28日 2:38

0

1. Transaction T1 acquires a Sch-S lock on the table. Lock partitioning is enabled in your system (because you have at least 16 CPUs), and this lock is taken on partition 28.

"Lock partitioning", well that's new to me, too. Thanks.

But is that an issue here - in fact, is it ever an issue in that it makes things more restrictive than they would be without partitioning? Seems to me the answer has to be, not. That is, if this were executed on a server with just one or two cores, wouldn't the blocking/deadlock issues would be exactly the same?

Or, does it in some way explain OP's original question, why it produces a deadlock instead of a block - would it block instead of deadlock, if executed on a one or two core server?

It is in any case an interesting factor that may show up in reading displays of active locks, and a nice piece of knowledge related to SQL Server efficiency.

Josh
- 回复
- 引用
2012年4月28日 13:43

0

what did you just say?
Gokhan Varol
- 回复
- 引用
2012年4月28日 18:16

版主

1

You might try acquiring an exclusive table lock in the SWITCH script early in the transaction (e.g. SELECT TOP (0)...WITH (HOLDLOCK, TABLOCKX)). This will serialize access to the table before other locks are obtained in the transaction, thus avoiding the deadlock with other sessions.

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
- 回复
- 引用
2012年4月28日 22:13

0
You might try acquiring an exclusive table lock in the SWITCH script early in the transaction (e.g. SELECT TOP (0)...WITH (HOLDLOCK, TABLOCKX)). This will serialize access to the table before other locks are obtained in the transaction, thus avoiding the deadlock with other sessions.

Sounds like an excellent idea! Why are the simple solutions, the hardest to think of?

Josh

ps - or if all else fails, try serializable ...
- 已编辑 JRStern 2012年4月28日 22:13
- 回复
- 引用