Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Deadlock In SQL Expand / Collapse
Author
Message
Posted Monday, January 7, 2013 4:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 219, Visits: 834
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!
Post #1403909
Posted Monday, January 7, 2013 4:36 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1403910
Posted Monday, January 7, 2013 4:49 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 219, Visits: 834
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.
Post #1403913
Posted Monday, January 7, 2013 4:57 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1403916
Posted Monday, January 7, 2013 5:00 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 219, Visits: 834
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?)
Post #1403919
Posted Monday, January 7, 2013 5:02 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
The entire deadlock graph. Just one of them if there are multiple.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1403920
Posted Monday, January 7, 2013 5:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 219, Visits: 834
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,AND PM.[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...
Post #1403924
Posted Monday, January 7, 2013 5:26 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1403927
Posted Monday, January 7, 2013 5:30 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 219, Visits: 834
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.....

Post #1403928
Posted Monday, January 7, 2013 5:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 219, Visits: 834
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)
Post #1403931
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse