Deadlock - how can 2 processes have an x lock

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

Transact-SQL (2008)

Deadlock - how can 2 processes have an x lock

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2013-08-06 : 16:20:41

I have a deadlock. And when I lokked into it, the error log, said that on the same resource, 2 processes owned an X lock. I thought that was impossible. How can 2 processes have an "exclusive" lock on the same resource?

Here is the errorlog showing 2 processes have an exclusive lock on the same object and are both waiting for a U lock on the same object.

  resource-list
   keylock hobtid=72057608971812864 dbid=5 objectname=Qa.dbo.Docket indexname=ClaimID id=lock2323ba00 mode=X associatedObjectId=72057608971812864
    owner-list
     owner id=process3a601390 mode=X
    waiter-list
     waiter id=process3968c70 mode=U requestType=wait
   keylock hobtid=72057608971812864 dbid=5 objectname=Qa.dbo.Docket indexname=ClaimID id=lock23423900 mode=X associatedObjectId=72057608971812864
    owner-list
     owner id=process3968c70 mode=X
    waiter-list
     waiter id=process3a601390 mode=U requestType=wait

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2013-08-09 : 16:40:28

There is nothing wrong with the log.

Do you still need explanation ?

If you can, just post the completed log.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2013-08-12 : 11:53:47

Originally I didn't understand. But now I believe that the X lock, although was the same table, was different records - I had 2 inserts on the same table at the same time. As, correct me if I am wrong, there can not exist 2 X locks on the same thing (i.e. Record, Page, Table) at the same time.

So, sure if my explanation is incorrect, please let me know, Namman.

I didn't want to show the entire log, but here are 2 other lines:

   process id=process3a601390 taskpriority=0 logused=3160 waitresource=KEY: 5:72057608971812864 (9b01376d9427) waittime=4601 ownerId=80737323 
transactionname=user_transaction lasttranstarted=2013-08-06T15:57:02.003 
XDES=0x10f56280 lockMode=U schedulerid=4 kpid=2464 status=suspended spid=65 sbid=0 ecid=0 priority=0 trancount=2 
lastbatchstarted=2013-08-06T15:57:01.157 lastbatchcompleted=2013-08-06T15:53:34.153 clientapp=Microsoft SQL Server Management Studio - Query hostname=GLOBW019M 
hostpid=41432 loginname=sa isolationlevel=read committed (2) xactid=80737323 currentdb=5 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200

process id=process3968c70 taskpriority=0 logused=2736 waitresource=KEY: 5:72057608971812864 (9a018e5543ba) waittime=1582 ownerId=80737553 
transactionname=user_transaction lasttranstarted=2013-08-06T15:57:05.023 
XDES=0x2e8b8280 lockMode=U schedulerid=3 kpid=3476 status=suspended spid=51 sbid=0 ecid=0 priority=0 trancount=2 
lastbatchstarted=2013-08-06T15:57:04.180 lastbatchcompleted=2013-08-06T15:56:56.370 clientapp=Microsoft SQL Server Management Studio - Query hostname=GLOBW019M 
hostpid=41432 loginname=sa isolationlevel=read committed (2) xactid=80737553 currentdb=5 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2013-08-12 : 11:56:12

Somehow message duplicated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources