Setting the version column in the append table only

Question

We have a table in which versions of records will be stored.

Columns:

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

Saving an item will insert a new row into the table with the same identifier and incremental version number.

I'm not sure how best to create sequential VersionNumber values. My initial thought:

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

And then use @NewVersionNumber in my insert statement.

If I use this method, do I need to set my transaction as serializable to avoid concurrency issues? I do not want to duplicate VersionNumbers for the same Id.

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

Answer 1

To avoid concurrency problems (or in your particular case of duplicate inserts), you can create a composite key as the main key for your table, consisting of columns ID and version Number. Then it enforces a unique constraint for the key column.

Subsequently, your insertion procedure / logic may be designed to handle, or rather, CATCH, insertion errors due to duplicate keys, and then simply re-execute the insertion process.

It is also worth noting that if you do not need to specifically use the GUID, that is, due to work with SQL Server replication or multiple data sources, you should consider using an alternative data type, such as BIGINT.

Answer 2

I thought that the following syntax instruction would avoid concurrency problems, but after Heinzi answered 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 leave this for reference only. Of course, this will work with table hints or a transaction isolation level of Serializable, but in general, the best solution is to use a constraint.