Re: Locks - how to monitor users operations

Marcin Kubec wrote:

> 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

My questions were based on the assumption from reading your posting that entire tables were being locked ...not records. I paid too much attention to a literal interpretation of the following statement: "If one user is going an operation which lock table A"

Sorry for any misunderstanding my read may have caused. Go with Richard's response.

Daniel Morgan Received on Mon Feb 24 2003 - 11:17:38 CST