What optimistic locking scheme would you recommend for SQL Server?

Question

I am building an enterprise application with .Net 1.1 and SQL Server 2000. I use the read committed isolation level . However changes in non-functional requirements have made it necessary to take measures against non-repeatable reads and phantoms. I see two options:

1. Introduce row-versioning to check if a row has been modified since it was read within a transaction. This is done by adding a VersionId column to tables abd incrementing the value whenever the row is changed. This would solve the problem but require us to rewrite all stored procedures and the data access layer of our applications.

2. Migrate to SQL Server 2005 and use the snapshot isolation level. This would save us the trouble of rewriting code, but there are a few challenges: a. The snapshot isolation level is not known in .Net 1.1, so we must take an extra round trip to the server to set it manually. b. We cannot make use of temporary tables in our stored procedures because the snapshot isolation level does not allow changes to the schema of the tempdb. I'm not sure how to around this.

Any ideas or suggestions are more than wellcome

Answer 1

Some thoughts on option #2:

You wouldn't need to make an extra round-trip to the server. I can think of about 3 different ways to avoid it, including pre-pending the command to your existing query string, moving to stored procedures and setting it there, etc.
Also, you could bypass SQL Server 2005 and go straight to SQL Server 2008, which is out now.

If you go with option #1, look at using the timestamp datatype for your versionid column.

Answer 2

I'd suggest you upgrade to 2005/2008 and enable the read committed snapshot option. Once enabled, it makes any transaction that asks for read committed use row versioning instead of locks. It also has less of an impact on TempDB than full snapshot isolation does.

What problems are you having with temp tables and snapshot? I can create temp tables in snapshot isolation without a problem. The only limitation I know of is if you're using global temp tables. For global temp tables to work either tempDB must have ALLOW_SNAPSHOT_ISOLATION enabled, or a locking hint must be added to the query to change the statement to another isolation level.