Re: Locks - how to monitor users operations

Thanks for attantion,

> Why is anyone locking anything in a production in an Oracle production
schema?

   A mismatch - of course not in the Oracle (sys) schema.    X - is the schema in which we implemented our business logic (tables,views,packages...)

   Objects are blocked in the X schema, but by many different users - sometimes at the same moment (unfortunatelly) and that generates troubles.

> Don't you know the code being executed and why?

 That is exactly the problem - I don't know all code and I have a short time to find bottlenecks.

> One rarely, if ever, needs to lock a table in Oracle. And if a table does
need to be locked it should be for a
> very good reason and be well documented.

Yes, of course - I am agree with you, but what in case the code is not properly documented?
More - on the first look some pieces of code seeems be well codded.... :-((

> If PL/SQL code being executed by users
> is locking tables I'd likely send the code back to development.

 But I might only send it to myself.... ;-))

> Try as I might by looking in sql.bsq, catalog.sql, and in my notes I can
find no
> record of an RX lock. Do you mean an XR lock?

I mean an Row Exclusive Table Locks (RX) => Oracle 8i Concepts Release 2 (8.1.6) Chapter 24 "Data Concurrency and Consistency" Look either into v$lock.lmode =3

> The dynamic views are not built on any objects but rather against memory
> structures.

Any conclusion - how to use these object structures to trace changing of locks? :-))

> If you can, please described with greater clarity what is happening and
why ...
> and what you wish to have happen and why. The solution is likely not
monitoring
> the locks ... but understanding why anyone would institute them in the
first

I try...

1. User A executes a SELECT FOR UPDATE statement with WHERE clause for example id =1 (id is indexed). SELECT date FROM table WHERE id =1 FOR UPDATE This couses that Table1 is blocked in RS (Row Shared) mode and the row of id=1 in exclusive TX (transaction) mode. Then the program makes another checks and...
2. User B does operation which requires locking table Table1 in shared mode (S) (no records will be changed by the user B , and noone will be able to change any record of this table, but everyone will be able to set S lock) LOCK TABLE Table1 IN ROW SHARED MODE NOWAIT Table is blocked in S mode. (RS is compatible with S )
3. User A procedure made all requiered checks and is trying execute update statement : UPDATE Table1 SET date = sysdate WHERE id =1 (record has been blocked by the FOR UPDATE clause !!!) But UPDATE tries to lock table Table1 in RX mode , and is waiting (there is no option UPDATE NOWAIT). (RX isn't compatible with S) The A's session is hanging...
4. User B commits or rollbacks the changes and release the lock S.
5. User's A transaction updates record.
6. User's A transaction makes SELECT date FROM table WHERE id =2 FOR UPDATE,
7. User C does operation which requires locking Table table1 in shared mode (S)... and so on.......
8. The user's A session extends a long time ...

I would like to find this B and C users which are blocking user A. I would like to know:

• what code they execute (which causes locking the table Table1)
• how long they blocks each other
• what are the reasons to execute the code in that time, etc..

I would like to do it transparently - some piece of my code should register (in background) essential information about:

• locking user (B)
• time of establish lock by the locking user (B)
• waiting user (A)
• time of request by the waiting user (A)
• waiting time
• locked and required objects ( Table1 from example)
• sql text which lock the object (it leads me to the entire business process 1)
• sql text which requested the lock of object (it leads me to the entire business process 2)

This code, something like trigger, should be fired when the Oracle establish the lock which waits to release another lock.

 Finally I would like to queueing business proccess, that have to lock the same objects.

 Now ,I have onother idea to set job which will be fired in short (5 minutes?) period of time and scaning/register  required information into my own table (my schema). Some kind of trace.  But I don't like this idea indead ....

 Maybe there is another, simple solution?

P.S.
Locking is a very difficult problem and it's hard to write down everythig in one mail.
Have you implement a shared row lock (Oracle doesn't - why?). It would be very useful...

> Well unless they think Oracle is SQL Server.

 Have you worked with SQL Server?
 What impressions?

Martin Kubec Received on Mon Feb 24 2003 - 04:47:09 CST