CLR-Stored Procedures > Deadlock

• manuel.pfaffenlehner

  Newbie

  Points: 3

  More actions

  March 20, 2014 at 4:41 am

  #286890

  Hi,

  recently we have increased problems with deadlocks on our SQL Server 2008 (Microsoft SQL Server 2008 (SP1) - 10.0.2766.0 (X64) Feb 25 2010 12:51:37 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)).

  There’s a CLR-Stored Procedure that reads changed or new data from another system over a WebService every 30 minutes (SQL-Agent Job). Within this call we get about 150-200 rows that we either INSERT or UPDATE in a table. There’s a Trigger on the table that sets some flags on the row in the same table and creates an XML-String for log purpose that is written into another table. Overall the whole INSERT/UPDATE process runs approximately 3-4 seconds for every row.

  Besides this Job users have the possibility to call another CLR-Stored Procedure for a specific record that reads the changes from the other system (also via WebService). This call does the same INSERT/UPDATE as the Job (also with the trigger).

  If both procedures try to access the table at the same time, we immediately get a deadlock although different rows are manipulated. Either the Job is the deadlock victim or the single user update.

  If I try to simulate these operations with simple SQL INSERT/UPDATE within two sessions I don’t get a deadlock. Not even if I put a delay of 20 seconds or more in the trigger. The second statement waits until the first has finished.

  For me it seems that CLR-Stored Procedures have a different behavior when there’s a lock on a table than simple SQL-Statements.

  Do you have any suggestions/hints for deeper analyzing?

  Thx in advance.

  Manuel

  PS: If needed I could post the deadlock-graph.