title: How to resolve SQL Server deadlocks - once changing order and shortening queries is exhausted?
body: <p>i have two hypothetical queries:</p>
  
  <pre><code>UPDATE BankAccounts SET HomePhone = '+1 252-555-0912' 
  WHERE AccountNumber = 14400000619
  </code></pre>
  
  <p>and</p>
  
  <pre><code>SELECT * FROM BankAccounts 
  WHERE HomePhone = '555-1212'
  </code></pre>
  
  <p>on a hypothetical table with no extra indexes:</p>
  
  <pre><code>CREATE TABLE BankAccounts 
  ( 
     AccountNumber bigint NOT NULL PRIMARY KEY CLUSTERED,
     FirstName nvarchar(50) NOT NULL,
     MiddleName nvarchar(50) NULL,
     LastName nvarchar(50) NOT NULL,
     HomePhone varchar(50) NULL,
     IsClosed tinyint DEFAULT 0
  )
  </code></pre>
  
  <p>and everything will be great. If i add an index on <code>HomePhone</code>:</p>
  
  <pre><code>CREATE INDEX IX_BankAccounts_HomePhone ON BankAccounts 
  ( HomePhone)
  </code></pre>
  
  <p>Now my <code>SELECT</code> statement can be a <strong>deadlock</strong> victim:</p>
  
  <blockquote>
    <p>Tranasction (Process ID 169) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.</p>
  </blockquote>
  
  <p>The common suggestions are to:</p>
  
  <ul>
  <li>access tables in the same order</li>
  <li>keep transactions as short as possible</li>
  </ul>
  
  <p>Except in this case:</p>
  
  <ul>
  <li>i am access the (one) table in the same order (1 choose 1 is 1)</li>
  <li>the transactions are a <em>single statement</em>; i cannot get any shorter than that</li>
  </ul>
  
  <p>What's the long-term solution to eliminate deadlocks like this?</p>
  
  <p>i was considering changing my <strong>transaction isolation level</strong> to <code>READ UNCOMMITTED</code> (i.e. eliminating integrity), but because i'm actually dealing with a financial system i'm hesitant to allow a customer to withdraw his entire balance twice.</p>
  
  <p>The only other solution i can find comes from <a href="http://support.microsoft.com/kb/832524">KB Article 83252</a>:</p>
  
  <blockquote>
    <h2>SQL Server technical bulletin - How to resolve a deadlock</h2>
    
    <p>...deadlocks cannot be avoided. That is why the front-end application should be designed to handle deadlocks.</p>
    
    <p>In a well designed application, the front-end application should trap for the 1205 error, reconnect to SQL Server, and then re-submit the transaction. </p>
  </blockquote>
  
  <p>Which i guess is saying: <em>"can't win; don't try"</em></p>
  
  <p>Anything else?</p>