| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Locks - how to monitor users operations
Hi Marcin,
Comments embedded.
"Marcin Kubec" <mqbec_at_poczta.onet.pl> wrote in message
news:b3ct7s$h5h$1_at_atlantis.news.tpi.pl...
>
> 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
Note that RX table locks is not particularly restrictive *if* implemented with appropriately designed applications. They only restrict *lock table* level S, SX and X locks, they do *not* prevent other concurrent DML or select for update operations. Therefore, such impact should either be minimal or non existent.
>
> > 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.
Again note that a RS table lock is not at all restrictive and only prevents manual X table level locks. As mentioned by other posters, I would strongly question why such a restriction would cause problems in any decently written application. Only the selected *rows* are protected from concurrent DML operations. If it blocks out an exclusive table lock, the only thing I would say is GOOD.
> 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 )
Question. What operation requires the locking of the table in Shared mode ? This *does* prevent DML from concurrently running on the table and is generally a highly questionable operation. I have come across such table locks on a number of occasions and for all par one, have been implemented for the "wrong reasons". Writing an "open" application to cater for any backend database and a non appreciation of Oracle's read consistency model are not valid reasons. IMHO, this is the major cause of your problems ....
> 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 !!!)
Hold on. If User A has really made all the "required checks" then generally they too would have issued a select for update operation and the lock should have been detected earlier (maybe)? With Oracle's locking methodology, yes, writers blocks writers *but* only at the row level (if coded correctly). That's life in Oracle but again I would question generally how often would two separate users need to currently update *the same data*. In the vast majority of cases this would constitute a rare event (eg. 2 customers wishing to purchase exactly the same theatre tickets, 2 customers wishing to withdraw monies from the same account ...). Providing such applications are coded "appropriately" the locks are released as quickly as possible (with the earliest possible commit) to reduce wait times or the application is "clever enough to move on" (eg. and search for other available tickets).
Note also a select for update should only generally be used if the intent is to potentially update the locked rows. The rows are logically locks earlier in the transaction to ensure an exclusive read. If there is no intention to perform an update, then again I would question the need for such a lock.
Key point. *** You want to lock the minimum amount of data for the minimum period of time with the minimum level of lock ***
> 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...
Yes, but only for the duration of the transaction. And although a transaction could hold the lock indefinitely, again that would make the design of such an application questionable. Why are your users continually stepping on each other's toes at the row level. Answer generally is because it's not at the row level as your example perfectly illustrates !!
> 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 again this fatal flaw is repeated. And so again your issues are repeated due to the poor design of inappropriately specifying a table level lock.
Been there, addressed that ...
> 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..
The key dynamic view with regard to all this v$lock. This can be joined with v$session, v$sql etc. etc. to get you all the info you wish. Also dba_blockers and dba_waiters can be useful when locking issues are common.
However, my big recommendation is rather than spend too much time and effort on your requests outlined below which would only add to the overhead of your applications, would be to seriously look at the design of your application and identify areas where *excessive* locking or a more *restrictive* locking is being implemented than is desirable or necessary.
Fix the root cause of your problems and such monitoring will more than likely be totally redundant.
Also read up on Oracle's locking and read consistency model (in the Concepts guide) in order to fully understand the issues at hand.
Let me repeat again as this is the main point I've been trying to get across in my long winded way.
Good Luck
Richard
>
> 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 - 07:59:39 CST
 |
 |