RE: Deadlock In SQL – SQLServerCentral

rocky_498

SSCertifiable

Points: 6532

January 7, 2013 at 5:10 pm

#1574578

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...