A slow request may be waiting for another transaction. Start with the session's current wait state, then follow a valid blocker to its owner and transaction context. The owner can decide how to finish that transaction and let the waiting request continue.
Oracle DBA Lesson 98 Part 1 — Read the current wait and its blocker
Read the wait correctly
STATUS and STATE answer different questions. STATUS='ACTIVE' means a session is executing SQL; its statement may be waiting. STATE='WAITING' means the reported EVENT is a current wait. With a completed wait state such as WAITED KNOWN TIME, EVENT describes the most recent wait. Check state before treating an event as the present cause of delay.
An inactive session can retain an open transaction. For example, it updates a row and returns control to its client without committing. Another transaction trying to update that row waits until the holder ends its transaction. This ordinary row-lock example explains why session activity alone cannot establish whether its work has released locks.
See V$SESSION and data concurrency and consistency.
Find sessions waiting now
On an Oracle Database 19c single-instance database, read current non-idle user waits from V$SESSION:
SELECT sid, serial#, status, state, sql_id,
event, wait_class, blocking_session_status,
blocking_session, service_name, con_id
FROM v$session
WHERE type = 'USER'
AND state = 'WAITING'
AND wait_class <> 'Idle'
ORDER BY sid;The filter selects current non-idle waits. A session using a processor or ready to run will be outside this selection when it is not currently waiting. An empty result therefore calls for checking the workload and its scope; it does not establish that all work is healthy. A momentary wait may also have ended before the capture.
Retain the capture time, service, container and session identity. These views change as the workload runs; Oracle does not guarantee ordinary query read consistency for dynamic performance views. Repeat important observations before acting.
This method uses live V$SESSION evidence. ASH, AWR, ADDM and SQL Monitor are separate features with their own licensing boundaries.
See dynamic performance views and licensing information.
Follow the blocker
One single-instance reading of a waiting request is:
| Field | Waiting request |
|---|---|
SID / SERIAL# | 82 / 612 |
STATUS / STATE | ACTIVE / WAITING |
EVENT | enq: TX - row lock contention |
BLOCKING_SESSION_STATUS | VALID |
BLOCKING_SESSION | 41 |
SERVICE_NAME / CON_ID | APP_SERVICE / 3 |
When blocker status is VALID, use the reported blocker identity. UNKNOWN means further evidence is needed. NO HOLDER and NOT IN WAIT have their documented meanings; a missing or invalid blocker value is not an operational target.
In this single-instance example, look up SID 41, record its serial number, then recheck both together. If the observed serial number were 907, the follow-up would be:
SELECT sid, serial#, username, status,
sql_id, prev_sql_id, module,
service_name, con_id
FROM v$session
WHERE sid = 41
AND serial# = 907;Replace both values with the current observed identity. If the row has disappeared or changed, capture fresh evidence. Match the service, container, application module and account with the application owner. MODULE depends on application instrumentation and may be null; an account or service name alone may be shared by many clients.
SID can be reused after a session ends. SERIAL# helps distinguish successive sessions with the same SID. Revalidate context immediately before any operational action. In RAC, a blocker is identified with its instance as well: use BLOCKING_INSTANCE and the appropriate GV$SESSION/INST_ID scope. The single-instance query is not a cross-instance investigation.
SQL_ID identifies the currently executing statement, and PREV_SQL_ID identifies the last statement executed. A lock can come from an earlier statement in the same transaction. Use application history and transaction evidence to establish what acquired it; neither field is a complete transaction history. The row-lock event also has other documented causes, including unique-key conflicts and shared bitmap-index fragments, so confirm the actual contention rather than diagnosing it from the event name alone.
See descriptions of wait events.
Resolve the transaction deliberately
The application owner decides whether the transaction's work should be retained with COMMIT or undone with a full ROLLBACK. These end the current transaction and release its transaction locks. Partial rollback to a savepoint has different semantics.
After release, check the previously waiting request's result and current wait state. Its update can now proceed, and its own transaction must also be completed deliberately. A vanished wait by itself does not confirm application success.
Session termination is a separate operational decision requiring current identity, authority, application impact and rollback/recovery considerations. It is not the default treatment for a row-lock wait.
Recap
Check STATE before treating EVENT as the current cause of delay. An inactive session can hold an open transaction and block another update. Follow a blocker when BLOCKING_SESSION_STATUS is VALID, then recheck that SID with its current SERIAL#, including the instance in RAC. SQL_ID and PREV_SQL_ID do not reconstruct the transaction that acquired the lock. The owner ends the holding transaction with COMMIT or a full ROLLBACK, then checks the waiting request and finishes that transaction too.
No comments:
Post a Comment