Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
You should tag questions like this with your platform - in this case the options available for this vary with the actual DBMS in use. –  ConcernedOfTunbridgeWells Apr 18 '12 at 8:17
    
well, they can all do 1, 3 and 4, and a few can do 2. i am looking for a general solution. –  Neil McGuigan Apr 18 '12 at 8:40
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
thanks. decided to go with a 'version' int that is updated by a trigger (mysql). didn't wanna risk timestamp as it only has 1sec resolution AFAIK –  Neil McGuigan Apr 18 '12 at 3:31
    
Timestamps on SQL Server aren't actually timestamps as such - effectively they're just a number that will always change as the record is written. –  ConcernedOfTunbridgeWells Apr 18 '12 at 8:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.