SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlock on single rows update and select statement


Deadlock on single rows update and select statement

Author
Message
stanley_1982
stanley_1982
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 482
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>
Adi Cohn
Adi Cohn
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8495 Visits: 6595
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/
stanley_1982
stanley_1982
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 482
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.
Adi Cohn
Adi Cohn
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8495 Visits: 6595
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/
stanley_1982
stanley_1982
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 482
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search