Thanks for the question, Krithika.

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

Last updated: October 12, 2016 - 5:03 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

(3 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!!!!

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

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

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.

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

Live SQL

Dev Gym

Session wait time of a transaction

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

Live SQL

Dev Gym