Inputs to collect in case of Issues Related to Blocking Locks

2)Give me an example of a Scenario mentioned in #1 in SSCA.

Consider Session#1 is API#1(For ex:changeOrder).changeOrder is invoked for Order with OrderHeaderKey OK1. As illustrated in Scenario#1, this API will fire a query to obtain a LOCK on the particular record.

Assume that changeOrder ON_SUCCESS, there is a custom code which makes a call to external system and it will need 10 mins to get back the response.

As changeOrder API execution and ON_SUCCESS event will happen in the same transaction boundary, a commit will be issued only 10 mins(after ON_SUCCESS completes). It means that the API#1 will hold a lock on the record for 10mins(+API execution time).

 

Consider Session#2 is API#2(For ex:getOrderDetails).getOrderDetails is invoked on an Order with OrderHeaderKey OK1.As illustrated in Scenario#1, this API will fire a query to obtain a LOCK on the particular record.

But this will wait on Session#1 to complete as Session#1(changeOrder) has already locked OK1.

In this case, changeOrder is the BLOCKER and getOrderDetails is the WAITER.

3) How can I find the BLOCKER and WAITER in a real-time scenario? For example how can I identify the Session#1 and Session#2?

Whenever a customer reports blocking locks or row-lock contention, get customer to run the queries below when the blocking locks are observed. The results of this query will give us the blocker and waiter information. The query results are self-explanatory.

Query#1
========
      
       SELECT sysdate,lh.inst_id l_ins, lh.SID lock_sid, lx.client_info||lx.module Locker,
       lx.status Locker_Status, lx.SQL_HASH_VALUE,lx.PREV_HASH_VALUE, ls.client_info||ls.module Waiter,
       ls.status Waiter_Status,do.object_name, lw.inst_id w_ins,
       lw.SID wait_sid,
               DECODE (lh.TYPE,
                       'MR', 'Media_recovery',
                       'RT', 'Redo_thread',
                       'UN', 'User_name',
                       'TX', 'Transaction',
                       'TM', 'Dml',
                       'UL', 'PLSQL User_lock',
                       'DX', 'Distrted_Transaxion',
                       'CF', 'Control_file',
                       'IS', 'Instance_state',
                       'FS', 'File_set',
                       'IR', 'Instance_recovery',
                       'ST', 'Diskspace Transaction',
                       'IV', 'Libcache_invalidation',
                       'LS', 'LogStaartORswitch',
                       'RW', 'Row_wait',
                       'SQ', 'Sequence_no',
                       'TE', 'Extend_table',
                       'TT', 'Temp_table',
                      'Nothing-'
                      ) waiter_lock_type,
               DECODE (lw.request,
                       0, 'None',
                       1, 'NoLock',
                       2, 'Row-Share',
                       3, 'Row-Exclusive',
                       4, 'Share-Table',
                       5, 'Share-Row-Exclusive',
                       6, 'Exclusive',
                       'Nothing-'
                      ) wait_mode_req
          FROM gv$lock lw, gv$lock lh, gv$session ls, gv$session lx, gv$locked_object lo, sys.dba_objects do
          WHERE lh.id1 = lw.id1
          AND lh.id2 = lw.id2
          AND lh.request = 0
          AND lw.lmode = 0
          AND lw.inst_id=ls.inst_id
          AND lx.inst_id=lh.inst_id
          AND lw.sid = ls.sid
          AND lx.sid = lh.sid
          AND lx.inst_id = lo.inst_id
          AND lx.sid = lo.session_id
          AND lo.object_id = do.object_id
          AND (lh.id1, lh.id2) IN (SELECT id1, id2
                                     FROM gv$lock
                                    WHERE request = 0
                                    INTERSECT
                                    SELECT id1, id2
                                      FROM gv$lock
                                     WHERE lmode = 0)
                                    order by lh.inst_id, lh.sid;

Query#2
========
          select /*+ ordered */ sysdate, s.inst_id, sid, client_info, module, s.status, machine,
                 program, logon_time, last_call_et, lo.object_id, object_name
                 from sys.gv$session s, sys.gv$locked_object lo, sys.dba_objects o
                 where type = 'USER' AND
                 last_call_et > 15 AND
                 s.sid = lo.SESSION_ID AND
                 s.inst_id = lo.INST_ID AND
                 lo.object_id = o.object_id
                 order by s.inst_id, s.sid;

 

    

4) The query results(CLIENT_INFO column) are not returning the names of Sterling API/Services?

If you do not see the API/Services names related to Sterling, then it means you have not enabled the yfs.app.identifyconnection parameter in your yfs.properties file(for versions < 8.0) or yfs.yfs.app.identifyconnection in your customer_overrides.properties file(for version 8.0 and above).</span></div>

If you set this parameter to Y, the application will stamp the API/Service name currently executed by this session.

5) After finding the API name(from#3 and #4), how do I find the piece of code my API is executing when it appears as the blocker/waiter?

You need to take a thread dump during the time of blocking. Typically when you take 3-4 thread dumps at an interval of 1 min each, you will be able to find out whether the thread(causing the blockage/ waiting to acquire a lock) is STUCK or it is executing a recursive/infinite loop.

6) How can I find the more information around the functionality around the blocker query?

SQLDEBUG level logs, which is light with minimal performance impact will give us this information. You can ask the customer to enable SQLDEBUG just before he starts taking the thread dump.

7) I can have multiple JVMs running during the time of blockage? How can I identify the JVM/API for which I need to take Thread Dumps and SQLDEBUG level logs?

#3 and #4 will come handy here. You can identify the root blocker from the query results and you can find the name of the JVM/API causing the blockage using the CLIENT_INFO column in the query results.

Here is the blocking set you need to collect to troubleshoot any blocking issues in SSCA,

1)    Blocking lock query results after implementing #4.

2)    Set of 3-5 thread dumps at an interval of 1 min each.

3)    SQLDEBUG level logs.