Deadlock on single rows update and select statement

  • Does anyone have any suggestion on these deadlock ? It seem like the desp_status and RUN_NO is the cause of the deadlock . I need advice to either implement the isolation level or create a new index . NoLock is not an option and the table ABC.dbo.TXN_DESP_OUT only have RUN_NO (PK_TXN_DESP_OUT) as a primary key and cluster index .

    - <deadlock-list>

    - <deadlock victim="process21ddb9198">

    - <process-list>

    - <process id="process21ddb9198" taskpriority="0" logused="0" waitresource="KEY: 9:72057596851585024 (92014ff3ee3a)" waittime="2203" ownerId="10934767660" transactionname="SELECT" lockMode="S" status="suspended" spid="76" sbid="0" ecid="0" priority="0" transcount="0" clientapp=".Net SqlClient Data Provider" hostname="SP-PROD" hostpid="511608" loginname="prod_db" isolationlevel="read committed (2)" xactid="10934767660" currentdb="9" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">

    - <executionStack>

    <frame procname="ABC.dbo.USP_PRO_GetDespatchAd" line="47" stmtstart="3388" stmtend="6248" sqlhandle="0x0300090066facb6be9f271005d9c00000100000000000000">

    select DESP_NO, TO_COUNTRY_CD, TO_EXCH_CD, TO_LOC_CD, SERVICE_TYPE, ITEM_TYPE, ITEM_CAT, SITE_CD, DESP_DT, AGENT_CD, TO_COUNTRY_CD+ ' / ' +TO_EXCH_CD+ ' / ' +TO_LOC_CD as DESTINATION, RUN_NO, DESP_STATUS, CREATE_DT, TRANSPORT_MODE, SERVICE_LVL=(SELECT SERVICE_LVL FROM VM_SERVICE_TYPE WHERE SERVICE_TYPE=TXN_DESP_OUT.SERVICE_TYPE AND TRAFFIC_IND<>'INB'), NO_OF_BAG, NO_OF_ITEM, TOTAL_CONV_WT, VAN_TIME, FLIGHT_NO1, FLIGHT_NO2, FLIGHT_NO3 , DEPART_DT1, DEPART_DT2, DEPART_DT3, ARRIVE_DT1, ARRIVE_DT2, ARRIVE_DT3, AIRPORT_CD1, AIRPORT_CD2, AIRPORT_CD3, TPT_MODE_DTL, CREATE_BY, MODIFY_DT, MODIFY_BY

    from TXN_DESP_OUT where (@STATUS = '' or DESP_STATUS = @status) AND (@SITE_CD = '' or SITE_CD = @SITE_CD)

    AND (@TO_COUNTRY_CD = '' or TO_COUNTRY_CD= @TO_COUNTRY_CD ) AND (@SERVICE_TYPE = '' o </frame>

    <frame procname="adhoc" line="1" stmtstart="822" sqlhandle="0x01000900668cbb028017a6bc000000000000000000000000">

    exec USP_PRO_GetDespatchAd @MODE = @grd_Despatch_List_searchMode, @status = @drp_S_DESP_STATUS, @SITE_CD = @drp_S_SITE_CD, @TO_COUNTRY_CD = @sel_S_TO_COUNTRY_CD, @SERVICE_TYPE = @sel_S_SERVICE_TYPE, @DESP_S_DT = @dat_S_DESP_DT, @DESP_NO = @txt_AS_DESP_NO, @DESP_DT = @dat_AS_DESP_DT, @AGENT_CD = @drp_AS_AGENT_CD, @TO_EXCH_CD = @sel_S_TO_EXCH_CD, @FROM_DT = @dat_S_FROM_DT, @TO_DT = @dat_S_TO_DT, @BAG_NO = @txt_AS_BAG_NO</frame>

    <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown</frame>

    </executionStack>

    <inputbuf>(@grd_Despatch_List_searchMode nvarchar(1),@drp_S_DESP_STATUS nvarchar(1),@drp_S_SITE_CD nvarchar(1),@sel_S_TO_COUNTRY_CD nvarchar(2),@sel_S_SERVICE_TYPE nvarchar(3),@dat_S_DESP_DT nvarchar(4000),@txt_AS_DESP_NO nvarchar(4000),@dat_AS_DESP_DT nvarchar(4000),@drp_AS_AGENT_CD nvarchar(4000),@sel_S_TO_EXCH_CD nvarchar(4000),@dat_S_FROM_DT nvarchar(4000),@dat_S_TO_DT nvarchar(4000),@txt_AS_BAG_NO nvarchar(4000))exec USP_PRO_GetDespatchAd @MODE = @grd_Despatch_List_searchMode, @status = @drp_S_DESP_STATUS, @SITE_CD = @drp_S_SITE_CD, @TO_COUNTRY_CD = @sel_S_TO_COUNTRY_CD, @SERVICE_TYPE = @sel_S_SERVICE_TYPE, @DESP_S_DT = @dat_S_DESP_DT, @DESP_NO = @txt_AS_DESP_NO, @DESP_DT = @dat_AS_DESP_DT, @AGENT_CD = @drp_AS_AGENT_CD, @TO_EXCH_CD = @sel_S_TO_EXCH_CD, @FROM_DT = @dat_S_FROM_DT, @TO_DT = @dat_S_TO_DT, @BAG_NO = @txt_AS_BAG_NO</inputbuf>

    </process>

    - <process id="process2770b0c58" taskpriority="0" logused="117124" waitresource="PAGE: 9:1:1100400" waittime="2015" ownerId="10934767383" transactionname="user_transaction" lockMode="IX" schedulerid="6" kpid="39300" status="suspended" spid="121" sbid="0" ecid="0" priority="0" transcount="3" clientapp=".Net SqlClient Data Provider" hostname="SP-PROD" hostpid="511608" loginname="prod_db" isolationlevel="read committed (2)" xactid="10934767383" currentdb="9" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">

    - <executionStack>

    <frame procname="ABC.dbo.USP_OUT_DESP_CloseDespatch" line="26" stmtstart="1854" stmtend="2128" sqlhandle="0x0300090034a06522fc091d015d9c00000100000000000000">

    UPDATE TXN_DESP_OUT set DESP_STATUS = 'C',DESP_STATUS_DT=getdate() WHERE RUN_NO=@RUN_NO and DESP_STATUS <> 'C'</frame>

    <frame procname="ABC.dbo.USP_PRO_UpdateDespatchStatus" line="81" stmtstart="7016" stmtend="7142" sqlhandle="0x03000900ec2ca6019cd53a018e9c00000100000000000000">

    EXEC USP_OUT_DESP_CloseDespatch @RUN_NO, @RET_CODE OUTPUT</frame>

    <frame procname="adhoc" line="1" stmtstart="92" sqlhandle="0x01000900525f6527104c4f61010000000000000000000000">EXEC USP_PRO_UpdateDespatchStatus @para_000,@para_001</frame>

    <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown</frame>

    </executionStack>

    <inputbuf>(@para_000 nvarchar(15),@para_001 nvarchar(8))EXEC USP_PRO_UpdateDespatchStatus @para_000,@para_001</inputbuf>

    </process>

    </process-list>

    - <resource-list>

    - <keylock hobtid="72057596851585024" dbid="9" objectname="ABC.dbo.TXN_DESP_OUT" indexname="PK_TXN_DESP_OUT" id="lockdd9a0380" mode="X" associatedObjectId="72057596851585024">

    - <owner-list>

    <owner id="process2770b0c58" mode="X" />

    </owner-list>

    - <waiter-list>

    <waiter id="process21ddb9198" mode="S" requestType="wait" />

    </waiter-list>

    </keylock>

    - <pagelock fileid="1" pageid="1100400" dbid="9" objectname="ABC.dbo.TXN_DESP_OUT" id="lock33f247580" mode="S" associatedObjectId="72057596881338368">

    - <owner-list>

    <owner id="process21ddb9198" mode="S" />

    </owner-list>

    - <waiter-list>

    <waiter id="process2770b0c58" mode="IX" requestType="wait" />

    </waiter-list>

    </pagelock>

    </resource-list>

    </deadlock>

    </deadlock-list>

  • Another index might help you, but we can't know for sure. It depends on the selectivity of the other columns and on the values that are being used for the parameters. For example, if the column DESP_STATUS is not selective at all (for example 70% of the rows have one value and the rest 30% have another value), then an index on that column won't be helpful at all. On the other hand if the column SITE_CD is very selective, but the procedure gets an empty string as the value for @SITE_CD, then again an index won't be helpful at all.

    If you don't have the primary key as one of the parameters, then currently the only plan that can be used is table scan. Take into consideration that if you'll have other indexes that sometimes can be used or one of the parameters is the primary key, but it is being passed only some times, then you'll have another problem because you'll might have in the plan cache a query plan that was very good plan to the values of the parameters that were sent to the procedure earlier, but with the new parameters that were sent the next time that the procedure was activated, the query plan will be a terrible one. Whenever I have a procedure that that its query plan should have different plans according to the input parameters, I try to work with few procedures instead of one so each one will have its own query plan or create it with recompile.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • There are actually index created :

    CREATE NONCLUSTERED INDEX [IDX_TXN_DESP_OUT_STATUS] ON [dbo].[TXN_DESP_OUT]

    (

    [DESP_STATUS] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[TXN_DESP_OUT] ADD CONSTRAINT [PK_TXN_DESP_OUT] PRIMARY KEY CLUSTERED

    (

    [RUN_NO] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    Any idea Why the update statement will require a page lock after a row lock on PK_TXN_DESP_OUT (RUN_NO) cluster index ?

    UPDATE TXN_DESP_OUT

    set DESP_STATUS = 'C',DESP_STATUS_DT=getdate()

    WHERE RUN_NO=@RUN_NO and DESP_STATUS <> 'C'

    The deadlock trace showing the waitresource="PAGE: 9:1:1100400". i am trying to prevent this page lock so that the deadlock will not occurred.

  • This is an intent lock. As you remember the server can have locks on few levels - row lock, page lock and table lock. Think of a situation that the process X used an exclusive row lock. After the row was locked another process (process Y) wants to get a table lock on the same table. Since part of the table is already locked it can't get that lock. It first has to make sure that there isn't any part of the table is already locked. One way is to check each page and each row if it is locked, but this will take time and resources. Instead there is another type of lock that is called intent lock. Whenever a resource is locked, the resource above it will have an intent lock to signal that a resource within it is already locked. So when a row is locked, the page that holds this row will have an intent lock that will mark that this page has in it a row that is locked and the table will also get an intent lock to mark that page or row in the table is locked.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • It means the IX locks that requested by the update process is not successful because the same page is owned by the select process . and the select operation is also requested rowlock on the record holding by the

    update process .

    How can i prevent this scenario happened again ?

    1. use rowlock hints in the select statement to prevent the page lock on the entire page ?

    2. add additional non cluster index with run_no and desp_status as indexed column ?

    3. use snapshot isolation level or serialize isolation level on all the stored procedure involve in deadlock?

Viewing 5 posts - 1 through 4 (of 4 total)

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