6 Replies Latest reply: Apr 25, 2013 6:38 AM by John Stegeman RSS

    Transaction Locks

    user627885
      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
          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
            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
              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
                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
                  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
                    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