Setting version column in append only table

Question

We have a table that will store versions of records.

The columns are:

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

Saving an item will insert a new row into the table with the same Id and an incremented 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

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

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?

Answer 1

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.

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.

Answer 2

I had thought that the following single insert statement would avoid concurrency issues, but after Heinzi's excellent answer to my question here it turns out that this is not safe at all:

Insert Into VersionTable
(Id, VersionNumber, Title, Description, ...)
Select @ObjectId, max(VersionNumber) + 1, @Title, @Description
From VersionTable
Where Id = @ObjectId

I'm leaving it just for reference. Of course this would work with either table hints or a transaction isolation level of Serializable, but overall the best solution is to use a constraint.