Deadlock problem with TEXT/NTEXT/VARCHAR(MAX)/NVARCHAR(MAX)

Question

Hi, we ran into a situation where a single process deadlock itself indefinitely.  The situation is:

• Try to update the currently fetched record
• WHEN the SELECT query has an ORDER BY clause
• AND the resultset contains a minimum of records
• AND an CLOB (TEXT/VARCHAR(MAX)) column is part of the resultset

Basically, the scenario is a batch processing job that act like that:

• SELECT all records from Table WHERE record has to be processed ORDER BY priority
• FOR EACH RECORD
  • Process record
  • Mark record as processed (UPDATE table SET processed = 1 where id = ...)

Notes:

• There is no explicit "begin transactions"
• we always use the same connection reference

Here is all the steps to reproduce the problem in a controled environment:

• Create a test table using this script:
  

  CREATE TABLE [dbo].[channel]
  (
      [id] [int] NOT NULL,
      [title] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [description] [VARCHAR](MAX) COLLATE Latin1_General_CS_AS NULL,
      [rank] [int] NULL,
      [mustGenerate] [bit] NULL,
      CONSTRAINT [PK_channel] PRIMARY KEY CLUSTERED  ([id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  ) ON [PRIMARY]
  

• Create a PHP script (ex: sqlsrv_test.php) that will accept a "step" parameter as HTTP GET
• Paste the following code:
  

  function getConnection()
  {
      //TODO: ENTER YOUR DATABASE CONNECTION INFORMATION HERE
      $serverName = "xxx.xxx.xxx.xxx";
      $connectionInfos = array('Database' => 'SQLPHP', 'UID' => 'user', 'PWD' => 'password');
      $conn = sqlsrv_connect($serverName, $connectionInfos);
      return ($conn);
  }
  
  function fillDatabase($conn, $startId, $endId)
  {
      $sql = "INSERT INTO channel (id, title, description, rank, mustGenerate) VALUES (?, ?, ?, ?, ?)";
      for ($id = $startId; $id <= $endId; $id++)
      {
          $params = array($id, 'title ' . $id, 'description ' . $id, $id * 10, 1);
          sqlsrv_query($conn, $sql, $params);
      }
      echo "Database filled with IDs from $startId to $endId<br/>";
  }
  
  function testQueryLock($conn)
  {
      $query = "SELECT * FROM channel ORDER BY rank ASC";
      $stmt = sqlsrv_query($conn, $query);
      $count = 0;
      $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
      while (false !== $row)
      {
          $count++;
          $id = $row['id'];
          if (!empty($id))
          {
              echo $id;
              //NOTE: THIS IS THE UPDATE THAT WILL EVENTUALLY END-UP CREATING A DEADLOCK
              $update = "UPDATE channel SET mustGenerate = 0 WHERE id = $id";
              $updateResult = sqlsrv_query($conn, $update);
              if ($updateResult)
                  echo " - ok <br/>";
              else
                  echo " = FAILED <br/>";
          }
  
          $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
      }
      echo "count = $count";
  
      sqlsrv_free_stmt($stmt);
  }
  //---------------------------------------------------------------
  if ('1' == $_GET['step'])
  {
      $conn = getConnection();
      fillDatabase($conn, 1, 200);
      sqlsrv_close($conn);
  }
  //---------------------------------------------------------------
  if ('2' == $_GET['step'])
  {
      $conn = getConnection();
      testQueryLock($conn);
      sqlsrv_close($conn);
      echo "Success, not enough record in resultset to generate the problem!";
  }
  //---------------------------------------------------------------
  if ('3' == $_GET['step'])
  {
      $conn = getConnection();
      fillDatabase($conn, 201, 500);
      sqlsrv_close($conn);
  }
  //---------------------------------------------------------------
  if ('4' == $_GET['step'])
  {
      $conn = getConnection();
      testQueryLock($conn);
      sqlsrv_close($conn);
      echo "This will never get echoed!";
  }
  
  

To run the test just navigate each step:

• http://localhost/sqlsrv_test.php?step=1
  => database filled with 200 records
• http://localhost/sqlsrv_test.php?step=2
  => success
• http://localhost/sqlsrv_test.php?step=3
  => database filled with additional records
• http://localhost/sqlsrv_test.php?step=4
  => script is never ending. Deadlock in driver.

Now if you remove the "ORDER BY rank ASC" in the testQueryLock function and repeat step 4 you will see the string "This will never get echoed!" being echoed.

Again, (put back the "ORDER BY rank ASC" if you removed it) and run this and execute step 1 to 4, that will still work:

DELETE FROM channel
ALTER TABLE channel ALTER COLUMN description VARCHAR(250)

I did test with TEXT, NTEXT, VARCHAR(MAX), NVARCHAR(MAX) and all these types end-up creating the deadlock situation.

Running Environment:

• Windows XP Professional
• ZendCore 2.5.2 (PHP 5.2.6, Zend Engine 2.2.0, Apache 2.2.10)
• SQLSRV 1.0.1924

I remember getting this problem with a Wamp Installation over Windows XP and using SQLSRV 1.0.1307

Answer 1

Thanks for the detailed and thorough report.  We will look into this as soon as possible.  Detailed reports such as this make it very possible to isolate the problem quickly.  While I cannot promise anything of course, please keep an eye out for our next release.  We try to make releases of cumulative updates (bug fixes, etc.) available in a timely manner.

If you need a quicker solution, there are two avenues: 1) contact our CSS department, and they can elevate the request or 2) please feel free to use our source code available at http://www.codeplex.com/SQL2K5PHP.  Disclaimer: even if you were to locate and fix the issue yourselves, we are not legally able to accept patches or fixes.

Thanks for reporting this problem.  We will look into this as soon as possible.

Jay

---

Jay Kint MSFT

Answer 2

Hi DreamDevil,

This is actually by design. Unfortunately we can make no special promises about locking and etc. when requests run under separate transactions, even with MARS enabled. Lob columns introduce special locking semantics, owing to the fact that lobs can't be stored entirely on-row.

To avoid the deadlock in this scenario, you can either:
1) run both requests under the same transaction. *recommended*
2) remove the the primary key constraint
3) remove the lob column
4) remove the order-by

[1] is the recommended solution. When you share the transaction you share locks, and deadlocks become much less of an issue. Obviously this not going to work in all scenarios though.

Thanks,
Leigh Stewart

Answer 3

Here is a MSDN documentation on MARS which also discusses the scenarios which can result in a deadlock and ways to prevent those.
http://msdn.microsoft.com/en-us/library/ms345109(SQL.90).aspx


Thanks
Vineet Chaudhary MSFT