none
Deadlock issue in Jobs

    Question

  •  

    Hi all 

    I am getting lot of deadlocks in my jobs while processing.  The below one which i am getting error msg in my jobs  :-

     

    Executed as user: NT AUTHORITY\SYSTEM. Transaction (Process ID 141) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205).  The step failed.

    can any one tell me what prevent i can do for this?

     

    --kmr 

    Monday, November 3, 2008 1:06 PM

All replies

  • If you are using explicit transactions...don't.

     

    A.D.T.

    Monday, November 3, 2008 1:46 PM
  • The best way to isolate a deadlock and the specific cause is to use the deadlock graph from one of the methods listed in the following articles:

     

    Troubleshoot Deadlocking in SQL Server.
    Troubleshoot Deadlocking in SQL Server 2005 using Profiler.

     

    Once you have the graph, you can post the information from it here, and we can better help you identify the specific cause of the deadlock, and offer ideas for how to resolve it.

     

    One thing to note is that if you catch the 1205 error, you can resubmit the batch/operation, and it will likely succeed since lock resources are often held only for a few microseconds.

    Monday, November 3, 2008 3:17 PM
    Moderator
  • The below xml which i captured from profiler for your information. UPDATE TDataCaptureStatus_10MinData SET DataCaptureLogVOB = DataCaptureCount, DataCaptureGenerationStatusVOB = CASE WHEN DCT.DataCapturePeriodStart < CONVERT ( DATETIME, CONVERT ( VARCHAR, CURRENT_TIMESTAMP, 101 ), 120 ) THEN 1 ELSE 0 END, ModifiedTimeStampVOB = CURRENT_TIMESTAMP FROM T_DCP_CalculateRemote10MinDataCapture_438627_1 AS DCT WHERE ( DataType = 100 ) AND ( TDataCaptureStatus_10MinData.ParkUnitId = DCT.ParkUnitId ) AND ( TDataCaptureStatus_10MinData.DataCapturePeriodStart = DCT.DataCapturePeriodStart ) sp_executesql EXEC @WMS_Error = SP_EXECUTESQL @SQLString EXEC @WMS_Error = WMS_SP_DCR_Update10MinDataCapture @LocalDCTableName, @DataCaptureSource EXEC WMS_SP_DCR_Calculate10MinDataCaptureVOB @DataCaptureTemplateId = 1, @ParkId = 249 EXEC WMS_SP_DCR_Calculate10MinDataCaptureVOB @DataCaptureTemplateId = 1, @ParkId = 249 UPDATE TDataCaptureStatus_10MinData SET DataCaptureVOB = DataCaptureCount, DataCaptureGenerationStatusVOB = CASE WHEN DCT.DataCapturePeriodStart < CONVERT ( DATETIME, CONVERT ( VARCHAR, CURRENT_TIMESTAMP, 101 ), 120 ) THEN 1 ELSE 0 END, ModifiedTimeStampVOB = CURRENT_TIMESTAMP FROM T_DCP_CalculateRemote10MinDataCapture_438630_1 AS DCT WHERE ( DataType = 1 ) AND ( TDataCaptureStatus_10MinData.ParkUnitId = DCT.ParkUnitId ) AND ( TDataCaptureStatus_10MinData.DataCapturePeriodStart = DCT.DataCapturePeriodStart ) sp_executesql EXEC @WMS_Error = SP_EXECUTESQL @SQLString EXEC @WMS_Error = WMS_SP_DCR_Update10MinDataCapture @LocalDCTableName, @DataCaptureSource EXEC WMS_SP_DCR_Calculate10MinDataCaptureVOB @DataCaptureTemplateId = 1, @ParkId = 250 UPDATE TDataCaptureStatus_10MinData SET DataCaptureVOB = DataCaptureCount, DataCaptureGenerationStatusVOB = CASE WHEN DCT.DataCapturePeriodStart < CONVERT ( DATETIME, CONVERT ( VARCHAR, CURRENT_TIMESTAMP, 101 ), 120 ) THEN 1 ELSE 0 END, ModifiedTimeStampVOB = CURRENT_TIMESTAMP FROM T_DCP_CalculateRemote10MinDataCapture_438630_1 AS DCT WHERE ( DataType = 1 ) AND ( TDataCaptureStatus_10MinData.ParkUnitId = DCT.ParkUnitId ) AND ( TDataCaptureStatus_10MinData.DataCapturePeriodStart = DCT.DataCapturePeriodStart ) sp_executesql EXEC @WMS_Error = SP_EXECUTESQL @SQLString EXEC @WMS_Error = WMS_SP_DCR_Update10MinDataCapture @LocalDCTableName, @DataCaptureSource EXEC WMS_SP_DCR_Calculate10MinDataCaptureVOB @DataCaptureTemplateId = 1, @ParkId = 250 EXEC WMS_SP_DCR_Calculate10MinDataCaptureVOB @DataCaptureTemplateId = 1, @ParkId = 250
    Tuesday, November 4, 2008 6:36 AM
  • Hi kmr, did you ever get this resolved ?

    Preet

     

    Friday, June 11, 2010 12:21 PM
  • The best way to isolate a deadlock and the specific cause is to use the deadlock graph from one of the methods listed in the following articles:

    Troubleshoot Deadlocking in SQL Server.
    Troubleshoot Deadlocking in SQL Server 2005 using Profiler.

     


    The first link mentions trace flags, however these only seem useful for deadlocking of data pages/rows, not for resources such as worker threads/memory etc. The trace flags in this case do not provide (seemingly) any useful info.
    Our SQL instance was upgraded from 2000 to 2005 and the default value is set to 255 for max worker threads, whereas a new install of SQL 2005 would set it to 0. Perhaps I should change this to 0, any suggestions  ?

    Preet

     

    Friday, June 11, 2010 12:30 PM
  • I set max worker threads to zero & restarted SQL. Seems to have had no effect as we still have the same problem. I can only presume it is to do with Communication Buffer Resources. So I ran Perf Mon with all possible counters that could be relevant. Nothing too unusual spotted.

    Preet

    Friday, June 25, 2010 10:46 AM