Oracle – How Can a Session Be Blocked by an Idle Session

blockingoracle

I'm trying to identify blocking sessions using the following query:

SELECT
  v1.sid esid,  v1.blocking_session bsid,  v1.process,  v1.wait_class,  v1.event,  v1.sql_id,  v1.seconds_in_wait waitss,  v2.blocking_session bsid2,  v2.process,
  v2.sql_id,  v2.wait_class,  v2.event
from
  v$session v1, v$session v2
where
  v1.blocking_session is not null and v1.blocking_session = v2.sid
order by
  waitss desc

Here is sample output:

ESID    BSID    PROCESS WAIT_CLASS  EVENT                           SQL_ID          WAITSS  BSID2   PROCESS_1 SQL_ID_1 WAIT_CLASS_1 EVENT_1
2707    2313    8526    Application enq: TX - row lock contention   05v90pbavkptk   11      (null)  28152     (null)   Idle         SQL*Net message from client

Which indicates that the blocking session is idle. Maybe I'm missing something but I don't understand how an idle session that is not executing any statement can be blocking anything.

Best Answer

It would appear the idle session has a lock on at least one record required by the blocked query. The user has already executed a query that acquired a lock. Normally the lock will be held until they commit or rollback the transaction.

I've run into this a few times when a developer went for lunch while in the middle of updating or inserting data. The lock was removed shortly after they returned from lunch.

If their session times out or you kill their session, the transaction should rollback and release the lock.

If you can determine what location they are connected from you may be able to contact them and request they commit or rollback their transaction.

Related Solutions

Sql-server – TempDB Query being Blocked

This code is from a system stored procedure called sp_spaceused, which returns the amount of allocated space (pages) for database objects.

Look for home-brewn monitoring jobs or maintenance scripts that may be running a bit too frequently.

SQL Server – Resource Cost of a Blocked Query

A request that is being blocked does no work. Here's an example:

Create a blocking query

use AdventureWorks2012;
go

begin tran;
    update HumanResources.Department
    set Name = 'testing 1 2 3'
    where DepartmentID = 7;
--rollback tran;

Run a query that will be blocked by the above session

use AdventureWorks2012;
go

select *
from HumanResources.Department;

Now look at any "work" this blocked request could be doing (query sys.dm_exec_requests for this blocked session, and then wait some time and query again to compare/contrast):

select
    start_time,
    status,
    total_elapsed_time_sec = 
        total_elapsed_time / 1000,
    cpu_time,
    reads,
    logical_reads,
    writes
from sys.dm_exec_requests
where session_id = 52;  -- my blocked session's spid

waitfor delay '00:00:10';

select
    start_time,
    status,
    total_elapsed_time_sec = 
        total_elapsed_time / 1000,
    cpu_time,
    reads,
    logical_reads,
    writes
from sys.dm_exec_requests
where session_id = 52;  -- my blocked session's spid

enter image description here

What you see here is a request that is doing no work, as it is not in the running state. When it is suspended then it is not on a processor doing any work.

Best Answer

Related Solutions

Sql-server – TempDB Query being Blocked

SQL Server – Resource Cost of a Blocked Query

Related Question