Thanks for the question, Alok.
Asked: November 17, 2021 - 12:39 pm UTC
Last updated: November 18, 2021 - 5:17 am UTC
Viewed 100+ times
This is regarding locking issue in database. One fine Monday morning client asked me to check slowness in the system reported by customer on Saturday.
ASH report looks like as below for issue period.
Blocking Sid (Inst) % Activity Event Caused % Event User Program
1132, 3397( 2) 9.12 enq: TX - row lock contention 9.12 ** NOT FOUND ** BLOCKING SESSION NOT FOUND
From report its clear that there was some row lock contention for almost 2 hours(same update query was present in AWR for almost 2 hours)
So I run below query on table (DBA_HIST_ACTIVE_SESS_HISTORY) to check further:
SELECT DISTINCT A.SQL_ID,TO_CHAR(A.SAMPLE_TIME,'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME,A.BLOCKING_SESSION,D.OBJECT_NAME
FROM DBA_HIST_ACTIVE_SESS_HISTORY A, GV$SQL S, DBA_OBJECTS D
AND BLOCKING_SESSION IS NOT NULL
AND A.USER_ID <> 0
AND A.CURRENT_OBJ# = D.OBJECT_ID
AND A.SAMPLE_TIME BETWEEN
TO_TIMESTAMP('06.11.2021 13:00:00', 'dd.mm.yyyy hh24:mi:ss') AND
TO_TIMESTAMP('06.11.2021 16:40:59', 'dd.mm.yyyy hh24:mi:ss')
AND A.EVENT = 'enq: TX - row lock contention'
ORDER BY SAMPLE_TIME DESC;
and output confirmed the same that blocking session is 1132 (as shown in ASH report). Now when I queried the same table with session Id 1132, sample output is below:
session Id sql_id
So here I am stuck and my question is as below:
1. Since multiple sqls are running with same session id, how to uniquely identify the blocking sql.
2. All above sqls with session id 1132 are select statement. So can a select statement cause lock if query runs for 30 minute and in between underlying data changes.
and Connor said...
A select statement wont cause a lock *unless* it is SELECT FOR UPDATE.
But here's the issue - you don't have to *active* to be a blocker, and it could easily be the case that you will not be present in active session history at all.
- update table set col = 1 where primary_key = [value]
That will run in microseconds and will most likely never appear in v$ash because its instantaneous
- update table set other_col = 1 0
This session will be stuck on session 1, you will see session 2 in ASH over and over and over (because its stuck) but there will never be an entry for session 1's SQL because it will never be captured.
In particular, session 1 may now be running hundreds of SELECTs, all of which *might* be in ASH, but they have no bearing on that update statement which has been left hanging.
You *might* be able to get some inferences by looking at V$TRANSACTION and comparing START_DATE to LAST_ACTIVE in V$SQL.