0

I am using SQL Server 2008 Enterprise. I am wondering whether this stored procedure causes deadlock if executed by multiple threads at the same time? Another question is -- is it best practices we define begin and end transaction inside of the stored procedure, or defining begin and end transaction from client code (e.g. ADO.Net code)?

create PROCEDURE [dbo].[FooProc]    
(  
 @Param1 int 
 ,@Param2 int  
 ,@Param3 int  
)    
AS    

DELETE FooTable WHERE  Param1 = @Param1     

INSERT INTO FooTable    
 (  
 Param1  
 ,Param2  
 ,Param3  
  )    
 VALUES    
 (  
 @Param1  
 ,@Param2  
 ,@Param3  
  )    

DECLARE @ID bigint    
 SET @ID = ISNULL(@@Identity,-1)    
 IF @ID > 0    
 BEGIN    
      SELECT IdentityStr FROM FooTable WHERE ID = @ID 
 END

thanks in advance, George

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
George2
  • 44,761
  • 110
  • 317
  • 455
  • 1
    Will MERGE do what you need in SQL 2008? Also definitely @@Identity should be avoided. Use SCOPE_IDENTIY – Martin Smith Jun 04 '10 at 15:28
  • Yes, I am currently analyzing some issues with legacy code and I need to analyze if deadlock is caused by this store procedure. Any ideas or comments about my original question? – George2 Jun 04 '10 at 15:40
  • " @@Identity should be avoided" -- why? – George2 Jun 04 '10 at 15:41
  • 2
    @@IDENTITY gives the latest identity value, if there is a trigger, you get the identity from that and not from the INSERT in your local scope. SCOPE_IDENTITY() gives the identity value from your local scope. use profiler and deadlock graph to pinpoint what procedures are involved in deadlocks. – KM. Jun 04 '10 at 15:44
  • 1
    @George2 - How many records will typically be deleted by `DELETE FooTable WHERE Param1 = @Param1`? and is there an index on `Param1`? If it needs to get quite a few locks then deadlock could definitely occur in a manner similar to this thread http://stackoverflow.com/questions/2945135/i-have-data-about-deadlocks-but-i-cant-understand-why-they-occur. As KM suggests setting up a trace to get the deadlock graph will be much more productive. – Martin Smith Jun 04 '10 at 16:00
  • Param1 has index but why it matters the count of records to be deleted for deadlock? And what happens if Param1 has no index? – George2 Jun 04 '10 at 16:08
  • 1
    @George2 the way I was reasoning is the greater the number of records affected the more locks there will be and the less granular they will be. – Martin Smith Jun 04 '10 at 16:20
  • "affected the more locks there will be and the less granular they will be" -- why it impacts whether deadlock will happen or not? – George2 Jun 04 '10 at 16:22

2 Answers2

2

The only reliable way to answer your question is to run your own stress tests

A-K
  • 16,804
  • 8
  • 54
  • 74
  • Sorry Alex, the question is not very clear. Please discuss here, which I had a more clearer description. http://stackoverflow.com/questions/2981121/why-single-sql-delete-statement-will-cause-deadlock – George2 Jun 05 '10 at 16:31
1

The code you have given could cause deadlock. Even if the stored procedure purely consisted of the following statement deadlock could occur.

   DELETE FooTable WHERE  Param1 = @Param1

Depending upon the exact table definition and indexes available (which you have left out of your question).

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Why consisting of this delete statement will cause deadlock? Any more details? I want to learn why it matters whether there is index on Param1 for deadlock? – George2 Jun 04 '10 at 16:11
  • 1
    Well the way I was thinking was suppose that invocation 1 of the stored procedure ends up getting page locks on pages x,y,z but needs page a. Invocation 2 of the stored procedure ends up getting page locks on pages a,b,c but needs page x then deadlock would ensue. – Martin Smith Jun 04 '10 at 16:14
  • Can you show me sample in my context please? :-) e.g. in the delete statement, page x,y,z and a, b, c are for what page? Why a single delete statement will involve so many pages? – George2 Jun 04 '10 at 16:21
  • 1
    @George2 We have no idea how many pages will be affected by it as you haven't told us! – Martin Smith Jun 04 '10 at 16:23
  • If only one record and Param1 is index, what will happen? And if more than one record will be impacted and Param1 is not indexed, what will happen? I do not want to solve this issue itself, but want to learn from you guru guys how to analyze this deadlock issue in different scenarios? – George2 Jun 04 '10 at 16:25
  • 1
    I am most definitely not a deadlock guru! Check out Remus Rusanu's posts for that http://stackoverflow.com/search?q=user%3A105929+deadlock – Martin Smith Jun 04 '10 at 16:35
  • I did not find similar scenarios to my case, like why a single delete statement (as you quoted) will cause deadlock? – George2 Jun 04 '10 at 16:39
  • 1
    single delete statement or single delete record? If the latter then I can't construct a scenario either where it would happen. If the former then the earlier example I gave would do it (maybe relying on a parallel execution plan just to ensure that the locks weren't issued in a linear order). – Martin Smith Jun 04 '10 at 16:52
  • Sorry Martin, the question is not very clear. Please discuss here, which I had a more clearer description. http://stackoverflow.com/questions/2981121/why-single-sql-delete-statement-will-cause-deadlock – George2 Jun 05 '10 at 16:29