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

On Thu, 14 Jan 1999 23:06:44 -0000, "Richard" <roxl_at_c031.aone.net.au> 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.

>client software can check to see if the row has changed since it fetched it
>for viewing (one way to do this is to have a field as an update counter -
>but I haven't tried this myself).

You are kidding right? The database in question has thousands of tables, and millions of records. How many extra fields is that? How much auditing is that?

>Given the app you have though, I don't know how you can find the offending
>users - perhaps you should run a periodic check and just disconnect users
>who have been inactive for a particular length of time (seems a bit rude,
>but you can tell them that 1hr breaks are not on).
>
>I also believe that it is possible to identify a user's last SQL (V$SQL or
>v$SQLTEXT???). Perhaps that would be worthwhile method - should narrow the
>field anyhow, even if they are using bind variables.
SQL text is truncated in the various SQL Views you mention, as they have limited VARCHAR2s column datatypes.

Paul
aspscott_at_tcp.co.uk
^^ remove 'as' anti-spam prefix Received on Thu Jan 14 1999 - 07:26:07 CST