[Dbix-class] Optimistic locking

[Dan Horne]

> On Behalf Of Steven Mackenzie
> 
> Dan Horne wrote:
> 
> >>On Behalf Of Zbigniew Lukasiak
> >>
> >>Hi,
> >>
> >>There will be race conditions - is that acceptable for you?  See
> following
> >>
> >>
> >scenario:
> >
> >
> >>Person A wants to save new record values
> >>Person B wants to save new record values
> >>A fetches the record to compute the hash
> >>B fetches the record to compute the hash
> >>The record has not been changed yet so both A and B are ready to save
> >>
> >>
> >their new values
> >
> >
> >>A saves her new values
> >>B saves her new values
> >>
> >>The result is that the values saved by A are overwritten by B without
> any
> >>
> >>
> >warning.
> >
> >
> >>It would be safe if we could do a
> >>
> >>UPDATE rec ... WHERE (id = our_id AND hash = the_old_hash_val)
> >>
> >>This would be a real optimistic locking using the database mechanisms to
> >>
> >>
> >eliminate the race conditions.
> >
> >
> >>--
> >>Zbyszek
> >>
> >>
> >>
> >Yes, I've been thinking about this in the meantime and have decided to
> add a
> >version column to each table and do something similar to what you
> suggested
> >
> >UPDATE rec set col1 = <col1>, col2 = <col2> ... version = version + 1 ...
> >WHERE conditions and version = old_version_value
> >
> >Dan
> >
> >
> >
> Why do you need the version column? Doing it exactly as Zbigniew
> Lukasiak suggested solves the problem without having to add columns to
> my DB schema to support optimistic locking. I think that would be much
> neater. It would allow two users to write the SAME data to the DB after
> one another, but what's the problem with that?

Well if I can't add an extra column (either hash or version), wouldn't I
need to read the whole row and recompute the original hash with every update
or delete to every single row? Sounds expensive to me, which is why I moved
away from using hashes as I originally proposed.

> It is also how the optimistic locking in ADO.Net DataSets work.
> 
The alternative version column approach is a common optimistic locking
design pattern and is supported by Hibernate and Toplink, for instance. I
guess it comes down to whichever ORM you wish to cite...