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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Aged Yak Warrior

Canada
596 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
285 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
596 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
596 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  
 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.08 seconds. Powered By: Snitz Forums 2000