Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Krithika.

Asked: October 07, 2016 - 12:33 pm UTC

Last updated: May 27, 2024 - 3:19 am UTC

Version: 11

Viewed 10K+ times! This question is

You Asked

Dear Tom,

please help me with the below.

Session 1:
update table set field = 'A' where field2 = 'B';

it is not committed/rollbacked

Session 2:


update table set field = 'C' where field2 = 'B';
The session will be waiting for session 1 to get committed, but where the exact max wait time of this will be defined.
In init.ora or in v$parameter, please reply ....

Thanks a lot.....
Krithika




and Connor said...

v$session_wait or v$lock. For eaxmple

--
-- session 1
--
SQL> create table t as select 1 x from dual;

Table created.

SQL> delete from t;

1 row deleted.

--
-- session 2
--
SQL> delete from t;

[stuck]

--
-- from any other session
--

SQL> select sid, event, blocking_session, last_call_et
  2  from v$session
  3  where status = 'ACTIVE'
  4  and blocking_session is not null;

       SID EVENT                                                            BLOCKING_SESSION LAST_CALL_ET
---------- ---------------------------------------------------------------- ---------------- ------------
        22 enq: TX - row lock contention                                                 599          123


So 22 is blocked by 599, and has been blocked for 123 seconds.


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Krithika, October 10, 2016 - 8:13 am UTC

Thanks a lot for the reply!

Please clarify me the below

Sesssion 1:

---sesssion 1

update t1 set t1.field1= 'YES' where t1.id= 12;

Session 1 is not committed/rolled back, so the session still exits.

Session 2:

update t1 set t1.field1= 'NO' where t1.id= 12;

Since session 1 is not committed/rolled back, session 2 will be running (held waiting for session 1 to get committed/rolled back).

Now if we check the locked objects/ session, we will be having the below details.

select a.sid,a.serial#,c.object_name ,event, blocking_session, a.last_call_et
from V$session a,
V$locked_object b,
dba_objects c
where a.sid=b.session_id
and b.object_id=c.object_id
and c.owner = a.username;

SID SERIAL# TABLE EVENT BLOCKING_SESSION LAST_CALL_ET
------------------------------------------------------------------------------------------
2268 23887 T1 enq: TX - row lock contention 2843 935
2843 12731 T1 SQL*Net message from client 309

Hence, according to my understanding last_call_et will reflect the time (in seconds), the object was waiting to get released (which will be increased according to the time that particular object was held in lock).
My doubt is session 2 will be running till session 1 gets committed, so how much time will session 2 wait for session 1, is there any pre-defined timeout time
defined for the same, say for example even after 20 mins, the session 2 is still trying to update waiting for session 1 to be completed, so when session 2 will be aborted/timed out.
Please let me know the parameter which will define about the time taken for raising a timeout/aborting a session.

As per my understanding, after waiting for session 1 for sometime, session 2 will be aborted (timed out) (correct me if I'm wrong), please let me know the
parameter (v$parameter or init.ora) where the timed-out time for any session will be defined.


Thanks a lot!!!!
Connor McDonald
October 11, 2016 - 12:34 am UTC

By default, you will wait *forever* for a lock to be released.

A reader, October 11, 2016 - 6:22 am UTC

Thanks for the reply.
"By default, it 'll wait forever"
Is there any way or parameter for setting the time limit for timeout for such scenario?? Any such parameter available in v$parameters or init.ora file....

Thanks
Connor McDonald
October 11, 2016 - 9:04 am UTC

There isn't a system wide parameter, but you could use a resource manager plan to control it.

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   plan                      IN VARCHAR2, 
   group_or_subplan          IN VARCHAR2, 
   comment                   IN VARCHAR2 DEFAULT NULL, 
   cpu_p1                    IN NUMBER   DEFAULT NULL, -- deprecated
   cpu_p2                    IN NUMBER   DEFAULT NULL, -- deprecated
   cpu_p3                    IN NUMBER   DEFAULT NULL, -- deprecated
   cpu_p4                    IN NUMBER   DEFAULT NULL, -- deprecated
   cpu_p5                    IN NUMBER   DEFAULT NULL, -- deprecated
   cpu_p6                    IN NUMBER   DEFAULT NULL, -- deprecated
   cpu_p7                    IN NUMBER   DEFAULT NULL, -- deprecated
   cpu_p8                    IN NUMBER   DEFAULT NULL, -- deprecated
   active_sess_pool_p1       IN NUMBER   DEFAULT NULL,
   queueing_p1               IN NUMBER   DEFAULT NULL,
   parallel_degree_limit_p1  IN NUMBER   DEFAULT NULL,
   switch_group              IN VARCHAR2 DEFAULT NULL,
   switch_time               IN NUMBER   DEFAULT NULL,
   switch_estimate           IN BOOLEAN  DEFAULT FALSE,
   max_est_exec_time         IN NUMBER   DEFAULT NULL,
   undo_pool                 IN NUMBER   DEFAULT NULL,
   max_idle_time                IN NUMBER   DEFAULT NULL,
   max_idle_blocker_time        IN NUMBER   DEFAULT NULL,  <<========
   switch_time_in_call          IN NUMBER   DEFAULT NULL, 
   mgmt_p1                      IN NUMBER   DEFAULT NULL,
   mgmt_p2                      IN NUMBER   DEFAULT NULL,
   mgmt_p3                      IN NUMBER   DEFAULT NULL,
   mgmt_p4                      IN NUMBER   DEFAULT NULL,
   mgmt_p5                      IN NUMBER   DEFAULT NULL,
   mgmt_p6                      IN NUMBER   DEFAULT NULL,
   mgmt_p7                      IN NUMBER   DEFAULT NULL,
   mgmt_p8                      IN NUMBER   DEFAULT NULL,
   switch_io_megabytes          IN NUMBER   DEFAULT NULL,
   switch_io_reqs               IN NUMBER   DEFAULT NULL,
   switch_for_call              IN BOOLEAN  DEFAULT NULL,
   max_utilization_limit        IN NUMBER   DEFAULT NULL,  
   parallel_target_percentage   IN NUMBER   DEFAULT NULL,  
   parallel_queue_timeout       IN NUMBER   DEFAULT NULL,
   parallel_server_limit        IN NUMBER   DEFAULT NULL,
   utilization_limit            IN NUMBER   DEFAULT NULL,
   switch_io_logical            IN NUMBER   DEFAULT NULL,
   switch_elapsed_time          IN NUMBER   DEFAULT NULL,
   shares                       IN NUMBER   DEFAULT NULL,
   parallel_stmt_critical       IN VARCHAR2 DEFAULT NULL);


A reader, October 11, 2016 - 5:35 pm UTC

Thanks a lot for the reply.

Request to kindly guide me on the below.
1. Max idle blocker time....please elaborate on the same with an example
2. Please explain whether the parameter can be used for controlling the below
Session 1:
One update happening at trigger level for one record.
Session 2:
Second update happening at java layer for same record. Since first session is not yet over, second session hang/block/wait.

In this case, how to.define max idle time blocker for controlling the same...please elaborate with an example.

Thanks a lot
Chris Saxon
October 12, 2016 - 5:03 am UTC 

SQL> begin
  2    dbms_resource_manager.create_pending_area();
  3    --
  4
  5    dbms_resource_manager.create_consumer_group(
  6      CONSUMER_GROUP=>'CG_STOP_BLOCKERS',
  7      COMMENT=>'CG for stop blocking'
  8      );
  9
 10    dbms_resource_manager.create_plan(
 11      PLAN=> 'STOP_BLOCKERS',
 12      COMMENT=>'Plan for stop blocking'
 13    );
 14
 15    dbms_resource_manager.create_plan_directive(
 16      PLAN=> 'STOP_BLOCKERS',
 17      GROUP_OR_SUBPLAN=>'CG_STOP_BLOCKERS',
 18      COMMENT=>'Directive',
 19      MAX_IDLE_BLOCKER_TIME => 60
 20    );
 21
 22
 23    dbms_resource_manager.create_plan_directive(
 24      PLAN=> 'STOP_BLOCKERS',
 25      GROUP_OR_SUBPLAN=>'OTHER_GROUPS',
 26      COMMENT=>'leave others alone'
 27    );
 28
 29    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
 30
 31    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
 32
 33  end;
 34  /

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group('SCOTT','CG_STOP_BLOCKERS',false);

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.set_initial_consumer_group('SCOTT','CG_STOP_BLOCKERS');

PL/SQL procedure successfully completed.

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = STOP_BLOCKERS;

System altered.

SQL> conn scott/tiger
Connected.

SQL> create table t as select 1 x from dual;

Table created.

SQL> delete from t;

1 row deleted.


So this session now has a lock on the row, but is now idle...Now we fire up another session who will get blocked

SQL> conn scott/tiger
Connected.

SQL> set timing on
SQL> delete from t;

[waiting]


After approx 1 min, the following appears

1 row deleted.

Elapsed: 00:00:58.09


When I go back to the other session, I try run something else

SQL> select * from dual;
select * from dual
            *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 24708
Session ID: 593 Serial number: 59128


The session has been killed.

Granularity for MAX_IDLE_BLOCKER_TIME

Narendra, May 22, 2024 - 11:00 am UTC

Hello Chris/Connor,

Does resource manager plan directive support granularity of only 1 second for MAX_IDLE_BLOCKER_TIME? Is there a way to define MAX_IDLE_BLOCKER_TIME in milliseconds?

Thanks in advance
Connor McDonald
May 23, 2024 - 2:29 am UTC

No we can't, and I can't see a reason why you would want something in milliseconds to be honest.

Re: Granularity for MAX_IDLE_BLOCKER_TIME

Narendra, May 23, 2024 - 10:14 pm UTC

Hello Chris/Connor,

I can see why it would not make sense to allow MAX_IDLE_BLOCKER_TIME to be defined in less than a second. But I am somewhat stumped over trying to diagnose an issue where a .NET based application job is completing in under 10 minutes while using on-premise database, with 10 concurrent sessions and exhibiting some amount of DB time spent in enq: tx row lock contention, caused by an UPDATE. The exact same application job, is taking almost 45 minutes when using ExaC@C database with exact same UPDATE statement, using exact same execution plan (using INDEX UNIQUE SCAN), is spending large amount of time in enq: tx row lock contention. The average wait time for enq: tx row lock contention event on ExaC@C is 5-6 times than that reported by on-premise database.
I did come across https://blog.ora-600.pl/2020/01/09/the-curious-case-of-enq-tx-row-lock-contention/ which appears to suggest this is expected in case of "chatty" applications.
However, I am trying to find a logical manner in which I can diagnose this issue.

Thanks in advance,
Connor McDonald
May 27, 2024 - 3:19 am UTC

SQL Trace is probably the best tool here when it comes to diagnosing this, or v$active_session_history *might* be useful depending on the length of the calls.

SQL Trace is going to give a breakdown of waits with precise timing, but also show any SQL net waits, to let you confirm if this issue is related to the Kamils' post
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.