Can an inactive session block other sessions?

Can an inactive sessions block other sessions? Can a session that shows no SQL_ID in v$session, block other sessions?

YES! It can!

And as a matter of fact, the wait event for the waiting sessions is: enq: TX – row lock contention, usually. And I am saying usually, because that is what I came across in my experience, but that doesn’t mean that it is the only reason.

Let’s look at an example, to better understand. For this example, we will need 2 sessions, session 1 (sid 81) and session 2 (sid 162). Session 1 will create a table and insert some data into it, then the session will update one row.

create table test_dr(id number, name varchar2(30));
insert into test_dr values(1,'A');
insert into test_dr values(2,'B');
insert into test_dr values(3,'B');
commit;

@whoami   --> I am checking information about my session.
USER: SYS
SESSION ID: 81
CURRENT_SCHEMA: SYS
INSTANCE NAME: dba1
DATABASE ROLE: PRIMARY
OS USER: oracle
CLIENT IP ADDRESS:
SERVER HOSTNAME: ***
CLIENT HOSTNAME: ***

--This session will update one row, and not commit.

update test_dr set name='C' where id=2;
1 row updated.

For the whoami script, check out an older post here.

Session 2 will try to update the exact same row, and you will notice it will hang.

update test_dr set name='C' where id=2;
1 row updated.

This session will “hang” as it cannot complete the transaction. The session is waiting to obtain a lock on a row in the test_dr table. Session#1 is holding the lock and blocking session #2. If I check for the sessions in the database, I will notice the following:

@sessions_all

INSTANCE   sid serial# user  osuser  MACHINE   program  L s hh:mm:ss SQL ID        current/last event        state    (sec) 
-------- ----- ------- ----- ------- --------- -------- - - -------- ------------- ------------------------- -------------- 
dba1       162   12277 SYS   oracle  *****     sqlplus  L A 00:00:34 c8jzgs9r03b98 enq: TX - row lock conten WAITING     33 
dba1        81   56405 SYS   oracle  *****     sqlplus    I 00:01:03               SQL*Net message from clie WAITING     62 
...

SID 81, is inactive, and as a matter of fact is not running any SQL ID, and SID 162 is blocked by it. There could be more sessions in the database that are blocked and waiting for locks to get released. If I further investigate the blocking, my script will confirm that sid 81 is blocking sid 162:

@blocking.sql

BLOCKER    sid node type           id1           id2 held             req
-------- ----- ---- ---- ------------- ------------- ---------------- ----------------
Blocking    81    1 TX          655365        255198 6 - Exclusive
Waiting    162    1                                                   6 - Exclusive

As soon as sid 81 commits or rolls back, the blocking will be gone.
If you wonder what the content of the blocking.sql script is, you can review one of my older posts Who Is Holding The Lock On The Table?

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the best price, only $18.99 CAD!