Dead lock issue

  • Hi ,

    we uses a view of two select queries with no.of joins and these two quies retriving data using UNION all.

    it clousing deadlock

    how i optimize it....i checked indexex are currect.

    how i decide it is a poor query.....

    ------------------this is the view

    CREATE view [INMISPR01].[ANH_Cancellation_Report] as

    --set dateformat 'dmy'

    SELECT RecCustomerInformation.PolicyNo as [Policy No], RecCustomerInformation.CustomerName as [Policy Holder],

    RecDataEntry.CCNo as [CC No.], RecDataEntry.RefundAmount, [Billed Date]=cast(RecDataEntry.BilledDt as nvarchar(12)),

    [Pol Eff. Date]=cast(RecCustomerInformation.PolicyEffectiveDate as nvarchar(12)),

    Premium=isnull(RecCustomerInformation.Premium,0),RecDataEntry.CancelledEmma, [Cancel Date]=

    cast(RecDataEntry.cancellationDate as nvarchar(12)),RecDataEntry.EndorsementDate ,

    [Refund Date]=cast(RecDataEntry.Refunddate as nvarchar(12)), [Sec Rfnd Amt]=RecDataEntry.SndRefundAmount,

    [Refund Date2]=cast(RecDataEntry.SndRefundDate as nvarchar(12)), RecDataEntry.Remarks,[HSBC Date]=

    cast(RecDataEntry.HSBCRecDt as nvarchar(12)), RecDataEntry.HSBCBatchNo, RecDataEntry.RefundAmount as Amount,

    CAST( RecDataEntry.BilledDt AS nvarchar(12)) as [Transaction Date],CAST( ReccustomerInformation.PolicyPrintDate AS

    nvarchar(12)) as [Policy Print Date], DocumentsRecDate, null as VLIDATE

    FROM RecDataEntry INNER

    JOIN RecCustomerInformation ON RecDataEntry.PolicyNo = RecCustomerInformation.PolicyNo

    Inner join RecCallDetails on reccustomerInformation.TxnrecID=ReccallDetails.Txnrecid

    where

    --RecDataEntry.EndorsementDate between '10/14/2009' and '10/14/2009' and

    RecDataEntry.TxnID in ( select max(TxnID)

    from RecDataEntry group by PolicyNo)-- and RecDataEntry.DocumentsRecDate is not null

    and reccalldetails.TxnID in ( select max(TxnID)

    from reccalldetails group by PolicyNo)-- and RecDataEntry.DocumentsRecDate is not null

    --Comment By Priya Arora dated 0n 14-10-09

    and reccalldetails.policyno not in(select distinct policyno from reccalldetails)

    --where vlidate is not null)

    --order by RecDataEntry.policyno

    --Comment By Priya Arora dated on 14-10-09

    union all

    SELECT RecCustomerInformation.PolicyNo as [Policy No], RecCustomerInformation.CustomerName as [Policy Holder],

    RecDataEntry.CCNo as [CC No.], RecDataEntry.RefundAmount, [Billed Date]=cast(RecDataEntry.BilledDt as nvarchar(12)),

    [Pol Eff. Date]=cast(RecCustomerInformation.PolicyEffectiveDate as nvarchar(12)),

    Premium=isnull(RecCustomerInformation.Premium,0),RecDataEntry.CancelledEmma, [Cancel Date]=

    cast(RecDataEntry.cancellationDate as nvarchar(12)), RecDataEntry.EndorsementDate ,

    [Refund Date]=cast(RecDataEntry.Refunddate as nvarchar(12)), [Sec Rfnd Amt]=RecDataEntry.SndRefundAmount,

    [Refund Date2]=cast(RecDataEntry.SndRefundDate as nvarchar(12)), RecDataEntry.Remarks,[HSBC Date]=

    cast(RecDataEntry.HSBCRecDt as nvarchar(12)), RecDataEntry.HSBCBatchNo, RecDataEntry.RefundAmount as Amount,

    CAST( RecDataEntry.BilledDt AS nvarchar(12)) as [Transaction Date],CAST( ReccustomerInformation.PolicyPrintDate AS

    nvarchar(12)) as [Policy Print Date], DocumentsRecDate, VLIDATE

    FROM RecDataEntry INNER

    JOIN RecCustomerInformation ON RecDataEntry.PolicyNo = RecCustomerInformation.PolicyNo

    Inner join RecCallDetails on reccustomerInformation.TxnrecID=ReccallDetails.Txnrecid

    where

    --RecDataEntry.EndorsementDate between '15/05/2007' and '15/05/2007' and

    RecDataEntry.TxnID in ( select max(TxnID)

    from RecDataEntry group by PolicyNo)-- and RecDataEntry.DocumentsRecDate is not null

    and reccalldetails.TxnID in ( select min(TxnID)

    from reccalldetails

    --where VLIDATE is not Null

    group by policyno)-- and RecDataEntry.DocumentsRecDate is not null

    --order by RecDataEntry.policyno

    Regards,
    Shivrudra W

  • 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)

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi Gail ...this is the wait graph.

    08/03/2010 09:43:13,spid14s,Unknown,waiter id=process291f28e0

    08/03/2010 09:43:13,spid14s,Unknown,waiter-list

    08/03/2010 09:43:13,spid14s,Unknown,owner id=process2885caa8

    08/03/2010 09:43:13,spid14s,Unknown,owner-list

    08/03/2010 09:43:13,spid14s,Unknown,exchangeEvent id=Port5412700 WaitType=e_waitPortOpen nodeId=7

    08/03/2010 09:43:13,spid14s,Unknown,waiter id=process2885caa8 mode=U requestType=wait

    08/03/2010 09:43:13,spid14s,Unknown,waiter-list

    08/03/2010 09:43:13,spid14s,Unknown,owner id=process291cfe40 mode=U

    08/03/2010 09:43:13,spid14s,Unknown,owner-list

    08/03/2010 09:43:13,spid14s,Unknown,pagelock fileid=1 pageid=1918424 dbid=5 objectname=CCMIS.dbo.reccalldetails id=lock73b328c0 mode=U associatedObjectId=72057594771079168

    08/03/2010 09:43:13,spid14s,Unknown,waiter id=process291cfe40 mode=U requestType=wait

    08/03/2010 09:43:13,spid14s,Unknown,waiter-list

    08/03/2010 09:43:13,spid14s,Unknown,owner id=process291f28e0 mode=U

    08/03/2010 09:43:13,spid14s,Unknown,owner-list

    08/03/2010 09:43:13,spid14s,Unknown,pagelock fileid=1 pageid=1918919 dbid=5 objectname=CCMIS.dbo.reccalldetails id=lock5f04e500 mode=U associatedObjectId=72057594771079168

    08/03/2010 09:43:13,spid14s,Unknown,resource-list

    08/03/2010 09:43:13,spid14s,Unknown,inputbuf

    08/03/2010 09:43:13,spid14s,Unknown,Update RecCallDetails set ParentCallID = TxnID<c/>IntDataUploadTimeStamp=DataUploadTimeStamp<c/>CaseType ='FRESH' where CaseType ='FRESHNew'

    08/03/2010 09:43:13,spid14s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000009c2f80359a1419d5f8f7c313e625d1c2fef66ad4

    08/03/2010 09:43:13,spid14s,Unknown,(@1 varchar(8000)<c/>@2 varchar(8000))UPDATE [RecCallDetails] set [ParentCallID] = [TxnID]<c/>[IntDataUploadTimeStamp] = [DataUploadTimeStamp]<c/>[CaseType] = @1 WHERE [CaseType]=@2

    08/03/2010 09:43:13,spid14s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000000ab0803977df2a1f8921311b0cbe3338ad02c782

    08/03/2010 09:43:13,spid14s,Unknown,executionStack

    08/03/2010 09:43:13,spid14s,Unknown,process id=process291f28e0 taskpriority=0 logused=10000 waittime=1092 schedulerid=3 kpid=3844 status=suspended spid=90 sbid=0 ecid=3 priority=0

    trancount=0 lastbatchstarted=2010-08-03T09:43:04.760 lastbatchcompleted=2010-08-03T09:43:04.730 clientapp=ICIS System hostname=OISWXR5IPCO037 hostpid=868 isolationlevel=read committed (2) xactid=1075601334 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

    08/03/2010 09:43:13,spid14s,Unknown,inputbuf

    08/03/2010 09:43:13,spid14s,Unknown,Update RecCallDetails set ParentCallID = TxnID<c/>IntDataUploadTimeStamp=DataUploadTimeStamp<c/>CaseType ='FRESH' where CaseType ='FRESHNew'

    08/03/2010 09:43:13,spid14s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000009c2f80359a1419d5f8f7c313e625d1c2fef66ad4

    08/03/2010 09:43:13,spid14s,Unknown,(@1 varchar(8000)<c/>@2 varchar(8000))UPDATE [RecCallDetails] set [ParentCallID] = [TxnID]<c/>[IntDataUploadTimeStamp] = [DataUploadTimeStamp]<c/>[CaseType] = @1 WHERE [CaseType]=@2

    08/03/2010 09:43:13,spid14s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000000ab0803977df2a1f8921311b0cbe3338ad02c782

    08/03/2010 09:43:13,spid14s,Unknown,executionStack

    08/03/2010 09:43:13,spid14s,Unknown,process id=process2885caa8 taskpriority=0 logused=0 waitresource=PAGE: 5:1:1918424 waittime=8757 ownerId=1075601334 transactionname=UPDATE lasttranstarted=2010-08-03T09:43:04.760 XDES=0x2dc50c28 lockMode=U schedulerid=1 kpid=3992 status=suspended spid=90 sbid=0 ecid=4 priority=0 trancount=0 lastbatchstarted=2010-08-03T09:43:04.760 lastbatchcompleted=2010-08-03T09:43:04.730 clientapp=ICIS System hostname=OISWXR5IPCO037 hostpid=868 isolationlevel=read committed (2) xactid=1075601334 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

    08/03/2010 09:43:13,spid14s,Unknown,Update RecCallDetails set ParentCallID = TxnID<c/>IntDataUploadTimeStamp=DataUploadTimeStamp<c/>CaseType ='FRESH' where CaseType ='FRESHNew'

    08/03/2010 09:43:13,spid14s,Unknown,inputbuf

    08/03/2010 09:43:13,spid14s,Unknown,Update RecCallDetails set ParentCallID = TxnID<c/>IntDataUploadTimeStamp=DataUploadTimeStamp<c/>CaseType ='FRESH' where CaseType ='FRESHNew'

    08/03/2010 09:43:13,spid14s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000009c2f80359a1419d5f8f7c313e625d1c2fef66ad4

    08/03/2010 09:43:13,spid14s,Unknown,(@1 varchar(8000)<c/>@2 varchar(8000))UPDATE [RecCallDetails] set [ParentCallID] = [TxnID]<c/>[IntDataUploadTimeStamp] = [DataUploadTimeStamp]<c/>[CaseType] = @1 WHERE [CaseType]=@2

    08/03/2010 09:43:13,spid14s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000000ab0803977df2a1f8921311b0cbe3338ad02c782

    08/03/2010 09:43:13,spid14s,Unknown,executionStack

    08/03/2010 09:43:13,spid14s,Unknown,process id=process291cfe40 taskpriority=0 logused=0 waitresource=PAGE: 5:1:1918919 waittime=8440 ownerId=1075600959 transactionname=UPDATE lasttranstarted=2010-08-03T09:43:02.123 XDES=0x439c69d8 lockMode=U schedulerid=6 kpid=6356 status=suspended spid=122 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2010-08-03T09:43:02.123 lastbatchcompleted=2010-08-03T09:43:02.117 clientapp=ICIS System hostname=OISWXR5IPCO047 hostpid=1068 loginname=INMISPR01 isolationlevel=read committed (2) xactid=1075600959 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

    08/03/2010 09:43:13,spid14s,Unknown,process-list

    08/03/2010 09:43:13,spid14s,Unknown,deadlock victim=process291cfe40

    08/03/2010 09:43:13,spid14s,Unknown,deadlock-list

    08/03/2010 09:43:13,spid6s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x439C69D8 Mode: U SPID:122 BatchID:0 ECID:0 TaskProxy:(0x383CA354) Value:0x412202c0 Cost:(0/0)

    08/03/2010 09:43:13,spid6s,Unknown,Victim Resource Owner:

    08/03/2010 09:43:13,spid6s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

    08/03/2010 09:43:13,spid6s,Unknown,ResType:ExchangeId Stype:'AND' SPID:90 BatchID:0 ECID:3 TaskProxy:(0x28F4C708) Value:0x291f28e0 Cost:(0/10000)

    08/03/2010 09:43:13,spid6s,Unknown,Port: 0x05412700 Xid Slot: 4<c/> Wait Slot: 5<c/> Task: 0x291F28E0<c/> (Producer)<c/> Exchange Wait Type: e_waitPortOpen<c/> Merging: 0

    08/03/2010 09:43:13,spid6s,Unknown,Node:3

    08/03/2010 09:43:13,spid6s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

    08/03/2010 09:43:13,spid6s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x2DC50C28 Mode: U SPID:90 BatchID:0 ECID:4 TaskProxy:(0x28F4C6D8) Value:0x2928bbc0 Cost:(0/0)

    08/03/2010 09:43:13,spid6s,Unknown,Requested by:

    08/03/2010 09:43:13,spid6s,Unknown,Input Buf: Language Event: Update RecCallDetails set ParentCallID = TxnID<c/>IntDataUploadTimeStamp=DataUploadTimeStamp<c/>CaseType ='FRESH' where CaseType ='FRESHNew'

    08/03/2010 09:43:13,spid6s,Unknown,SPID: 122 ECID: 0 Statement Type: UPDATE Line #: 1

    08/03/2010 09:43:13,spid6s,Unknown,Owner:0x4CAD7840 Mode: U Flg:0x40 Ref:0 Life:00000001 SPID:122 ECID:0 XactLockInfo: 0x439C6A00

    08/03/2010 09:43:13,spid6s,Unknown,Grant List 2:

    08/03/2010 09:43:13,spid6s,Unknown,PAGE: 5:1:1918424 CleanCnt:2 Mode:U Flags: 0x3

    08/03/2010 09:43:13,spid6s,Unknown,Node:2

    08/03/2010 09:43:13,spid6s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

    08/03/2010 09:43:13,spid6s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x439C69D8 Mode: U SPID:122 BatchID:0 ECID:0 TaskProxy:(0x383CA354) Value:0x412202c0 Cost:(0/0)

    08/03/2010 09:43:13,spid6s,Unknown,Requested by:

    08/03/2010 09:43:13,spid6s,Unknown,Input Buf: No Event:

    08/03/2010 09:43:13,spid6s,Unknown,SPID: 90 ECID: 3 Statement Type: UPDATE Line #: 1

    08/03/2010 09:43:13,spid6s,Unknown,Owner:0x52F9D4E0 Mode: U Flg:0x40 Ref:0 Life:00000001 SPID:90 ECID:3 XactLockInfo: 0x2AA3BD28

    08/03/2010 09:43:13,spid6s,Unknown,Grant List 3:

    08/03/2010 09:43:13,spid6s,Unknown,PAGE: 5:1:1918919 CleanCnt:2 Mode:U Flags: 0x3

    08/03/2010 09:43:13,spid6s,Unknown,Node:1

    08/03/2010 09:43:13,spid6s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

    08/03/2010 09:43:13,spid6s,Unknown,Wait-for graph

    08/03/2010 09:43:13,spid6s,Unknown,Deadlock encountered .... Printing deadlock information

    Regards,
    Shivrudra W

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply