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

On Fri, 15 Jan 1999 14:27:41 +0800, "Nicodemus Chan" <nicodemus_at_pacific.net.sg> wrote:

>
>>>This is just bad application design and the problem is discussed in most
>DB
>>>manuals - the record should not be locked until just before the update.
>>This is exactly what the client program does. When going to edit the
>>record by way of DB aware controls the row lock select is fired to
>>lock just before the update.
>
>
>Then something may be wrong with the way the client is written. The update
>should occur and then quickly committed to release the lock immediately. It
>seems the client doesn't commit and holds on to the locks issued.

Like a lot of large RDBs, our system utilises cached updates to minimize network traffic (The lock query is not cached), so the updates on the form they are on need to be finished with before the Cache gets flushed to the server and committing / rollback takes place. Most forms edit only a single table and normally only a few records on each form are locked. Committing after every record change results in ridiculous levels of network traffic especially on a large user / large scale RDB. I know, I've worked on large systems that do this.

I still haven't had a solution to the problem in hand. The trouble is people keep saying "you could try this, but I don't know if it works", "you should have done it this way" or "that way", but the fundaments of the problem are this system is live / established / and used successful by lots of large clients, the architecture is already in place. The problem I described is a requirement, and the scenario is a possible worst case situation. Anyone who has worked on large Corporate Client Server systems know that it is not a case of stop the train and redesign it. It works, and it works well but needs a locking identification solution to a NOWAIT query, a simple requirement which myself, many others and Oracle doesn't seem to be able solve or provide. I've seen other DB systems which seem to be able do it, so why not the best DB Oracle?

Many people, who have tried similar pessimistic locking approaches, have E-mailed me agreeing that they believe it is an architectural flaw in Oracle. All I need is a join between the lockers Session SID and the NOWAIT failed User's SID which Oracle must know to be able to return the "Resource busy ...NOWAIT" exception anyway. Why can't they expose it in a view?

Kind Regards
Paul Scott
aspscott_at_tcp.co.uk
^^ remove 'as' anti-spam prefix to E-mail Received on Fri Jan 15 1999 - 08:13:13 CST