Re: Application locking to support optimisitc locking ?

Timasmith wrote:
> 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?

I would recommend buying and reading Tom Kyte's latest book "Expert Oracle Database Architecture".

He reviews and discusses in depth how locking and transactions work in oracle and compares and contrasts it with other databases.

You definitely don't want to create a LOCK_TABLE table. That's going in the opposite direction of suppporting scalability and concurrency.

I would first ask you to look at re-designing your application and spending time at the ERD stage.

Oracle supports row level locking very well. If you need to add in columns such as a timestamp and related information ( who/what/where ) then add them in to the tables that need them.

>
> thanks
>
> Tim
Received on Wed Oct 18 2006 - 12:19:08 CDT