Finding rows that were locked, after the fact

In the Oracle database, if you have purchased the tuning pack, you have full access to the DBA_HIST_% views. AWR, or Automated Workload Repository, is normally accessed via grid control or another tool.

However, the raw data in the DBA_HIST views is absolutely invaluable. This post will show how you can find which rows were locked and blocking another user.

To do this, we use the DBA_HIST_ACTIVE_SESS_HISTORY view. This view reflects historical samples taken every ten seconds of the live data in the GV$ACTIVE_SESSION_HISTORY view, which itself reflects samples taken every second.

For us to find the rows, we must query for sessions waiting on the “enq: TX – row lock contention” event during our window of interest. We also want to group by SQL_ID, so we can see which statements were being executed that were most impacted.

Starting at about 21:06, ten minutes into the test, we had huge row locks on the following SQL statement…

UPDATE dcs_inventory SET inventory_lock = :1  WHERE catalog_ref_id = :2

We reverse engineered the row(s) on which it was waiting (this is where the Oracle database really shines with this kind instrumentation), and found the following…

SQL> select inventory_lock from atg_core_prod.dcs_inventory where rowid = dbms_rowid.rowid_create(1, 116307, 1024, 1109549, 44);

INVENTORY_LOCK
--------------------
inventoryManager

SQL> select inventory_lock from atg_core_prod.dcs_inventory where rowid = dbms_rowid.rowid_create(1, 116307, 1024, 1109512, 56);

INVENTORY_LOCK
--------------------
inventoryManager

SQL> select catalog_ref_id from atg_core_prod.dcs_inventory where rowid = dbms_rowid.rowid_create(1, 116307, 1024, 1109512, 56);

CATALOG_REF_ID
----------------------------------------
3413362

SQL> select display_name from atg_cata_prod.dcs_sku where sku_id = '3413362';

DISPLAY_NAME
--------------------------------------------------------------------------------
CAMOUFLAGE FIVE PANEL HAT

SQL>

You can also find the rowid for a given row, and search for it in DBA_HIST_ACTIVE_SESS_HISTORY with something like below

declare
  l_rowid_type number;
  l_object_number number;
  l_relative_fno number;
  l_block_number number;
  l_row_number number;
  l_ts_type_in number;
begin
  for r in (select order_id,rowid from atg_core_prod.dcspp_order where order_id in ('EXP536424935','EXP536461512','EXP536439773')) loop
    dbms_rowid.rowid_info(r.rowid,l_rowid_type,l_object_number,l_relative_fno,l_block_number,l_ts_type_in);
    dbms_output.put_line(r.order_id);
    dbms_output.put_line(l_rowid_type);
    dbms_output.put_line(l_object_number);
    dbms_output.put_line(l_relative_fno);
    dbms_output.put_line(l_block_number);
    dbms_output.put_line(l_ts_type_in);
  end loop;
end;
/

Keep in mind this is mostly effective with UPDATE’s and assumes that the rows aren’t migrated as they are in a table shrink. However, it can be invaluable.