I have a basic locking question. While one user is updating data, I want other users to be able to read the data—but not dirty data. For example, say User A issues the command
USE Northwind GO BEGIN TRANSACTION UPDATE Region WITH Set RegionDescription = 'South' WHERE RegionID = 4
At this stage, User A hasn't closed the transaction, so it's been neither committed nor rolled back. With User A's transaction still open, User B then issues the command
USE Northwind GO SELECT * FROM Region
Right now, in our implementation, User B goes into a blocking situation. But I want User B to be able to read the data as it was before User A's update.
I tried using the UPDLOCK hint with the UPDATE statement but found that User B's command still hits a blocking situation. The only way I can execute User B's command is to use either NOLOCK or READPAST. But neither hint serves my purpose. NOLOCK gives me a dirty read, and READPAST ignores the locked records and shows only unlocked records. Is there any way to allow User B to read data that User A is in the process of updating?
The functionality you're asking for, called snapshot concurrency, is a new feature we're providing in SQL Server 2005. Snapshot concurrency lets users see the version of data that existed when the snapshot transaction started. However, a big debate surrounds this feature in the database community; many people argue that a record should be locked until the database system commits the transaction or rolls it back. But for business purposes, many organizations want the behavior you describe, and that's why we're adding it in the next release of SQL Server. If you want to experiment with this functionality, download Beta 2 of SQL Server 2005 Express at http://www.microsoft.com/sql/express/.