Running into requests for attention to resolve blocked transactions is not unusual.
Usually it is enough to identify the session causing the blocking, cancel it (kill session) and it is solved, but on this occasion the problem experienced by one of my customers was massive and recurrent, and therefore required further analysis.
The problem
The problematic application has multiple tasks, and all of them have in common the need to update a row that is unique to each task.
If we are able to identify the row that is being massively blocked, the Client will be able to determine which task it is, and thus start the process of correcting the code, which is the final solution.
The solution
If we simply want to get out of the blocking, a query like the following one helps us to identify the session causing the blocking and proceed to cancel it.
SELECT inst_id, sid,
blocking_instance, blocking_session
SELECT gv$session
WHERE event = 'enq: TX - row lock contention';
INST_ID SID BLOCKING_INSTANCE BLOCKING_SESSION
---------- ---------- ----------------- ----------------
2 57 1 285But since we want to identify the row that is being blocked, we must resort to the package DBMS_ROWID, and some additional columns that fortunately are present in v$session.
The query to use is the following:
SELECT do.owner,
do.object_name,
dbms_rowid.rowid_create ( rowid_type=> 1,
object_number=> do.data_object_id,
relative_fno=> v.rfile#,
block_number=> s.row_wait_block#,
row_number=> s.row_wait_row# )
FROM gv$session s,
dba_objects do,
v$datafile v
WHERE s.event = 'enq: TX - row lock contention'
AND s.row_wait_obj# = do.object_id
AND s.row_wait_file# = v.file#;
OWNER OBJECT_NAME DBMS_ROWID.ROWID_C
---------- --------------- ------------------
SCOTT EMP AAASYGAAOAAAACFAAAWhat we have obtained are the data of the table (owner and name) and the ROWID of the locked row.
With them we can finally obtain the required data:
SELECT *
FROM scott.emp
WHERE rowid = 'AAASYGAAOAAAACFAAA';
EMPNO ENAME SAL
------ ------ ------
7782 CLARK 2450What if the blocking occurred in the past?
In this case we will have to rely on dba_hist_active_sess_history in replacement of v$session, and of course we will need to know the time interval in which the massive blockage occurred.
The query to use will be the following:
SELECT DISTINCT do.owner, do.object_name,
dbms_rowid.rowid_create ( rowid_type=> 1,
object_number=> do.data_object_id,
relative_fno=> v.rfile#,
block_number=> s.current_block#,
row_number=> s.current_row# )
FROM dba_hist_active_sess_history s,
dba_objects do,
v$datafile v
WHERE sample_time >= TO_DATE('18/11/2024 10:01','dd/mm/yyyy hh24:mi')
and sample_time <= TO_DATE('18/11/2024 10:02','dd/mm/yyyy hh24:mi')
and event = 'enq: TX - row lock contention'
AND s.current_obj# = do.object_id
AND s.current_file# = v.file#;
OWNER OBJECT_NAME DBMS_ROWID.ROWID_C
---------- --------------- ------------------
SCOTT EMP AAASYGAAOAAAACFAAAAgain we have obtained the table data (owner and name) and the ROWID of the locked row.
With them we can obtain the required data:
SELECT *
FROM scott.emp
WHERE rowid = 'AAASYGAAOAAAACFAAA';
EMPNO ENAME SAL
------ ------ ------
7782 CLARK 2450Done, case closed, it wasn’t that complicated, was it?
If you wish to complement the above, I recommend you to read the notes:
| 1524216.1 | How to Determine which Row is Locked by a Blocking Session |
| 198150.1 | How To Indentify The Row Which is Locked By an Other User's Session |
One Response
Grande Maestro!!