How to find records that cause enq: TX-row lock contention

How to find out the record V $ SESSION that causes enq: TX-row lock contention has the following four columns to record the row that causes suspension when enq: TX-row lock contention occurs. Www.2cto.com ROW_WAIT_OBJ # NUMBER -- OBJECT_ID ROW_WAIT_FILE # NUMBER containing the record -- the relative file NUMBER of the record ROW_WAIT_BLOCK # NUMBER -- the block number of the record ROW_WAIT_ROW # NUMBER -- the BLOCK in which the record is located most people at www.2cto.com use the following SQL statement to query the blocked rows: select c. OBJECT_NAME,. ROW_WAIT_OBJ #,. ROW_WAIT_FILE #,. ROW_WAIT_BLOCK #,
A. ROW_WAIT_ROW #, dbms_rowid.rowid_create (1, c. OBJECT_ID, a. ROW_WAIT_FILE #, a. ROW_WAIT_BLOCK #,
A. ROW_WAIT_ROW #) rid from v $ session a, v $ enqueue_lock B, dba_objects cwhere. sid = B. SID and B. type = 'tx 'and. ROW_WAIT_OBJ # = object_id (+) and it is normal. SESSION 1: SQL> select rowid from test where rownum = 1; ROWID ------------------ AAp/YEAAFAACmXkAAA SQL> delete from test where rownum = 1; 1 row deleted. SQL> SESSION 2: SQL> update test set object_name = object_name where rownum = 1; SESSION 2 will be HANG. Run the following query in another SESSION to check whether the log that caused the congestion is the ROWID = 'ap/YEAAFAACmXkAAA 'in the TEST table. SQL> select c. OBJECT_NAME, a. ROW_WAIT_OBJ #, a. ROW_WAIT_FILE #, a. ROW_WAIT_BLOCK #,
A. ROW_WAIT_ROW #, 2 dbms_rowid.rowid_create (1, c. OBJECT_ID, a. ROW_WAIT_FILE #, a. ROW_WAIT_BLOCK #,
A. ROW_WAIT_ROW #) rid 3 from v $ session a, v $ enqueue_lock B, dba_objects c 4 where. sid = B. SID and B. type = 'tx 'and. ROW_WAIT_OBJ # = object_id (+); OBJECT_NAM ROW_WAIT_OBJ # ROW_WAIT_FILE # ROW_WAIT_BLOCK # ROW_WAIT
_ ROW # RID ---------- ------------- -------------- --------------- ------------- release TEST 11007492 5 681444 0 AAp/YEAAFAACmXkAAA SQL> select rowid, object_id from scott. test where rowid = 'aap/YEAAFAACmXkAAA '; ROWID OBJECT_ID ---------------- ---------- AAp/YEAAFAACmXkAAA 258 SQL> select rowid, object_id from scott. test where rowid = 'ap/YEAAFAACmXkAAA 'for update
Skip locked; no rows selected, but this is only possible if the OBJECT_ID of the TEST table is consistent with that of DATA_OBJECT_ID. SQL> select object_id, data_object_id from user_objects where object_name = 'test'; OBJECT_ID DATA_OBJECT_ID ---------- ------------ 11007492 11007492 if OBJECT_ID and DATA_OBJECT_ID are different, the query result will be incorrect. Because V $ SESSION. ROW_WAIT_OBJ # records the OBJECT_ID of the object, while DBMS_ROWID.CREATE_ROWID requires the following DATA_OBJECT_ID of the object: SESSION 1: SQL> alter table test move; Table altered. SQL> select object_id, data_object_id from user_objects where object_name = 'test'; OBJECT_ID DATA_OBJECT_ID ---------- ------------ 11007492 11007579 SQL> select rowid from TEST where rownum = 1; ROWID ------------------ AAp/ZbAAFAACmjsAAA SQL> delete from te St where rownum = 1; 1 row deleted. SQL> SESSION 2: SQL> update test set object_name = object_name where rownum = 1; will be HANG. SESSION 3: SQL> select c. OBJECT_NAME, a. ROW_WAIT_OBJ #, a. ROW_WAIT_FILE #, a. ROW_WAIT_BLOCK #,.
ROW_WAIT_ROW #, 2 dbms_rowid.rowid_create (1, c. OBJECT_ID, a. ROW_WAIT_FILE #, a. ROW_WAIT_BLOCK #,.
ROW_WAIT_ROW #) rid 3 from v $ session a, v $ enqueue_lock B, dba_objects c 4 where. sid = B. SID and B. type = 'tx 'and. ROW_WAIT_OBJ # = object_id (+) 5/OBJECT_NAM ROW_WAIT_OBJ # ROW_WAIT_FILE # ROW_WAIT_BLOCK # ROW_WAIT _
ROW # RID ---------- ------------- -------------- --------------- ------------------------------------------------ TEST 11007492 5 682220 0 AAp/YEAAFAACmjsAAA SQL> SELECT rowid from scott. test where rowid = 'ap/YEAAFAACmjsAAA '; SELECT rowid from scott. test where rowid = 'ap/YEAAFAACmjsAAA '* ERROR at line 1: ORA-01410: C in invalid ROWID ROWID_CREATE. ROW_WAIT_OBJ # You must change it to DATA_OBJECT_ID. Www.2cto.com SQL> select c. OBJECT_NAME, a. ROW_WAIT_OBJ #, a. ROW_WAIT_FILE #, a. ROW_WAIT_BLOCK #,.
ROW_WAIT_ROW #, 2 dbms_rowid.rowid_create (1, c. DATA_OBJECT_ID, a. ROW_WAIT_FILE #, a. ROW_WAIT _
BLOCK #,. ROW_WAIT_ROW #) rid 3 from v $ session a, v $ enqueue_lock B, dba_objects c 4 where. sid = B. SID and B. type = 'tx 'and. ROW_WAIT_OBJ # = object_id (+); OBJECT_NAM ROW_WAIT_OBJ # ROW_WAIT_FILE # ROW_WAIT_BLOCK # ROW_WAIT _
ROW # RID ---------- ------------- -------------- ------------- allow TEST 11007492 5 682220 0 AAp/explain SQL> SELECT rowid from scott. test where rowid = 'aap/done'; ROWID ------------------ AAp/hour