reducing deadlocks with serializable iso level

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

reducing deadlocks with serializable iso level

Author

Topic

MuadDBA

628 Posts

Posted - 2011-08-24 : 16:32:58

I have a process in which serializable is insisted to be required by the development team. Attempts on my part to remove this notion so far have failed, so now I am reduced to doing my best to prevent deadlocks when they are executing concurrent insert/updates into a table.

The current proc is structured like this:


update table
if @@rowcount = 0 then 
insert into table values (x, y, z)

I have also tried putting the x, y, z values into a table variable and then using a MERGE.

Both of these result in deadlocks based on the attempt to convert a shared range lock to an exclusive one. I have little experience in serializable isoloation level environments, so my next ideas are as follows:

attempt the insert first, to move the range-x lock to the beginning of the batch, as in:


insert into table values (x, y, z)
where not exists (select a, b, c from table where a=x, b= y, c = z)
if @@rowcount = 0 then update table

my final idea is to use a try/catch block, as in


begin try
insert into table values (x, y, z)
end try
begin catch 
update table
end catch

I am looking for advice on which of my stragies, if any, is advisable, and if there are better ways to accomlpsh what I am trying to do.

Thanks!

sqlmashup
Starting Member

5 Posts

Posted - 2011-08-24 : 17:07:57

Not sure I completely understand the requirements. What was the purpose of the table variable if you are able to use the MERGE statement?

sqlmashup
Starting Member

5 Posts

Posted - 2011-08-24 : 17:31:55

here are some links to check out about MERGE

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/7f01abee-4f07-4a45-be4f-08581bf5b3d5/

http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server

http://weblogs.sqlteam.com/mladenp/archive/2007/08/03/60277.aspx

http://technet.microsoft.com/en-us/library/bb510625.aspx

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-08-24 : 19:54:15

Could you post the full proc and deadlock trace?

MuadDBA

628 Posts

Posted - 2011-08-24 : 22:37:05

Here is the code (please pardon the confusion, I asked for a username change and it went through much faster than I thought)


  
cREATE PROCEDURE [dbo].[UpdateACHDailyTransactionTotal_joe]   
 @AcctIdCriteria VARCHAR(8)   
 , @DollarAmtCriteria DECIMAL(22,5)  
 , @MMTransTypeAliasCriteria VARCHAR(30)  
 , @EffectiveDateCriteria DATETIME  
 , @User VARCHAR(50) = 'fakeuser'  
AS  
BEGIN  
 DECLARE @MMTransTypeCode CHAR(3);  
 DECLARE @CountDailyTrans INT;  
 DECLARE @Merge TABLE(  
 AIC varchar(8),  
 DAC decimal(22,5),  
 MMTC char(3),  
 EDC Datetime,  
 Usr varchar(50))  
   
 SET @MMTransTypeCode  = (SELECT TOP 1 MoneyMovementTransTypeCode FROM MoneyMovementTransTypeLkuVw WHERE Alias = @MMTransTypeAliasCriteria)  
   
 Insert into @Merge values (@AcctIdCriteria,@DollarAmtCriteria, @MMTransTypeCode, @EffectiveDateCriteria,@User)  
   
  
 MERGE INTO [dbo].[money_movement_trans] AS TGT -- Target table  
  
 USING @Merge AS SRC -- Source table  
    ON TGT.acct_id = SRC.AIC  
   AND TGT.eff_dtm = SRC.EDC  
   AND TGT.money_movement_trans_type_code = SRC.MMTC  
     
  WHEN MATCHED THEN  
  UPDATE SET  
   [dollar_amt] += DAC  
   ,last_update_dtm = SYSDATETIME ()  
   ,last_update_user_name = Usr  
     
  WHEN NOT MATCHED THEN  
  INSERT ([acct_id]  
   , [money_movement_trans_type_code]  
   , [dollar_amt]  
   , eff_dtm  
   , last_update_user_name )  
 VALUES (@AcctIdCriteria  
   , @MMTransTypeCode  
   , @DollarAmtCriteria  
   , @EffectiveDateCriteria  
   , @User);  
     

     
END

and here are the two executions that deadlocked:
exec UpdateACHDailyTransactionTotal @AcctIdCriteria=N'531',@DollarAmtCriteria=-1,@MMTransTypeAliasCriteria=N'Outbound',@EffectiveDateCriteria='2011-08-22 00:00:00',@User=N'jim-bob'

exec UpdateACHDailyTransactionTotal @AcctIdCriteria=N'376',@DollarAmtCriteria=500,@MMTransTypeAliasCriteria=N'Inbound',@EffectiveDateCriteria='2011-08-22 00:00:00',@User=N'jim-bob'

The deadlock exent data is as follows:


<deadlock-list>
 <deadlock victim="process60e5948">
  <process-list>
   <process id="process60e5948" taskpriority="0" logused="0" waitresource="KEY: 19:72057594051035136 (c2f2666cc8f9)" waittime="3999" ownerId="5737933" transactionguid="0x15b45995b5c7a14cb3ff701bc71c7f8c" transactionname="user_transaction" lasttranstarted="2011-08-18T15:32:57.043" XDES="0x3cef5a3b0" lockMode="X" schedulerid="23" kpid="9072" status="suspended" spid="103" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-08-18T15:32:57.663" lastbatchcompleted="2011-08-18T15:32:57.370" clientapp=".Net SqlClient Data Provider" hostname="CANAL2XB" hostpid="13404" loginname="RJF\SOADevMoneyMovement" isolationlevel="serializable (4)" xactid="5737933" currentdb="19" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="FundsTransferPhysical.dbo.UpdateACHDailyTransactionTotal" line="25" stmtstart="1632" stmtend="3420" sqlhandle="0x03001300a436f80e9e81df00439f00000100000000000000">
MERGE INTO [dbo].[money_movement_trans] AS TGT  -- Target table

      USING @Merge AS SRC     -- Source table
         ON TGT.acct_id = SRC.AIC
        AND TGT.eff_dtm = SRC.EDC
        AND TGT.money_movement_trans_type_code = SRC.MMTC
        
       WHEN MATCHED THEN
      UPDATE SET
                  [dollar_amt] += DAC
                  ,last_update_dtm = SYSDATETIME ()
                  ,last_update_user_name = Usr
                  
      WHEN NOT MATCHED THEN
      INSERT ([acct_id]
                  , [money_movement_trans_type_code]
                  , [dollar_amt]
                  , eff_dtm
                  , last_update_user_name )
      VALUES (@AcctIdCriteria
                  , @MMTransTypeCode
                  , @DollarAmtCriteria
                  , @EffectiveDateCriteria
                  , @User);     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 19 Object Id = 251147940]    </inputbuf>
   </process>
   <process id="process60a9948" taskpriority="0" logused="132" waitresource="KEY: 19:72057594051035136 (578a6cd15742)" waittime="4195" ownerId="5737916" transactionguid="0x229320b3213dd64f834557b4ec54b418" transactionname="user_transaction" lasttranstarted="2011-08-18T15:32:56.983" XDES="0x800bd1b0" lockMode="RangeI-N" schedulerid="17" kpid="1608" status="suspended" spid="111" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-08-18T15:32:57.470" lastbatchcompleted="2011-08-18T15:32:57.293" clientapp=".Net SqlClient Data Provider" hostname="CANAL2XB" hostpid="13404" loginname="RJF\SOADevMoneyMovement" isolationlevel="serializable (4)" xactid="5737916" currentdb="19" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="FundsTransferPhysical.dbo.UpdateACHDailyTransactionTotal" line="25" stmtstart="1632" stmtend="3420" sqlhandle="0x03001300a436f80e9e81df00439f00000100000000000000">
MERGE INTO [dbo].[money_movement_trans] AS TGT  -- Target table

      USING @Merge AS SRC     -- Source table
         ON TGT.acct_id = SRC.AIC
        AND TGT.eff_dtm = SRC.EDC
        AND TGT.money_movement_trans_type_code = SRC.MMTC
        
       WHEN MATCHED THEN
      UPDATE SET
                  [dollar_amt] += DAC
                  ,last_update_dtm = SYSDATETIME ()
                  ,last_update_user_name = Usr
                  
      WHEN NOT MATCHED THEN
      INSERT ([acct_id]
                  , [money_movement_trans_type_code]
                  , [dollar_amt]
                  , eff_dtm
                  , last_update_user_name )
      VALUES (@AcctIdCriteria
                  , @MMTransTypeCode
                  , @DollarAmtCriteria
                  , @EffectiveDateCriteria
                  , @User);     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 19 Object Id = 251147940]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594051035136" dbid="19" objectname="FundsTransferPhysical.dbo.money_movement_trans" indexname="PKmoney_movement_trans_IDX" id="lock14ca53b00" mode="RangeS-U" associatedObjectId="72057594051035136">
    <owner-list>
     <owner id="process60a9948" mode="RangeS-S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process60e5948" mode="X" requestType="convert"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594051035136" dbid="19" objectname="FundsTransferPhysical.dbo.money_movement_trans" indexname="PKmoney_movement_trans_IDX" id="lock1691de800" mode="RangeS-U" associatedObjectId="72057594051035136">
    <owner-list>
     <owner id="process60e5948" mode="RangeS-S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process60a9948" mode="RangeI-N" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-08-25 : 00:03:05

I think 2 places should be changed :

1 - isolation level
2 - update/select statement

As you say, 1 can not be changed. This makes harder to avoid deadlock, but let try with 2.

Merge does not help in this case, so come back with your original sp.

Could you post your original sp (without using merge)?

Also, enable both trace 1222 and 1204, then post the deadlock trace.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-25 : 04:47:28

No need to enable 1204 and 1222. 1222 produces the newer style deadlock graph. It contains all the info that the 1204 graph contains and far more.

Just enable traceflag 1222

--
Gail Shaw
SQL Server MVP

MuadDBA

628 Posts

Posted - 2011-08-25 : 10:15:35

1222 is enabled. What you see above is what I get when I extract the event data from the deadlock graph so I could paste it here. Here is the graphical representation:

sqlmashup
Starting Member

5 Posts

Posted - 2011-08-25 : 11:05:05

Was this solved already? The tweet was a little confusing.

The deadlock type appears to be from a lock escalation and not the stereotypical SELECT then UPDATE while something else does UPDATE then SELECT. I assumed the latter which is probably wrong looking at this graph so changing the SELECT then MERGE to just MERGE will unlikely solve the issue.

Below is an attempt to rewrite the MERGE anyway to solve your deadlock lock hints are probably the solution.

To confirm the lock escalation deadlock being the only issue can you run a trace with event "Lock:Escalation"? In the meantime I will dig into the locking hint recommendations for heaps in conjuction with merge statement.

MERGE INTO [dbo].[money_movement_trans] AS TGT -- Target table

USING (SELECT @AcctIdCriteria AS AIC, @DollarAmtCriteria AS DAC, @EffectiveDateCriteria AS EDC, @User AS Usr, MoneyMovementTransTypeCode FROM MoneyMovementTransTypeLkuVw WHERE Alias = @MMTransTypeAliasCriteria) AS SRC -- Source table
ON TGT.acct_id = SRC.AIC
AND TGT.eff_dtm = SRC.EDC
AND TGT.money_movement_trans_type_code = SRC.MMTC

WHEN MATCHED THEN
UPDATE SET
[dollar_amt] += DAC
,last_update_dtm = SYSDATETIME ()
,last_update_user_name = Usr

WHEN NOT MATCHED THEN
INSERT ([acct_id]
, [money_movement_trans_type_code]
, [dollar_amt]
, eff_dtm
, last_update_user_name )
VALUES (AIC
, MoneyMovementTransTypeCode
, DAC
, EDC
, Usr);

sqlmashup
Starting Member

5 Posts

Posted - 2011-08-25 : 11:45:18

Joe, before suggesting a lock hint can you please confirm/deny these assumptions?

1. PKmoney_movement_trans_IDX is a unique index built over money_movement_trans (AIC, EDC, money_movement_trans_type_code).
2. It is unacceptable to change isolation levels before MERGE even if it is set back to SERIALIZABLE afterward.
3. There are queries with performance expectations using ROW and/or PAGE locks on PKmoney_movement_trans_IDX.

sqlmashup
Starting Member

5 Posts

Posted - 2011-08-25 : 17:26:43

using the test scripts here- http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx

i was unable to recreate deadlocking when the isolation level is serializable which as expected and the reason to question some assumptions

continuing to assume the previous 3 items prevent other solution choices and not being able to recreate your issue my belief is that the lock hint you should try is exclusive table lock. your graph shows deadlocking with inserts so you may have a hotspot the exclusive lock will cause all changes to queue up and occur sequentially but as long as performance (concurrency) is not a problem this will hopefully avoid deadlocks:

MERGE INTO [dbo].[money_movement_trans] WITH (TABLOCKX) AS TGT -- Target table

USING (SELECT @AcctIdCriteria AS AIC, @DollarAmtCriteria AS DAC, @EffectiveDateCriteria AS EDC, @User AS Usr, MoneyMovementTransTypeCode AS MMTC FROM MoneyMovementTransTypeLkuVw WHERE Alias = @MMTransTypeAliasCriteria) AS SRC -- Source table
ON TGT.acct_id = SRC.AIC
AND TGT.eff_dtm = SRC.EDC
AND TGT.money_movement_trans_type_code = SRC.MMTC

WHEN MATCHED THEN
UPDATE SET
[dollar_amt] += DAC
,last_update_dtm = SYSDATETIME ()
,last_update_user_name = Usr

WHEN NOT MATCHED THEN
INSERT ([acct_id]
, [money_movement_trans_type_code]
, [dollar_amt]
, eff_dtm
, last_update_user_name )
VALUES (AIC
, MoneyMovementTransTypeCode
, DAC
, EDC
, Usr);

MuadDBA

628 Posts

Posted - 2011-08-26 : 11:41:50

Thanks. We ran another test yesterday and for some reason generated no deadlocks when previously we had generated 20+

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources