title: SQL Server: preventing dirty reads in a stored procedure
body: <p>Consider a SQL Server database and its two stored procs:</p>
  
  <p>*1. A proc that performs 3 important things in a transaction: Create a customer, call a sproc to perform another insert, and conditionally insert a third record with the new identity.</p>
  
  
  
  <pre><code>BEGIN  TRAN
      INSERT INTO Customer(CustName) (@CustomerName)
      SELECT @NewID = SCOPE_IDENTITY()
  
      EXEC  CreateNewCustomerAccount @NewID, @CustomerPhoneNumber
  
      IF @InvoiceTotal &gt; 100000
           INSERT INTO  PreferredCust(InvoiceTotal, CustID) VALUES (@InvoiceTotal, @NewID)
  
  COMMIT TRAN
  </code></pre>
  
  
  
  <p>*2. A stored proc which polls the <code>Customer</code> table for new entries that <strong>don't</strong> have a related <code>PreferredCust</code> entry. The client app performs the polling by calling this stored proc every 500ms.  The SELECT onto the <code>Customer</code> does NOT involve a transaction.</p>
  
  <pre><code>  --not in the Preferred list
     SELECT C.ID
     FROM Customer    AS C
     LEFT JOIN PreferredCust AS PRE ON PRE.CustID = C.ID
     WHERE PRE.CustID IS NULL  
  </code></pre>
  
  <p>A problem has arisen where the polling stored procedure has found an entry in the <code>Customer</code> table, and returned it as part of its results. The problem was that it has picked up that record, I am assuming, as part of a dirty read. The record ended up having an entry in <code>PreferredCust</code> later, and ended up creating a problem downstream.</p>
  
  <p><strong>Question</strong></p>
  
  <ul>
  <li>How can you explicitly prevent dirty reads by that second stored proc?</li>
  <li>How likely is my assumption of the dirty read scenario?</li>
  </ul>
  
  <p>The environment is SQL Server 2005 with the default configuration out of the box. No other locking hits are given in either of these stored procedures.</p>
  
  <p>These two stored procs are being called from a Java client via a JDBC connection. It's unknown whether they are using the same connection, but SQL Profiler shows that they're using the <strong>same SPID and ClientProcessID</strong>.</p>
  
  <p>Here's what SQL Profiler shows:</p>
  
  <pre><code>SELECT @@MAX_PRECISION
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
  SET IMPLICIT_TRANSACTIONS OFF
  SET QUOTED_IDENTIFIER ON
  SET TEXTSIZE 2147483647
  go
  EXEC WriteNewCustomer  'CustomerX', 199000
  go
  
  --get any customers in the priority 
  SELECT @@MAX_PRECISION
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
  SET IMPLICIT_TRANSACTIONS OFF
  SET QUOTED_IDENTIFIER ON
  SET TEXTSIZE 2147483647
  go
  EXEC GetCustomersWithLowInvoice
  go
  </code></pre>