問題描述
我們有一個表來存儲記錄的版本.
We have a table that will store versions of records.
列是:
Id (Guid)
VersionNumber (int)
Title (nvarchar)
Description (nvarchar)
etc...
保存項(xiàng)目將在表中插入一個新行，該行具有相同的 Id 和遞增的 VersionNumber.
Saving an item will insert a new row into the table with the same Id and an incremented VersionNumber.
我不確定如何最好地生成連續(xù)的 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.
如果我使用這種方法，是否需要將我的事務(wù)設(shè)置為可序列化以避免并發(fā)問題?我不想最終得到相同 ID 的重復(fù)版本號.
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.
有沒有更好的方法來做到這一點(diǎn)，而不是讓我使用可序列化的事務(wù)?
Is there a better way to do this that doesn't make me use serializable transactions?
推薦答案
為了避免并發(fā)問題(或在您的特定情況下重復(fù)插入)，您可以創(chuàng)建一個復(fù)合鍵作為表的主鍵，由 ID 組成和 VersionNumber 列.然后，這將對鍵列強(qiáng)制執(zhí)行唯一約束.
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.
隨后，您的插入例程/邏輯可以設(shè)計(jì)為處理或捕獲由于重復(fù)鍵導(dǎo)致的插入錯誤，然后簡單地重新發(fā)出插入過程.
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，即因?yàn)槭褂?SQL Server 復(fù)制或多個數(shù)據(jù)源，否則您應(yīng)該考慮使用替代數(shù)據(jù)類型，例如 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.
這篇關(guān)于在僅附加表中設(shè)置版本列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！