title: sql server 2005 deadlock times out in production, not in test environment: why?
body: <p>In my development environment, I seek to recreate a production issue we
  face with MSSQL 2005. This issue has two parts:</p>
  
  <p><strong>The Problem</strong></p>
  
  <p>1) A deadlock occurs and MSSQL selects one connection ("Connection X") as the 'victim'.
  2) All subsequent attempts to use "Connection X" fail (we use connection pooling). MSSQL says "The server failed to resume the transaction"</p>
  
  <p>Of the two, #2 if more serious: since "connection X" is whacked every
  "round robin" attempt to re-use "connection x" fails--and mysterious
  "random" errors appear to the user. We must restart the server.</p>
  
  <p><strong>Why I Write</strong></p>
  
  <p>At this point, however, I wish to recreate problem #1. I can create a
  deadlock easily.</p>
  
  <p>But here's my issue: whereas in production, MSSQL chooses one
  connection (SPID)  as the 'deadlock victim', in my test environment, the deadlock just hangs...and hangs and hangs. Forever? I'm not sure, but I left it hanging overnight and it still hung in the morning.</p>
  
  <p>So here's the question: <strong>how can I make sql server "choose a deadlock victim" when a deadlock occurs?</strong> </p>
  
  <p><strong>Attempts so Far</strong></p>
  
  <p>I tried setting the "lock_timeout" parameter via the jdbc url ("lockTimeout=5000"), however I got a different message than in production (in test,"Lock request time out period exceeded." instead of in production "Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.")</p>
  
  <p><strong>Some details on problem #2</strong></p>
  
  <p>I've researched this "unable to resume the transaction" problem and found a
  few things:</p>
  
  <ul>
  <li>bad exception handling may cause this problem. E.g.: the java code does
  not close the Statement/PreparedStatement and the driver's implementation
  of "Connection" is stuck with a bad/stale/old "transaction ID"</li>
  <li>A jdbc driver upgrade may make the problem go away.</li>
  </ul>
  
  <p>For now, however, I just want to recreate a deadlock and make sql server
  "choose a deadlock victim".</p>
  
  <p>thanks in advance!</p>
  
  <p><strong><em>Appendix A. Technical Environment</em></strong></p>
  
  <p><em>Development:</em></p>
  
  <ul>
  <li>sql server 2005 SP3 (9.00.4035.00)</li>
  <li>driver: sqljdbc.jar version 1.0 </li>
  <li>Jboss 3.2.6</li>
  <li>jdbc url: jdbc:sqlserver://&lt;>;</li>
  </ul>
  
  <p><em>Production:</em></p>
  
  <ul>
  <li>sql server 2005 SP2 (9.00.3042.00)</li>
  <li>driver: sqljdbc.jar version 1.0 </li>
  <li>Jboss 3.2.6</li>
  <li>jdbc url: jdbc:sqlserver://&lt;>;</li>
  </ul>
  
  <p><strong><em>Appendix B. Steps to force a deadlock</em></strong></p>
  
  <ul>
  <li>get connection A</li>
  <li>get connection B</li>
  <li>run sql1 with connection A</li>
  <li>run sql2 with connection B</li>
  <li>run sql1 with connection B</li>
  <li>run sql2 with connection A</li>
  </ul>
  
  <p>where
  sql1:
  update member set name = name + 'x' WHERE member_id = 71</p>
  
  <p>sql2:
  update member set name = name + 'x' WHERE member_id = 72</p>