Re: [Hibernate-devel] Pessimistic locking?

[Paul S. <pau...@ne...>]

Gavin_King/Cirrus%CI...@ci... wrote:
> 
>>Another simple question then: how do I "lock" something? If I was doing
>>plain old JDBC, I'd use a "SELECT ... FOR UPDATE" with or without a
>>NOWAIT option.
>>
> 
> Okay, I almost brought this up myself earlier. I've been concerned about
> it for a while. My understanding is that most databases will lock rows
> automatically when necessary (according to the transaction isolation
> level). Hibernate never uses FOR UPDATE because some databases (eg.
> Sybase) don't even allow this clause on a select. However, I sort of
> thought that some other platforms (eg. MySQL) might need it. Does anyone
> know more about this than me? It will be simple enough to make hibernate
> always select for update on those databases which need it, or we can add
> findForUpdate() or loadForUpdate() to the API.
> 
> Are there other times when you *need* to use a select for update? ie.
> times when you *need* to grab a write lock immediately rather than
> escalating to a write lock when we issue an UPDATE?

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.

Consider a simple web app: a user selects something to update, a servlet 
(say, or  JSP, or whatever) grabs the info from the database and 
responds with the HTML page with a form to fill in. The user submits the 
form, and another servlet updates the DB.

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.

The same applies no matter what the architecture: a fat Swing client 
talking directly to the DB has the same issues.

PaulS.