January 13, 2010 at 5:54 am
Hi Experts!
We are facing serious issue of deadlock on production server, we are colleting deadlock information from error log file but i am not able to interpret the info correctly, can anyone trace the following information & explain how this deadlock occured. specially I want to know how keylock & pagelock works.
resource-list
keylock hobtid=72057594774880256 dbid=10 objectname=CLAS.dbo.OWNERSHIP indexname=OWNERSHIP_PK_1 id=lockec92d00 mode=X associatedObjectId=72057594774880256
owner-list
owner id=processe613ac8 mode=X
waiter-list
waiter id=processe609438 mode=U requestType=wait
pagelock fileid=1 pageid=250955 dbid=10 objectname=CLAS.dbo.FACILITY_GUARANTOR id=lockf17ac80 mode=SIX associatedObjectId=72057594043498496
owner-list
owner id=processe609438 mode=SIX
waiter id=processe613ac8 mode=U requestType=wait
Thanks
January 13, 2010 at 6:12 am
Can you post the entire deadlock graph please? There's no enough info in just the resource list.
You've got a classic two-object deadlock there. Processe613ac8 has the keylock (a lock on a single row in an index) in exclusive mode and wants a page lock (held by the other process) as an update lock.
Processe609438 owns the page lock (in mode SIX - Shared with Intent Exclusive) and wants the keylock that the other process holds.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2010 at 6:49 am
hi, Thanks for reply i understand your short explaintion of how deadlock occured.
I don't have graph but i have a following detail information, I am interested to know how it puts pagelock or keylock? (or how can we avoid pagelocks)
deadlock-list
deadlock victim=processe609438
process-list
process id=processe609438 taskpriority=0 logused=13800 waitresource=KEY: 10:72057594774880256 (b100ca8ce276) waittime=1375 ownerId=382339076 transactionname=user_transaction lasttranstarted=2010-01-07T16:29:08.423 XDES=0x21cfba370 lockMode=U schedulerid=5 kpid=9260 status=suspended spid=77 sbid=0 ecid=0 priority=0 transcount=3 lastbatchstarted=2010-01-07T16:29:08.427 lastbatchcompleted=2010-01-07T16:29:08.423 clientapp=.Net SqlClient Data Provider hostname=COMRCW07CLAPROD hostpid=2192 loginname=MTBANKD01\CLAAPPL isolationlevel=read committed (2) xactid=382339076 currentdb=10 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=CLAS.dbo.P_delete_pending_request line=335 stmtstart=28792 stmtend=29420 sqlhandle=0x03000a00cdfbdd1bfbee5400e19c00000100000000000000
DELETE carr
FROM request_profile rp with(nolock)
INNER JOIN ownership carr with(rowlock) ON carr.request_profile_id = rp.request_profile_id
WHERE rp.request_id = @request_id
AND rp.profile_id IN (SELECT profile_id
FROM @Delete_Result
WHERE source <> 'erm')
inputbuf
Proc [Database Id = 10 Object Id = 467532749]
process id=processe613ac8 taskpriority=0 logused=58968 waitresource=PAGE: 10:1:250955 waittime=1609 ownerId=382340454 transactionname=T1 lasttranstarted=2010-01-07T16:29:10.093 XDES=0x328635390 lockMode=U schedulerid=6 kpid=8700 status=suspended spid=94 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-01-07T16:29:10.073 lastbatchcompleted=2010-01-07T16:29:10.050 clientapp=.Net SqlClient Data Provider hostname=COMRCW05CLAPROD hostpid=2272 loginname=MTBANKD01\CLAAPPL isolationlevel=read committed (2) xactid=382340454 currentdb=10 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056
executionStack
frame procname=CLAS.dbo.p_get_Appro_Data_Process line=467 stmtstart=36248 stmtend=37508 sqlhandle=0x03000a00b18cae1f67ef5400e19c00000100000000000000
DELETE fg
FROM request_profile rp WITH (nolock)
INNER JOIN request_facility_detail rfd WITH (nolock)
ON rfd.request_profile_id = rp.request_profile_id
INNER JOIN facility f WITH (nolock)
ON f.facility_id = rfd.facility_id
INNER JOIN facility_guarantor fg WITH (nolock)
ON fg.facility_id = f.facility_id
INNER JOIN guarantee_type gt WITH(nolock)
ON gt.guarantee_type_id=fg.guarantee_type_id
WHERE rp.request_id = @request_id and gt.guarantee_type_name<>'SBA Guarantee'
---- insert new guarantor
inputbuf
Proc [Database Id = 10 Object Id = 531532977]
resource-list
keylock hobtid=72057594774880256 dbid=10 objectname=CLAS.dbo.OWNERSHIP indexname=OWNERSHIP_PK_1 id=lockec92d00 mode=X associatedObjectId=72057594774880256
owner-list
owner id=processe613ac8 mode=X
waiter-list
waiter id=processe609438 mode=U requestType=wait
pagelock fileid=1 pageid=250955 dbid=10 objectname=CLAS.dbo.FACILITY_GUARANTOR id=lockf17ac80 mode=SIX associatedObjectId=72057594043498496
owner-list
owner id=processe609438 mode=SIX
waiter id=processe613ac8 mode=U requestType=wait
Thanks,
January 13, 2010 at 7:49 am
SQL choses the appropriate locking level and, unless you really know what you're doing, it's better to let it decide.
Can you post the full script of these two stored procs?
CLAS.dbo.P_delete_pending_request
CLAS.dbo.p_get_Appro_Data_Process
Given the size, I suggest save the scripts into text files, zip them and attach the zip file
By the way, you have Nolocks in your deletes. Data modifications have to lock, hence the nolock is ignored. Besides, it's not a good thing to use everywhere. Do you know what it really means? See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Can you also post the structure and all indexes on these tables?
CLAS.dbo.OWNERSHIP
CLAS.dbo.FACILITY_GUARANTOR
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2010 at 9:04 am
Hi,
Check the attachement..
Thank you
January 13, 2010 at 9:16 am
Will take a look, but may only be this weekend. Deadlock are a fair bit of work
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2010 at 4:57 am
Thanks for taking care of this..
January 15, 2010 at 8:43 pm
Not familiar enough with the system but my first blush guess would simply be that the transactions are way too long. The way to get around this is to do all of the necessary selects outside of the transaction to determine the PK's of the tables that you want to delete from... then do the deletes in a transaction with nearly super simple deletes that reference the PK's of each table only... no SELECTsin the transaction and the only possible join might be on a control temp table that contains the names of the tables and the PK's to be deleted. Will run very fast and clean after that. Unfortunately for your team, that's a major rewrite. The underlying logic won't change but it'll still need a major rewrite.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2010 at 4:42 am
Jeff Moden (1/15/2010)
Not familiar enough with the system but my first blush guess would simply be that the transactions are way too long.
At a first glance, I'd agree with you. The transaction in one is around 800 lines long and in the other proc almost 400.
xparmanand: Do you absolutely need all of those inserts, updates, deletes to be a single, atomic operation (all committed or none committed)? If not, consider splitting that transaction up. The problem here is that locks taken up the inserts/updates/deletes are held until the end of the transaction. So you've got a lot of locks held for a very long time.
Another thing that might help is a general optimisation of the entire procedure. Check that the indexes are the most efficient possible, check that the queries are written efficiently.
One last suggestion. Switching to snapshot isolation for the duration of the proc might help, but I won't bet on it. Plus you'll need to make sure that TempDB is laid out optimally.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2010 at 8:20 pm
xparmanand,
Perhaps this one can be a useful read
http://strictlysql.blogspot.com/2009/07/impact-of-indexes-on-locking.html
Forgive me if you are already aware of it.
Regards,
Raj
January 18, 2010 at 3:16 am
Thanks all of you
I will look into the points what you mentioned to minimise the locking
Thanks,
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply