在仅附加表中设置版本列 [英] Setting version column in append only table

问题描述

我们有一个表来存储记录的版本.

We have a table that will store versions of records.

列是:

Id (Guid)
VersionNumber (int)
Title (nvarchar)
Description (nvarchar)
etc...

保存项目将在表中插入一个新行，该行具有相同的 Id 和递增的 VersionNumber.

Saving an item will insert a new row into the table with the same Id and an incremented VersionNumber.

我不确定如何最好地生成连续的 VersionNumber 值.我最初的想法是:

I am not sure how is best to generate the sequential VersionNumber values. My initial thought is to:

SELECT @NewVersionNumber = MAX(VersionNumber) + 1
FROM VersionTable
WHERE Id = @ObjectId

然后在我的插入语句中使用@NewVersionNumber.

And then use the the @NewVersionNumber in my insert statement.

如果我使用这种方法，是否需要将我的事务设置为可序列化以避免并发问题?我不想最终得到相同 ID 的重复版本号.

If I use this method do I need set my transaction as serializable to avoid concurrency issues? I don't want to end up with duplicate VersionNumbers for the same Id.

有没有更好的方法来做到这一点，而不是让我使用可序列化的事务?

Is there a better way to do this that doesn't make me use serializable transactions?

推荐答案

为了避免并发问题(或在您的特定情况下重复插入)，您可以创建一个复合键作为表的主键，由 ID 组成和 VersionNumber 列.然后，这将对键列强制执行唯一约束.

In order to avoid concurrency issues (or in your specific case duplicate inserts) you could create a Compound Key as the Primary Key for your table, consisting of the ID and VersionNumber columns. This would then enforce a unique constraint on the key column.

随后，您的插入例程/逻辑可以设计为处理或捕获由于重复键导致的插入错误，然后简单地重新发出插入过程.

Subsequently your insert routine/logic can be devised to handle or rather CATCH an insert error due to a duplicate key and then simply re-issue the insert process.

还值得一提的是，除非您特别需要使用 GUID，即因为使用 SQL Server 复制或多个数据源，否则您应该考虑使用替代数据类型，例如 BIGINT.

It may also be worth mentioning that unless you specifically need to use a GUID i.e. because of working with SQL Server Replication or multiple data sources, that you should consider using an alternative data type such as BIGINT.

这篇关于在仅附加表中设置版本列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！