問題描述

我們有一個表來存儲記錄的版本.

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.

這篇關于在僅附加表中設置版本列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！