您会为SQL Server推荐哪种乐观锁定方案？

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:

我正在使用.Net 1.1和SQL Server 2000构建企业应用程序。我使用读取提交的隔离级别。然而,非功能性要求的变化使得有必要针对不可重复的读取和幻像采取措施。我看到两个选择:

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.

引入行版本控制以检查在事务中读取行之后是否已修改行。这是通过向表abd添加VersionId列来完成的,每次更改行时都会增加值。这将解决问题,但要求我们重写我们的应用程序的所有存储过程和数据访问层。
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.

迁移到SQL Server 2005并使用快照隔离级别。这样可以省去重写代码的麻烦,但是有一些挑战:a。快速隔离级别在.Net 1.1中是未知的,因此我们必须额外往返服务器以手动设置它。湾我们无法在存储过程中使用临时表,因为快照隔离级别不允许更改tempdb的模式。我不知道如何解决这个问题。

Any ideas or suggestions are more than wellcome

任何想法或建议都不仅仅是好的

2 个解决方案

#1

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.

我建议你升级到2005/2008并启用read committed snapshot选项。启用后,它会使任何要求读取已提交的事务使用行版本控制而不是锁定。与完全快照隔离相比,它对TempDB的影响也更小。

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.

你对临时表和快照有什么问题?我可以在快照隔离中创建临时表而不会出现问题。我所知道的唯一限制是你使用的是全局临时表。要使全局临时表起作用,tempDB必须启用ALLOW_SNAPSHOT_ISOLATION,或者必须在查询中添加锁定提示以将语句更改为另一个隔离级别。

#2

Some thoughts on option #2:

关于选项#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.

您不需要额外往返服务器。我可以想到避免它的3种不同方法,包括将命令预先挂起到现有的查询字符串,转移到存储过程并在那里设置等等。另外,你可以绕过SQL Server 2005并直接进入SQL Server 2008 ,现在出来了。

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

如果使用选项#1,请查看使用versionid列的timestamp数据类型。

#1

#2