Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: A row locking problem that baffles all...

Re: A row locking problem that baffles all...

From: Stephen Tenberg <STenberg_at_fcs-usa.com>
Date: Fri, 15 Jan 1999 16:06:47 GMT
Message-ID: <rIJn2.123$Rc.1464@nnrp2.ptd.net>

><STenberg_at_fcs-usa.com> wrote:
>
>>This is just a classic mistake programmers have been making for the past
40
>>years, and probably will until the end of time.
>>
>>It is *always* a programming error to lock a record when some further
manual
>>intervention is required. In such cases someone will inevitably walk away
>>at the wrong time.
>The original question was to establish who caused the block to the
>nowait lock. The scenario was a worst case situation.
>
>Our system is well established, successfully processing a massive
>amount of sensitive data, and so uses pessimistic locking so this
>sensitive data isn't compromised. Pessimistic locking has the
>shortfall that you mention (someone might leave a transaction with a
>lock open), but some of the biggest RDBs in the world use Pessimistic
>locking. 99.999% of all users commit / rollback their work as soon as
>they finish, and the others which hold locks for longer rarely cause
>problems as Oracle always has facilities to close sessions/kill
>sessions/warn sessions that their transaction/lock has been open too
>long. I am forced to disagree with your original statement.
>
>Kind Regards
>Paul Scott
>aspscott_at_tcp.co.uk
>^^ remove 'as' anti-spam prefix to E-mail

Some of the biggest RDBs in the world have Y2K issues too, when we all knew that 20 years ago it was insane. I am not being sarcastic, I just seriously believe that this type of programming (as in, its not a problem *most* of the time, so lets do it) is what causes unreliable systems.

I know all too well how difficult it can be to patch large running systems, but nevertheless that is probably the right thing to do. The most elegant solution I have seen is:

  1. Add a column to the table - "UpdateCount"
  2. Add an update trigger that increments the count.
  3. When selected the record, do *not* use for update.
  4. After the user makes the edits, add "Where UpdateCount = <whatever>" to the SQL
  5. Make sure something actually got updated.

As a temporary workaround I have seen Windows NT clients with a timer that is fired when a user enters edit mode, giving them say, 3 minutes before a warning is issued. Ugly, but can hold you a while.

Steve Received on Fri Jan 15 1999 - 10:06:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US