[Dbix-class] Optimistic locking

[Dan Horne]

> On Behalf Of Steven Mackenzie
> >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.
> >
> >
> An option would be to keep original column values in memory (copy on
> change), and then on ->update(), add
> col1=orig_val1 AND col2 = orig_val2 AND ...
> to the SQL update where clause.
> 
> Can your module do that?

Well, it's not written yet ;-). I was hoping to quickly knock up a DBIC
component, but when I started going through the DBIC internals I got a
little stuck.

For optimistic locking to work, I need to know how many rows were updated
(or deleted), but I don't seem to have access to this. (the number of rows
in an update is determined in DBIC::Row, but I don't have access to it). I
could just rely on DBIC::Row throwing its exception when no rows get
updated, but there could be a different exception thrown somewhere else as
part of the transaction. I can't see anywhere in the code that checks for
the number of rows affected by a delete, but maybe I'm just blind.

I'm not sure about your suggestion of keeping things in memory, though. If
an app is stateless, then you'd have to start managing some kind of server
side cache, I guess. In the context of my app, I was just going to keep the
version as a field in my form, which would be accessible when it was
submitted. 

Likewise, I need to be able to inject the "and version = n" condition
automatically, but I'm not sure of the best way to do this as I don't know
how the conditions are stored internally (as some kind of SQL::Abstract
specification?).

> >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...
> >
> >
> >
> True -- I don't have wide experience with such things, but find the
> ADO.Net approach nicely straightforward, and you don't even need to know
> that it's doing it.

Originally, I was hoping to make it transparent too, but I couldn't think of
a performant way to do so. Others may have a better idea regarding this