This discussion is archived
6 Replies Latest reply: Apr 25, 2013 4:38 AM by John Stegeman RSS

Transaction Locks

user627885 Newbie
Currently Being Moderated
Hi all,

10.2.0.1 XE

My jboss apps logs is flooded with lots of lock error :
     at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: ORA-00054: resource busy and acquire with NOWAIT specified
But I can not see error in alert.log. Is ora-0054 not being captured?

Thanks,
  • 1. Re: Transaction Locks
    Kh$n Journeyer
    Currently Being Moderated
    check it in OEM performance tab under hang analysis and instance locks,blocking lock are not stored in alter log file.
  • 2. Re: Transaction Locks
    John Stegeman Oracle ACE
    Currently Being Moderated
    Why would you expect every little application error to be in the database alert log?

    They are not.

    You are correct in that ORA-00054 errors aren't expected to be logged in the alert log.
  • 3. Re: Transaction Locks
    user627885 Newbie
    Currently Being Moderated
    Which of the following 3 scripts captures the lock information correctly?
    SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, 
    l.oracle_username, l.locked_mode
    FROM dba_objects o, gv$locked_object l
    WHERE o.object_id = l.object_id;
    
    
    SELECT DECODE(request,0,'Holder: ','Waiter:') || sid sess, id1, id2, lmode, request, type
    FROM gv$lock
    WHERE (id1, id2, type) IN (
      SELECT id1, id2, type FROM gv$lock WHERE request>0)
    ORDER BY id1, request;
    
    SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
    S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT 
    FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, 
    V$PROCESS P, V$SQL SQ 
    WHERE L.OBJECT_ID = O.OBJECT_ID 
    AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR 
    AND S.SQL_ADDRESS = SQ.ADDRESS;
    Kind Regards
  • 4. Re: Transaction Locks
    John Stegeman Oracle ACE
    Currently Being Moderated
    Which of the following 3 scripts captures the lock information correctly?
    Probably none of them. Your SQL is not being blocked (the SQL fails immediately with ORA-00054), so you're not going to be able to see a classic blocker/blockee situation. If I were trying to debug what's going on, I'd tell the developers, "let's please turn on some of that tracing/instrumentation that you wrote and we can see what SQL is being executed," but my experience in the world tells me the response you get will be "what's instrumentation?"
  • 5. Re: Transaction Locks
    user627885 Newbie
    Currently Being Moderated
    So to solve the issue I will do tracing?

    Which of the following command can best resolve the issue?
    ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY;
    
    ALTER SESSION SET sql_trace = true;
    Kind regards.
  • 6. Re: Transaction Locks
    John Stegeman Oracle ACE
    Currently Being Moderated
    So to solve the issue I will do tracing?
    No.

    To help debug the issue, tracing/instrumentation of your code would help. Tracing in the DB, while it could provide some insight is probably not the first option I'd be exploring. This is almost undoubtedly a problem with the design and/or coding in your application, not something in the database itself.

    John

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points