| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: LIBRARY CACHE LOCK !!!! ( SQL Tuning )
Thanks Jared !!
My DB is running on Version 8.1.6.2.0 ( 64 Bit )
I have browsed through the code, I did not find anything specific which can cause the deadlock , may be a BUG as you mentioned ..
Thanks
Madhu
-----Original Message-----
Sent: Wednesday, February 26, 2003 12:21 PM
To: Multiple recipients of list ORACLE-L
Some to the graph does not appear properly for some reason.
In any case, this may be a bug. Please read MetaLink Document 166924.1
In part it says:
It occur when one session tries to get a lock on a resource that he
already has in some way.
Normally, the Oracle engine should detect those situations and should
avoid
the signalling of the ORA-04020 to the end-users. When a self deadlock
detection occurs,
Oracle generates a trace file in the user_dump_dest. It is only considered
as a bug if
an ORA-04020 is signalled to the end-user.
2. Deadlocks between concurrent sessions
The ORA-04020 deadlock error usually occurs when two user processes cannot complete their transactions because they are trying to access the same resource.
HTH Jared
"Reddy, Madhusudana" <Madhusudana.Reddy_at_bestbuy.com>
Sent by: root_at_fatcity.com
02/25/2003 05:48 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: RE: LIBRARY CACHE LOCK !!!! ( SQL Tuning )
Thanks Jared !!!!
Here is the Graph i can see in the trace file : ( SELF DEADLOCK !!!! )
8888888888888888888888888888888888888888888888888888888888888888888888888888 888888888888888888888888888888888888 8888888888888888888888888888888888888888888888888888888888888888888888888888 888888888888888888888888888888888888 8888888888888888888888888888888888888888888888888888888888888888888888888888 888888888888888888888888888888888888 8888888888888888888888888888888888888888888888888888888888888888888888888888 888888888888888888888888888888888888
A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object
MDO.MDO_BSE_TEMP_RETEK_PRICE
object waiting waiting blocking blocking handle session lock mode session lock mode
8888888888888888888888888888888888888888888888888888888888888888888888888888 888888888888888888888888888888888888 8888888888888888888888888888888888888888888888888888888888888888888888888888 888888888888888888888888888888888888 8888888888888888888888888888888888888888888888888888888888888888888888888888 888888888888888888888888888888888888 8888888888888888888888888888888888888888888888888888888888888888888888888888 888888888888888888888888888888888888
Thanks again
Madhu
-----Original Message-----
Sent: Tuesday, February 25, 2003 6:36 PM
To: ORACLE-L_at_fatcity.com
Cc: Madhusudana.Reddy_at_bestbuy.com
Importance: High
If you are getting ORA-60 deadlock errors, how about posting the deadlock graph from the trace file?
Also read Doc # 62365.1 on MetaLink.
Jared
"Reddy, Madhusudana" <Madhusudana.Reddy_at_bestbuy.com>
Sent by: root_at_fatcity.com
02/25/2003 02:09 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: LIBRARY CACHE LOCK !!!! ( SQL Tuning )
Hello All,
I have a PL/SQL code which will run once a week, and every time this job
will stuck doing nothing .. and end up waiting on " LIBRARY CACHE LOCK "
.Most of the time this job results in a deadlock . As I know I am not a
SQL
tuning expert ,once again I am seeking your suggestions and help in
resolving the issue !!
Another interesting thing is , after restarting the job ( after killing
for
the first time ) it will go through fine. I am suspecting the way it is
coded. Any inputs ???
Thanks
Madhu
SET SERVEROUTPUT ON
SET LINESIZE 255
SET TAB OFF
VARIABLE g_return_code NUMBER;
DECLARE
CURSOR c_incoming_rows IS
SELECT product_id
, store_id
, clearance_price
, effective_date
, out_of_stock_date
, reset_date
, flag
FROM mdo_pre_temp_retek_price;
v_existing_count NUMBER;
e_invalid_row_count EXCEPTION;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
:g_return_code := 1;
FOR v_row IN c_incoming_rows LOOP
BEGIN
ELSE
UPDATE mdo_bse_temp_retek_price
SET clearance_price = TO_NUMBER(v_row.clearance_price)
/
100.0
, effective_date =
TO_DATE(v_row.effective_date,'YYYYMMDD')
, out_of_stock_date =
TO_DATE(v_row.out_of_stock_date,'YYYYMMDD')
, reset_date =
TO_DATE(v_row.reset_date,'YYYYMMDD')
, flag = v_row.flag
WHERE product_id = LTRIM(v_row.product_id,'0')
AND store_id = LTRIM(v_row.store_id,'0');
END IF;
-- if we have neither 0 nor 1 records, something is terribly
wrong
ELSE
:g_return_code := 4;
RAISE e_invalid_row_count;
END IF;
EXCEPTION
WHEN OTHERS THEN
:g_return_code := 5;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE('Record ignored for store ' ||
v_row.store_id || ' sku ' ||
v_row.product_id || '.');
END;
END LOOP;
:g_return_code := 0;
END;
/
EXIT :g_return_code
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: Madhusudana.Reddy_at_bestbuy.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesReceived on Wed Feb 26 2003 - 13:32:44 CST
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: Madhusudana.Reddy_at_bestbuy.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: Madhusudana.Reddy_at_bestbuy.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |