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
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Oct 18 2006 - 11:53:27 CDT
