[odb-users] SELECT FOR UPDATE support

[Boris Kolpackov]

Hi Magnus,

Magnus Granqvist <magnus.granqvist at tailormade.se> writes:

> Does ODB support pessimistic Locking with "SELECT ... FOR UPDATE"?
> With current ORM layer we can specify optimistic or pessimistic mode
> for the database connection. We can also in runtime override this for
> tables we doesn't need to lock (for read only tables for instance).
> For some long running batch processes we doesn't want to get an
> optimistic exception in the middle of the transaction. In this case
> we do select ... for update and waits if given row already is locked.
> 
> When executing code like this we need to specify if the select
> statements will do "for update" or not:
> 
> auto_ptr<person> jane (db.load<person> (jane_id));
>
> odb::result<person_stat> r (db->query<person_stat> ());
> result r (db->query<person> (query::first == "John" &&
>                              query::last == "Doe"));

There is no direct support for this yet (i.e., there is no 
load_for_update() or query_for_update()). The tricky part with
adding such support is that ODB caches and reuses prepared
statements so we cannot just add the FOR UPDATE suffix to the
normal load() statement on demand. Instead, we would have to
prepare and cache a separate statement.

At the same time, it is quite easy to achieve this functionality
with query by simply adding the FOR UPDATE suffix. For example:

result r (db->query<person> (
  (query::first == "John" && query::last == "Doe") + 
  "FOR UPDATE OF" + query::id
));

The OF" + query::id part is only necessary if you have object
relationships (without it all the JOIN'ed rows will also be locked).

We can also emulate load() with query and thus add FOR UPDATE:

result r (
  db.query<person> (
    (query::id == jane_id) + "FOR UPDATE OF" + query::id
));

if (!r.empty ())
  auto_ptr<person> jane (r.begin ().load ());

Boris