none
DeadLock SQL Server

    Question

  • We are facing deadlock issue at customer side. I verified Deadlock event. Below is deadlock event xml file. I set transaction isolation level as ReadCommitted for insert data. Even though we are faced with deadlock. Advise next step. Thanks in advance.

    <?xml version="1.0"?>
    -<deadlock>-<victim-list><victimProcess id="process320b57c38"/></victim-list>-<process-list>-<process id="process320b57c38" clientoption2="128056" clientoption1="671088672" lockTimeout="4294967295" currentdb="9" xactid="1732908131" isolationlevel="read committed (2)" loginname="sa" hostpid="3584" hostname="HSMEHR01" clientapp=".Net SqlClient Data Provider" lastattention="1900-01-01T00:00:00.617" lastbatchcompleted="2013-10-02T06:50:16.617" lastbatchstarted="2013-10-02T06:50:16.620" trancount="2" priority="0" ecid="0" sbid="0" spid="73" status="suspended" kpid="5496" schedulerid="4" lockMode="U" XDES="0x4f65756a8" lasttranstarted="2013-10-02T06:50:16.617" transactionname="user_transaction" ownerId="1732908131" waittime="1189" waitresource="PAGE: 9:1:396826 " logused="0" taskpriority="0">-<executionStack><frame sqlhandle="0x0300090044eb3411d325a3003ba2000001000000000000000000000000000000000000000000000000000000" stmtend="5156" stmtstart="4464" line="33" procname="Qst.Source.WCF.ProductionDb.PageDesigner.OperationConsentOperationConsent-Page2_1917_32_Insert_FD"> UPDATE Patients.InPageationsVersion SET AccountNumberCheckSum= @AccountNumberCheckSum_PatientsInPageations,AccountNumber= dbo.EncryptString(@AccountNumber_PatientsInPageations),LastModifiedBy=@LastModifiedBy,LastModifiedDate=@LastModifiedDate WHERE PatientId=@PatientId_PatientsInPageations AND PatientPageId=@PatientPageId_PatientsInPageations;; </frame></executionStack><inputbuf> Proc [Database Id = 9 Object Id = 288680772] </inputbuf></process>-<process id="process3d377d0c8" clientoption2="128056" clientoption1="671088672" lockTimeout="4294967295" currentdb="9" xactid="1732836266" isolationlevel="read committed (2)" loginname="sa" hostpid="3584" hostname="HSMEHR01" clientapp=".Net SqlClient Data Provider" lastattention="1900-01-01T00:00:00.313" lastbatchcompleted="2013-10-02T06:50:14.313" lastbatchstarted="2013-10-02T06:50:14.313" trancount="3" priority="0" ecid="0" sbid="0" spid="69" status="suspended" kpid="3276" schedulerid="2" lockMode="IU" XDES="0x4f655e3a8" lasttranstarted="2013-10-02T06:49:51.787" transactionname="user_transaction" ownerId="1732836266" waittime="1060" waitresource="PAGE: 9:1:21173 " logused="196424" taskpriority="0">-<executionStack><frame sqlhandle="0x02000000f2ec460f4ce36bda0b09671b6a11302f7548b3a30000000000000000000000000000000000000000" stmtstart="70" line="1" procname="adhoc"> UPDATE [Patients].[InPageationsVersion] set [PersonId] = @1 WHERE [PatientPageId]=@2 </frame><frame sqlhandle="0x02000000298ecf31799c72b7820e6ac716e750bd895292840000000000000000000000000000000000000000" line="1" procname="adhoc"> UPDATE Patients.InPageationsVersion SET PersonId='C9F7CBEA-435A-4D7F-810A-9BCD13375496' WHERE PatientPageId='98E053E9-C7D5-4038-BE85-7D0F930F43F1' </frame><frame sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000" stmtstart="-1" line="1" procname="mssqlsystemresource.sys.sp_executesql"> sp_executesql </frame><frame sqlhandle="0x03000900e3ba7e09d017300048a2000001000000000000000000000000000000000000000000000000000000" stmtend="12826" stmtstart="12718" line="144" procname="Qst.Source.WCF.ProductionDb.Patients.MovePatientPages"> EXEC sp_executesql @UPDATE_STATEMENT </frame></executionStack><inputbuf> Proc [Database Id = 9 Object Id = 159300323] </inputbuf></process></process-list>-<resource-list>-<pagelock id="lock4deb7ac00" associatedObjectId="72057594238402560" mode="IX" objectname="Qst.Source.WCF.ProductionDb.Patients.InPageationsVersion" subresource="FULL" dbid="9" pageid="396826" fileid="1">-<owner-list><owner id="process3d377d0c8" mode="IX"/></owner-list>-<waiter-list><waiter id="process320b57c38" mode="U" requestType="wait"/></waiter-list></pagelock>-<pagelock id="lock371627e80" associatedObjectId="72057594238402560" mode="U" objectname="Qst.Source.WCF.ProductionDb.Patients.InPageationsVersion" subresource="FULL" dbid="9" pageid="21173" fileid="1">-<owner-list><owner id="process320b57c38" mode="U"/></owner-list>-<waiter-list><waiter id="process3d377d0c8" mode="IU" requestType="wait"/></waiter-list></pagelock></resource-list></deadlock>


    Developer

    Thursday, October 03, 2013 7:03 AM

All replies

  • Can you reproduce this error? If so, please turn on SQL Profiler and select Deadlock Graph event...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, October 03, 2013 7:11 AM
  • Do you have index on [Patients].[InPageationsVersion] table for [PatientPageId]?

    Could you please provide details on the below:

    1. dbo.EncryptString(@AccountNumber_PatientsInPageations) function 

    2. Execution plan for the below statement:

     
      UPDATE [Patients].[InPageationsVersion] set [PersonId] = @1 WHERE [PatientPageId]=@2 
       UPDATE Patients.InPageationsVersion SET AccountNumberCheckSum= @AccountNumberCheckSum_PatientsInPageations,AccountNumber= dbo.EncryptString(@AccountNumber_PatientsInPageations),LastModifiedBy=@LastModifiedBy,LastModifiedDate=@LastModifiedDate WHERE PatientId=@PatientId_PatientsInPageations AND PatientPageId=@PatientPageId_PatientsInPageations;; 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, October 03, 2013 7:11 AM
  • dbo.EncryptString(@AccountNumber_PatientsInPageations) this fuction encrypt the account number using SHA1. I will provide the execution plan.

    Developer

    Thursday, October 03, 2013 7:39 AM
  • We can't reproduce at our local

    Developer

    Thursday, October 03, 2013 7:39 AM