Which Optimistic Concurrency Control method will work out best in the long run?

Question

These are the options I've thought of:

DotNet Dave:

UPDATE mytable set f1=newval where id=666 and f1 := f1ValueFromSelect

Charlie Checksum:

UPDATE mytable set f1=newval where id=666 and md5(concat(id,f1)) := checksumFromSelect

Timothy Timestamp:

UPDATE mytable set f1=newval where id=666 and mytimestampfield := timestampFromSelect

Veronica Version:

UPDATE mytable set f1=newval, version=version+1 where id=666 and version=versionFromSelect

I like Dave, as no schema changes required. Have to keep all of record's original data on client though. How to compare blobs?

Charlie is good in that less data to send to client. Not sure about performance with blobs/clobs

Tim and Veronica are good as pretty simple though may require schema change. Timestamp looks a bit more foolproof. How cross-platform is it?

Thanks!

Answer 1

Depends on your DBMS platform to some extent. On the SQL Server based development I did back in the late Jurassic period we used timestamps as these are automatically updated when the record is written.

You load a record with:

select Foo
      ,Bar
      ,TS
  from FooTab
 where FooID = 666

And save with

update FooTab
   set Foo = @Foo
      ,Bar = @Bar
 where FooID = @FooID
   and TS = @TS

select @@rowcount as RowCount 

... or some such, depending on where the concurrency was being handled.

Oracle doesn't have such a timestamp, so the Oracle stuff I did around the same era worked by incrementing a version counter.

My view about checksumming and comparing field values is it is fiddlier and therefore easier to make a mistake with, so it's best only used when you don't have the option of using a timestamp or version on the record. And, yes, without applying any google-fu to the question, I can't think of a way to do it with BLOBs off the top of my head either. You might be able to do the comparison separately with something like:

... and hashbytes ('MD5', BlobField) = @BlobHash

So, as an opinion, use timestamping or versioning if you have the option - it's much simpler. You can do field comparisons or hashing if you don't have the option of changing the database, but it's more fiddly.