SQL Server: preventing dirty reads in a stored procedure

Question

Consider a SQL Server database and its two stored procs:

*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.

BEGIN  TRAN
    INSERT INTO Customer(CustName) (@CustomerName)
    SELECT @NewID = SCOPE_IDENTITY()

    EXEC  CreateNewCustomerAccount @NewID, @CustomerPhoneNumber

    IF @InvoiceTotal > 100000
         INSERT INTO  PreferredCust(InvoiceTotal, CustID) VALUES (@InvoiceTotal, @NewID)

COMMIT TRAN

*2. A stored proc which polls the Customer table for new entries that don't have a related PreferredCust entry. The client app performs the polling by calling this stored proc every 500ms. The SELECT onto the Customer does NOT involve a transaction.

  --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  

A problem has arisen where the polling stored procedure has found an entry in the Customer 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 PreferredCust later, and ended up creating a problem downstream.

Question

• How can you explicitly prevent dirty reads by that second stored proc?
• How likely is my assumption of the dirty read scenario?

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.

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 same SPID and ClientProcessID.

Here's what SQL Profiler shows:

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

Answer 1

The default isolation level is read committed. Dirty reads cannot occur under that isolation level.

There's probably another cause that you've overlooked.

Answer 2

Put the following at the top of your procedure (or just before the BEGIN TRAN).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 

You could also choose REPEATABLE READ or SERIALIZABLE. That said, I would concur with Andomar in that there is likely another cause to what looks like a change in the isolation level given that the default level is READ COMMITTED.

Answer 3

You can't prevent dirty reads. Writers take exclusive locks to prevent honest, read committed, reads. But there is nothing you can do to prevent dirty reads. The dirty reader has to stop doing dirty reads, period.

Assuming that the code that polls the Customer table is under your control, the solution is to remove the dirty read hint from the query. This will probably cause contention, since the polling will now block on the writes. The best solution for that is to enable row versioning:

ALTER DATABASE [<DBNAME>] SET ALLOW_SNAPSHOT_ISOLATION ON; 
ALTER DATABASE [<DBNAME>] SET READ_COMMITTED_SNAPSHOT ON;

Then simply poll from Customer as a normal query, w/o any hints. Your poll won't block behind writes as row-versioning will kick in and redirect the query scan to a pre-update, non-locked version of the row.

One more note: polling every 500ms? Perhaps you should use a Query Notification mechanism to invalidate your caches, see The Mysterious Notification.