title: SQL Server 2005 deadlock with nonclustered index
body: <p>Can anybody help me for a deadlock in SQL Server 2005? </p>
  
  <p>For a simple test, I have a table "Book" which has a primary key (id), and a column name. The default index of this primary key is <strong>nonclustered</strong>.</p>
  
  <p>The deadlock happens when two sessions run at a same time. Activity monitor shows the first session "//step 1" locks the row(rid lock) with X lock. The second session keeps row U lock and key U lock. The deadlock picture shows "//step2" of first session requires key U lock.</p>
  
  <p>If the index is <strong>clustered</strong>, there is no deadlock in this case. "//step 1" will keep the row and key lock at same time, so there is no problem. I can understand locking a row will also lock the index since leaf node of clustered index is row data. </p>
  
  <p>But, why <strong>nonclustered</strong> index is in this way?  If the second session holds the key U lock,  why "step 1" of first session does not hold this lock since they are same the update statement.</p>
  
  <pre><code>--// first session
  BEGIN TRAN
    update Book set name = name where id = 1 //step 1
    WaitFor Delay '00:00:20'
    update Book set name = 'trans' where id = 1 //step2
  COMMIT
  
  --// second session
  BEGIN TRAN
  --// this statement will keep both RID(U lock) and KEY(U lock) if first session did not use HOLDLOCK
    update Book set name = name where id = 1
  COMMIT
  </code></pre>