January 7, 2013 at 4:32 pm
Hi Guys,
Need urget help Please. I am running SP on SSMS everytime i run that SP getting Error "Msg 1205, Level 13, State 52, MyStoreProcedureName, Line 85
Transaction (Process ID 11) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
I kill spid 11, still giving me problem everytime i run this Store Procedure.Any help would be great appreciate!
January 7, 2013 at 4:36 pm
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
There's no point in killing spid 11, you're just killing yourself (spid 11 is the session that gets that error message)
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 7, 2013 at 4:49 pm
Thanks for your prompt reply.
I ran DBCC TRACEON(1222,-1)
Message:-
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I couldn't see any Graph. Help me out..
Thank You.
January 7, 2013 at 4:57 pm
GilaMonster (1/7/2013)
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
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 7, 2013 at 5:00 pm
Thanks,
I checked Error Log, and they create Lot of new entries. You want me to Paste everything (all 22 rows or any specific one?)
January 7, 2013 at 5:02 pm
The entire deadlock graph. Just one of them if there are multiple.
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 7, 2013 at 5:10 pm
Here it is...
Date,Source,Severity,Message
01/07/2013 15:56:32,spid26s,Unknown,deadlock-list
01/07/2013 15:56:32,spid26s,Unknown,deadlock victim=process253f8c5dc8
01/07/2013 15:56:32,spid26s,Unknown,process-list
01/07/2013 15:56:32,spid26s,Unknown,process id=process253f8c5dc8 taskpriority=0 logused=0 waitresource=PAGE: 7:1:2772944 waittime=986 ownerId=2832471083 transactionname=INSERT lasttranstarted=2013-01-07T15:56:10.547 XDES=0x21d3d0ea70 lockMode=S schedulerid=26 kpid=12768 status=suspended spid=102 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2013-01-07T15:56:10.547 lastbatchcompleted=2013-01-07T15:53:14.657 clientapp=Microsoft SQL Server Management Studio - Query hostname=FHCSQLPROD hostpid=5744 loginname=LOGAN\sqladminsvc isolationlevel=read committed (2) xactid=2832471083 currentdb=7 lockTimeout=4294967295 clientoption1=673187936 clientoption2=390200
01/07/2013 15:56:32,spid26s,Unknown,executionStack
01/07/2013 15:56:32,spid26s,Unknown,frame procname=CM.dbo.MYSTOREPROCEDURE line=85 stmtstart=3034 stmtend=6438 sqlhandle=0x0300070089c03346bcf9fc003fa100000100000000000000
01/07/2013 15:56:32,spid26s,Unknown,insert into @Temp11
01/07/2013 15:56:32,spid26s,Unknown,select
01/07/2013 15:56:32,spid26s,Unknown,PM.MId<c/>
01/07/2013 15:56:32,spid26s,Unknown,PS.PId<c/>
01/07/2013 15:56:32,spid26s,Unknown,PM.LastName<c/>
01/07/2013 15:56:32,spid26s,Unknown,PM.FirstName<c/>
01/07/2013 15:56:32,spid26s,Unknown,PM.HomeAddressId<c/>
01/07/2013 15:56:32,spid26s,Unknown,Max(E.EId) LEnc<c/>
01/07/2013 15:56:32,spid26s,Unknown,MAX(E.DService) LDOS<c/>
01/07/2013 15:56:32,spid26s,Unknown,DATEDIFF(YEAR<c/> PM.DOB<c/>E.Dservice) AS AGE_DOS
01/07/2013 15:56:32,spid26s,Unknown,from tMaster PM
01/07/2013 15:56:32,spid26s,Unknown,inner join tblSub PS on PM.MId = PS.MId
01/07/2013 15:56:32,spid26s,Unknown,and C.VoidedDate is null
01/07/2013 15:56:32,spid26s,Unknown,and C.IsError=0
01/07/2013 15:56:32,spid26s,Unknown,inner Join tDiagnosis D on C.ICDId = D.DiagonisId
01/07/2013 15:56:32,spid26s,Unknown,and D.DCode like '493%'
01/07/2013 15:56:32,spid26s,Unknown,where E.IsQualified=1
01/07/2013 15:56:32,spid26s,Unknown,and E.EStatusId<>7
01/07/2013 15:56:32,spid26s,Unknown,AND PM.IsDeceased = 0
01/07/2013 15:56:32,spid26s,Unknown,and E.DService>=@StartDate
01/07/2013 15:56:32,spid26s,Unknown,and E.PUniqueId not in (select PuniqueId
01/07/2013 15:56:32,spid26s,Unknown,from FEncounters E1
01/07/2013 15:56:32,spid26s,Unknown,where E1.EsId<>7
01/07/2013 15:56:32,spid26s,Unknown,and E1.DService>=@StartDate)
01/07/2013 15:56:32,spid26s,Unknown,ANDPM.[MId] NOT IN (SELECT MId
01/07/2013 15:56:32,spid26s,Unknown,FROM [tblContact]
01/07/2013 15:56:32,spid26s,Unknown,WHERE [Isdeleted] = 0
01/07/2013 15:56:32,spid26s,Unknown,union
01/07/2013 15:56:32,spid26s,Unknown,select MId
01/07/2013 15:56:32,spid26s,Unknown,from tlert
01/07/2013 15:56:32,spid26s,Unknown,where
01/07/2013 15:56:32,spid26s,Unknown,frame procname=adhoc line=1 stmtstart=2 sqlhandle=0x010007006f48251850d18a59110000000000000000000000
01/07/2013 15:56:32,spid26s,Unknown,exec MyStoreProcedure
01/07/2013 15:56:32,spid26s,Unknown,inputbuf
01/07/2013 15:56:32,spid26s,Unknown,exec MyStoreProcedure
01/07/2013 15:56:32,spid26s,Unknown,process id=process53e1b88 taskpriority=0 logused=3328 waitresource=OBJECT: 7:565577053:15 waittime=4054 ownerId=2832588113 transactionname=user_transaction lasttranstarted=2013-01-07T15:56:28.190 XDES=0xdbf9ae780 lockMode=IX schedulerid=16 kpid=8784 status=suspended spid=99 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-01-07T15:56:28.190 lastbatchcompleted=2013-01-07T15:56:28.190 lastattention=2013-01-07T15:14:27.980 clientapp=.Net SqlClient Data Provider hostname=FHCWEB01 hostpid=0 loginname=cmis isolationlevel=read committed (2) xactid=2832588113 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
01/07/2013 15:56:32,spid26s,Unknown,executionStack
01/07/2013 15:56:32,spid26s,Unknown,frame procname=CM.dbo.saveCodedProcTx line=88 stmtstart=4388 stmtend=4976 sqlhandle=0x030007004b78867f14212e0152a000000100000000000000
01/07/2013 15:56:32,spid26s,Unknown,insert into tblCharge (charge<c/> chargeTypeId<c/> codingId<c/> encId<c/> pId<c/>
01/07/2013 15:56:32,spid26s,Unknown,payerId<c/> chargeDate<c/> userId<c/> voidedUserId<c/> voidedDate<c/> isCommitted<c/> procedureId<c/> IcdId) values (
01/07/2013 15:56:32,spid26s,Unknown,@charge<c/> @chargeTypeId<c/> @codingId<c/> @encId<c/> @patientId<c/> @payerId<c/> getdate()<c/> @userid<c/> null<c/> null<c/> 0<c/> @procId<c/> @diagId)
01/07/2013 15:56:32,spid26s,Unknown,frame procname=CM.dbo.usp_AddCoding line=664 stmtstart=38116 stmtend=38450 sqlhandle=0x03000700319581141b5f2d0152a000000100000000000000
01/07/2013 15:56:32,spid26s,Unknown,exec saveCodedProcTx null<c/> @charge<c/> @chargeTypeId<c/> @CodingId<c/> @EncounterId<c/> @patientId<c/> @payerId<c/> @userid<c/> @discountAmount<c/> @discountType<c/> @ProcedureId<c/> @DiagnosisId
01/07/2013 15:56:32,spid26s,Unknown,inputbuf
01/07/2013 15:56:32,spid26s,Unknown,Proc [Database Id = 7 Object Id = 344036657]
01/07/2013 15:56:32,spid26s,Unknown,resource-list
01/07/2013 15:56:32,spid26s,Unknown,pagelock fileid=1 pageid=2772944 dbid=7 objectname=CM.dbo.tblEncounters id=lock3c0f21000 mode=IX associatedObjectId=72057664385122304
01/07/2013 15:56:32,spid26s,Unknown,owner-list
01/07/2013 15:56:32,spid26s,Unknown,owner id=process53e1b88 mode=IX
01/07/2013 15:56:32,spid26s,Unknown,waiter-list
01/07/2013 15:56:32,spid26s,Unknown,waiter id=process253f8c5dc8 mode=S requestType=wait
01/07/2013 15:56:32,spid26s,Unknown,objectlock lockPartition=15 objid=565577053 subresource=FULL dbid=7 objectname=CM.dbo.tblCharge id=lock44d410600 mode=S associatedObjectId=565577053
01/07/2013 15:56:32,spid26s,Unknown,owner-list
01/07/2013 15:56:32,spid26s,Unknown,owner id=process253f8c5dc8 mode=S
01/07/2013 15:56:32,spid26s,Unknown,waiter-list
01/07/2013 15:56:32,spid26s,Unknown,waiter id=process53e1b88 mode=IX requestType=wait
Please let me know if it is work...
January 7, 2013 at 5:26 pm
Can you post the definitions for MyStoreProcedure and CM.dbo.saveCodedProcTx, as well as the indexes on the tables CM.dbo.tblEncounters and CM.dbo.tblCharge?
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 7, 2013 at 5:30 pm
The Store Procedure "MyStoreProcedure" has 19 Pages hard to post here ( I hope you understand). Some time this SP work fine and Some time i am getting Dead Lock Error.....
January 7, 2013 at 5:34 pm
and Indexes on as well as the indexes on the tables CM.dbo.tblEncounters and CM.dbo.tblCharge?
is CM.dbo.tblEncounters = ECounter (Non Clustered Index)
CM.dbo.tblCharge = Enc (Non Clustered Index)
January 7, 2013 at 5:39 pm
rocky_498 (1/7/2013)
The Store Procedure "MyStoreProcedure" has 19 Pages hard to post here ( I hope you understand). Some time this SP work fine and Some time i am getting Dead Lock Error.....
Then I suggest you start by rewriting that procedure and simplifying it (or splitting it up). A several page stored proc is a good indication that there's a design problem.
Once you've simplified and broken the procedure down, if you're still having deadlocks post the new deadlock graph.
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 8, 2013 at 9:24 am
I will do that.
Quick Question. I am running same procedure in different Server and its working fine with no Problem. I ran this SP 10 time no deadlock problem.
Could be Server Issue? or still deadlock in que? or something... ( I am just gussing)... What do you think?
January 8, 2013 at 9:47 am
rocky_498 (1/8/2013)
Could be Server Issue?
No
or still deadlock in que?
No.
Deadlocks occur when 2 pieces of code want the resources that the other is using. It's not a problem of one piece of code, rather of 2 (or more). If this proc is deadlocking on one server and not on the other, then the other server probably isn't running the code that this proc is deadlocking with.
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 8, 2013 at 10:16 am
I am Refreshing this Database from Production to Test (Everything is same except One day old data)
January 8, 2013 at 10:45 am
I'd be willing to bet a dollar it has something to do with those two "NOT IN"s in the query.If you change those to left joins I bet the problem is greatly mitigated, though it won't go away completely.
Deadlocks happen when a depends on b and b depends on a. that usually happens when a query takes a long time to complete. Making the query go fast makes it happen less often because by the time b depends on a, a no longer depends on b.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply