Re: [Hibernate-devel] Pessimistic locking?

[Gavin_King/Cirrus%<CI...@ci...>]

> Without doing this you effectively get optimistic lock behaviour. That
> is, you find out at commit time whether everything is okay or not.
> Because the O/R layer will defer all SQL insert/update/delete statements
> until the last second, we don't have any exclusive locks as far as the
> underlying DB is concerned until that point, which from the Hibernate
> callers perspective is commit time.

Okay, heres what I mean by pessimistic/optimistic (you seem to be working
from a different definition).

pessimistic locking:

Retrieval and update of data occurs in one transaction. the database is
solely responsible for ensuring transaction isolation.

Take sysbase, set transaction isolation to serializable. start two
transactions. select the same rows from both transactions. Then, from one
transaction, try to do an update. That transaction will block, waiting to
see what the other transaction tries to do.

Whats happened is that both transactions acquire a read lock when you issue
the select. Any attempts to acquire a write lock (eg. the escalation when
you try to update) will block waiting for the other transaction to release
its read lock.

optimistic locking:

We retrieve the data in one transaction and then update it in another. To
ensure consistency, we check version numbers/timestamps/etc when we update.
In the previous example, the first transaction would have been allowed to
proceed with the update, since there was *no* lock held by the other
transaction. The second would have failed to update because its
timestamp/version number would be stale.

So the distinction between selection with or without "FOR UPDATE" (ie.
between a read lock and a write lock is different to my distinction between
optimistic (no lock) and pessimistic locking.

clearly there are shades of optimism.... :)


> If the exclusive lock is only obtained in the second servlet that
> handles the form being posted a possibility is that it fails. From the
> the user's perspective this is optimistic locking behaviour.

> What you need to be able to do is gain the exclusive lock in the first
> servlet, the one that knows the user is planning to do the update.

hmmmmmm.....this is not usually a recommended approach in web applications.
Generally people say transactions should not span "user-think-time". Still,
its appropriate in some other situations.....

Anyways all that is beside the point. The point is that some people
probably want to do a select for update (I would imagine people using MySQL
would be very keen on it). So, how is this functionality best exposed to
the user:

  * a global property for the database ie. hibernate.select_for_update=true
  * on a class-by-class basis in the mapping file
  * as a property of the individual session ie. Session.setSelectForUpdate
()
  * as part of the query language ie. SELECT foo FROM CLASS f.Foo FOR
UPDATE
  * as part of the API ie. session.loadForUpdate(fooID)

I figure, if paul is using this, other people will also want it.....

Gavin.