title: Can I use C# locks to prevent SQL Server deadlocks in my web application?
body: <p>I am writing a web application in Visual Studio 2010 using C#. The web app executes complex SQL Server 2008 statements that sometimes result in a deadlock if the same .aspx page is called more than once at the same time. The proposed solution is to use SQL server means to prevent these deadlocks, but my issue is that I don't really understand it well, which is not true about C#, that I know way better.</p>
  
  <p>So I'm wondering, what is the downside of me using locks in the ASP.NET page (or locks in C#, or Windows named mutex) instead of doing the locking through SQL server to prevent these deadlocks?</p>
  
  <p>PS. The SQL Server database in question is used only by this web application.</p>
  
  <p><strong>EDIT:</strong> The following is C# code that executes SQL statements:</p>
  
  <pre><code>int iNumRows = 0;
  
  using (SqlConnection cn = new SqlConnection(strConnection))
  {
      cn.Open();
  
      using (SqlCommand cmd = new SqlCommand(strSQL, cn))
      {
          //Use C# lock here
          iNumRows = Convert.ToInt32(cmd.ExecuteScalar());
          //Release C# lock here
      }
  }
  </code></pre>
  
  <p>And here's a sample of SQL (that in reality is dynamically composed by C# script):</p>
  
  <pre><code>SET XACT_ABORT ON;
  BEGIN TRANSACTION;
  
  DELETE FROM [dbo].[t_Log_2] 
        WHERE [idtm]&lt;'2011-03-12 08:41:57';
  
  WITH ctx AS(
       SELECT MIN([idtm]) AS mdIn, 
              MAX([odtm]) AS mdOut 
             FROM [dbo].[t_Log_2] 
            WHERE [type] = 0 
              AND [state] = 0 
              AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4' 
              AND [odtm] &gt;= '2013-03-11 06:33:32' 
              AND [idtm] &lt;= '2013-03-11 06:43:12' 
             ) 
  INSERT INTO [dbo].[t_Log_2] 
  ([oid],[idtm],[odtm],[type],[state],[huid],
   [cnm],[cmdl],[batt],[dvtp0],[dvtp1]) 
  SELECT 
      2, 
      CASE WHEN mdIn IS NOT NULL 
            AND mdIn &lt; '2013-03-11 06:33:32' 
           THEN mdIn 
           ELSE '2013-03-11 06:33:32' 
           END,
      CASE WHEN mdOut IS NOT NULL 
            AND mdOut &gt; '2013-03-11 06:43:12' 
           THEN mdOut 
           ELSE '2013-03-11 06:43:12' 
           END,
      0,
      0,
      N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4',
      null,
      null,
      0,
      1,
      null 
  FROM ctx 
  
  SELECT ROWCOUNT_BIG()
  
  DELETE FROM [dbo].[t_Log_2] 
        WHERE [type] = 0 
          AND [state] = 0 
          AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4' 
          AND [odtm] &gt;= '2013-03-11 06:33:32' 
          AND [idtm] &lt;= '2013-03-11 06:43:12' 
          AND [id] &lt;&gt; SCOPE_IDENTITY()
  
  DELETE FROM [dbo].[t_Log_2] 
        WHERE [type] = 0 
          AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4' 
          AND [idtm] &gt;= (SELECT [idtm] FROM [dbo].[t_Log_2] 
                                      WHERE [id] = SCOPE_IDENTITY()) 
          AND [odtm] &lt;= (SELECT [odtm] FROM [dbo].[t_Log_2] 
                                      WHERE [id] = SCOPE_IDENTITY()) 
          AND [id] &lt;&gt; SCOPE_IDENTITY() 
  
  ;WITH ctx1 AS( 
       SELECT [idtm] AS dI 
         FROM [dbo].[t_Log_2] 
        WHERE [id] = SCOPE_IDENTITY() 
               )
  UPDATE [dbo].[t_Log_2] 
          SET [odtm] = ctx1.dI 
         FROM ctx1 
        WHERE [id] &lt;&gt; SCOPE_IDENTITY() 
          AND [type] = 0 
          AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4' 
          AND [idtm] &lt; ctx1.dI 
          AND [odtm] &gt; ctx1.dI 
  
  ;WITH ctx2 AS(
       SELECT [odtm] AS dO 
         FROM [dbo].[t_Log_2] 
        WHERE [id] = SCOPE_IDENTITY() 
               ) 
  UPDATE [dbo].[t_Log_2] 
          SET [idtm] = ctx2.dO 
         FROM ctx2 
        WHERE [id] &lt;&gt; SCOPE_IDENTITY() 
          AND [type] = 0 
          AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4' 
          AND [idtm] &lt; ctx2.dO 
          AND [odtm] &gt; ctx2.dO 
  
  COMMIT TRANSACTION;
  SET XACT_ABORT OFF
  </code></pre>