«

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

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?
>
> thanks
>
> Tim

Don't reinvent the wheel.
Look at the capabilities of the DBMS_LOCK built-in package.
--
Daniel A. Morgan
University of Washington

(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On 18 Oct 2006 09:46:36 -0700, "Timasmith" <>
wrote:

>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.

What is your volume and rate?

A couple of inserts/deletes per second on average, should be harmless,
unless you have very, very bursty peaks that might have problems.

J.

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

["hpuxrac" <>]
|
| You definitely don't want to create a LOCK_TABLE table. That's going
| in the opposite direction of suppporting scalability and
| concurrency.

it would also make the application vulnerable to any application not
using the aforementioned locking scheme.

-Bjørn