One Sql Server Deadlock issue: exclusive lock wait on update lock

met the following problem about deadlock, don't know whey this happens, need help, thanks! Here is what happens(the simplified version).

I have the following table and indexes:

    --Create table JobMaster

    CREATE TABLE [dbo].[JobMaster](
        [JobId] uniqueidentifier,
        [CreateTime] datetime NOT NULL DEFAULT (GETUTCDATE()),
        [JobStatus] INT NULL, 
        [JobStatusUpdateTime] DATETIME NULL, 
[DataUploadStatus] int,
        PRIMARY KEY NONCLUSTERED([JobId])     
    )
    GO

    CREATE CLUSTERED INDEX index_jobMaster_createtime ON [dbo].[JobMaster]([CreateTime])
    GO

    CREATE NONCLUSTERED INDEX index_jobMaster_jobStatus ON [dbo].[JobMaster]([JobStatus])
    GO

    CREATE NONCLUSTERED INDEX index_jobMaster_jobStatusUpdateTime ON [dbo].[JobMaster]([JobStatusUpdateTime])
    GO

And we have a dead lock on three sessions: Deadlock: A ->(wait for resource holding by) B -> C -> A.

A and C holding the update lock on JobMaster with index PK (non-cluster), and session B holding the X lock on JobMaster with cluster index

Here is the operation for A/B/C(Read committed isolation level, no special setting): A/B:

UPDATE [JobMaster] SET 
    [DataUploadStatus] = 1,e
    WHERE JobId= 'E6AEE804-6A09-4293-B93C-15E9748E46ED'   

C:

UPDATE [JobMaster] SET [JobStatus] = 1,
 [JobStatusUpdateTime] = GETUTCDATE() 
WHERE JobId= 'E6AEE804-6A09-4293-B93C-15E9748E46ED'    

My question is:

Why does session B wait on the U lock of jobmaster on pk index hold by A, while B is holding the X lock on clustered index? I mean since B has the X lock, which is lock to update the clustered index, seems it has no other locking needs?

What is the sequence of lock request/release on the three sessions? My understanding is, like for session B: update lock on JobMaster with PK and JobMaster with cluster index, then the X lock on JobMaster with Cluster index. Correct me if I am wrong.

Since we don't modify the non-cluster indexes, in any of the queries. Why do they need a U lock on the index? And why C is waiting for A, as C already have the U lock.

链接地址: http://www.djcxy.com/p/95446.html

上一篇: 关键更新死锁

下一篇: 一个Sql Server死锁问题：独占锁等待更新锁