SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Deadlock - how can 2 processes have an x lock
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 08/06/2013 :  16:20:41  Show Profile  Reply with Quote

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

USA
272 Posts

Posted - 08/09/2013 :  16:40:28  Show Profile  Reply with Quote
There is nothing wrong with the log.

Do you still need explanation ?

If you can, just post the completed log.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 08/12/2013 :  11:53:47  Show Profile  Reply with Quote
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

Edited by - denis_the_thief on 08/12/2013 12:02:36
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 08/12/2013 :  11:56:12  Show Profile  Reply with Quote
Somehow message duplicated.

Edited by - denis_the_thief on 08/12/2013 11:57:19
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000