Application locking to support optimisitc locking ?

Hi,

I have a new application with optimistic locking implemented. Works rather well since I didnt implement it, it came free with Hibernate.

Regardless of that, the challenge is to implement application locking for the general use case of
1) User A opens a business object for write access 2) User B attempts to open the same business object for write access - an exception is thrown which the UI converts to a helpful message to the user.
3) The helpful message might say who has the object open at that time, would they like to override etc.

So my first attempt was to add a column to every table called 'lock_date' which has a timestamp when a user opens that table (represented by a business object) for write access.

It is a timestamp because it facilitates implementation of a timeout feature.
It is a column on every table since the row was read anyway - so cheap to check.

However a couple issues
a) Reading the value might have been cheap but I still have do an update on the table to set the new lock date/time

b) Adding information - such as the user who locked the row and what application they were using requires more fields. Rather than add them to every table it makes sense to have a LOCK table.

This leads to the second attempt which is lets get rid of the column and just have a single table which has the unique identifier for the object locked (pkey on a table), user, application and date.

Rows will be read/written as locks are checked/created.

Of course this adds considerations. This process needs to be efficient. Inserting and deleting rows would not be efficient - perhaps only updating rows in the lock table is the way to go - activate, inactivate etc. Purge now and then through a batch job.

That all makes it less appealing. I am using a J2ee server, perhaps the locks should not be in the database at all - though it is rather useful to have that relational access to the locks information.

Anyone have a better strategy for all this?

thanks

Tim Received on Wed Oct 18 2006 - 11:46:36 CDT