SQL Server: preventing dirty reads in a stored procedure
- by pcampbell
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.
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?
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.
