Application locking to support optimisitc locking ?

Discussion in 'Java' started by Timasmith, Oct 18, 2006.

  1. Timasmith

    Timasmith Guest

    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, Oct 18, 2006
    #1
    1. Advertising

  2. Timasmith

    DA Morgan Guest

    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
     
    DA Morgan, Oct 18, 2006
    #2
    1. Advertising

  3. Timasmith

    JXStern Guest

    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.
     
    JXStern, Oct 18, 2006
    #3
  4. Timasmith

    hpuxrac Guest

    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, Oct 18, 2006
    #4
  5. Timasmith

    Bjorn Borud Guest

    ["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
     
    Bjorn Borud, Nov 1, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ron Vecchi

    Locking Application object

    Ron Vecchi, Jul 24, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    433
    Ron Vecchi
    Jul 24, 2003
  2. Nick Zdunic
    Replies:
    0
    Views:
    918
    Nick Zdunic
    Nov 5, 2003
  3. =?Utf-8?B?c29tZXNwYW1Abm9zcGFtLm5vc3BhbQ==?=

    Application registration/locking

    =?Utf-8?B?c29tZXNwYW1Abm9zcGFtLm5vc3BhbQ==?=, May 22, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    315
    =?Utf-8?B?c29tZXNwYW1Abm9zcGFtLm5vc3BhbQ==?=
    May 22, 2004
  4. Allan Ebdrup

    asp.net 1.1 application pool locking

    Allan Ebdrup, May 11, 2007, in forum: ASP .Net
    Replies:
    6
    Views:
    708
    Steven Cheng[MSFT]
    May 22, 2007
  5. Wayne  Wengert

    Application Locking

    Wayne Wengert, Jan 17, 2004, in forum: ASP General
    Replies:
    2
    Views:
    104
    Wayne Wengert
    Jan 18, 2004
Loading...

Share This Page