Wait for event enq TX row lock contention Analysis

When analyzing the event enq TX row lock contention Analysis in the Oracle database performance report AWRRPT, it is found that the top 5 tasks waiting for the event first are enq: TX-row lock contention. Www.2cto.com Rpt code Top 5 Timed Events Event Waits Time (s) Avg Wait (MS) % Total Call Time Wait Class enq: TX-row lock contention 27,190 62,674 2,305 Application CPU time 63.1 36,227 log file sync 36.5 52 0. 1 Commit log file parallel write 143,992 29 0. 0 System I/O db file sequential read 49,403 23 0. 0 User I/O declaration, the database version is 10 Gb for linux x86 64bit. This wait event consumes a vast majority of CPU resources, resulting in a reduction in system sorting performance. Some queries take only a few milliseconds, but now they take several minutes. The CPU usage remains at 100% for a long time. Www.2cto.com wait event enq: TX-row lock contention may appear in the following three cases. The first case is a real row lock conflict in the business logic. A record is operated by multiple people at the same time, such as updating at the same time. The request mode corresponding to this lock is 6. The second case is a unique key conflict, that is, multiple records with the same primary key fields are inserted at the same time. The request mode corresponding to this lock is 4. This is also an application logic problem. The third case is the update conflict on the bitmap index block, that is, multiple sessions simultaneously update the same data block on the bitmap index. In this case, the request mode corresponding to the Session Request lock is 4. The physical structure of a bitmap index is the same as that of a normal index. It is also a B-tree structure. The logical structure of the data records it stores is "key_value, start_rowid, end_rowid, bitmap ". The content is similar to this: "'200', 100000000000,10000034441, 8088" Bitmap is a binary, indicating the record from START_ROWID to END_ROWID. 1 indicates the ROWID record equal to key_value, that is, '200, 0 indicates that this record is not. By understanding the structure of the bitmap index, we can understand that when multiple records are inserted to a table with a bitmap index at the same time, the records in one block of the bitmap index will be updated at the same time. The larger the concurrency, the more serious the wait. Enquence is the lock for coordinated access to database resources. Enq: indicates that the session is waiting for the lock held by another session. Its name is composed of enq: enqueue_type-related_details. In v $ event_name, a complete list of waiting events starting with enq: is provided. In v $ session and v $ session_wait, the event column is enq: TX-row lock contention, which indicates that the session is in the row lock wait state. The p1 parameter value of the wait event is name | mode. Select sid, chr (bitand (p1,-16777216)/16777215) | chr (bitand (p1, 16711680)/65535) "Name", (bitand (p1, 65535 )) "Mode" from v $ session_wait where event like 'enq % '; you can use this SQL statement to convert p1 to readable text. I will test these three cases separately. First, prepare the test table and data. -- Create test table and data create table t_all_objs as select owner, object_id, object_name from all_objects where 0 = 1; alter table T_ALL_OBJS add constraint into primary key (OBJECT_ID); insert into t_all_objs (owner, object_id, object_name) values ('test', 2013011701, 'test1'); insert into t_all_objs (owner, object_id, object_name) values ('test', 2013011702, 'test2 '); commit; in the first case, different sessions simultaneously update the same record -- test1 -- sid = 1015 update t_a Ll_objs set object_name = 'test11' where object_id = 2013011701; -- sid = 1065 update t_all_objs set object_name = 'test101 'where object_id = 2013011701; -- Query select sid, SQL _id, blocking_session, event, p1text, p1, wait_class, state from v $ session where sid in (1015,106 5); select * from v $ session_wait where sid in (1015,106 5 ); SID SQL _ID BLOCKING_SESSION EVENT P1TEXT P1 WAIT_CLASS STATE1 1015 SQL * Net message From client driver id 1413697536 Idle WAITING2 1065 6y6u0gx4qa5v5 1015 enq: TX-row lock contention name | mode 1415053318 Application waiting sid seq # EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS # WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE1 1015 44 SQL * Net message from client driver id 1413697536 54435000 # bytes 1 00000001 0 00 2723168908 6 Idle 0 1306 WAITING2 1 065 41 enq: TX-row lock contention name | mode 1415053318 54580006 usn <16 | slot 655407 000A002F sequence 92680 00016A08 4217450380 1 Application 0 1291 WAITING -- Obtain the SQL _id value, query the SQL information. Select SQL _text, s. EXECUTIONS, s. ELAPSED_TIME/1000000 from v $ SQL s where SQL _id = '6y6u0gx4qa5v5 '; SQL _TEXT EXECUTIONS S. ELAPSED_TIME/10000001 update t_all_objs set object_name = 'test11' where object_id = 2013011701 0 1286.977282 -- query the lock Information select * from v $ lock where sid in (1015,106 5) order by sid, type; addr kaddr sid type ID1 ID2 lmode request ctime BLOCK1 31AA5BC4 31AA5BDC 1015 TM 191499 0 3 0 1306 02 30D135B0 30D136CC 1015 TX 655407 92680 6 0 1306 13 31AA5C88 31AA5CA0 1065 TM 191499 0 3 0 1291 04 32034928 3203493C 1065 TX 655407 92680 0 6 1291 0 second case, insert records with the same primary key field in different sessions -- test2 -- sid = 1015 insert into t_all_objs (owner, object_id, object_name) values ('test', 2013011703, 'test1 '); -- sid = 1065 insert into t_all_objs (owner, object_id, object_name) values ('test', 2013011703, 'test11'); -- Query select sid in the Third Session, SQL _ Id, blocking_session, event, p1text, p1, wait_class, state from v $ session where sid in (1015,106 5); select * from v $ session_wait where sid in (1015,106 5 ); SID SQL _ID BLOCKING_SESSION EVENT P1TEXT P1 WAIT_CLASS STATE1 1015 SQL * Net message from client driver id 1413697536 Idle WAITING2 1065 bmv0jcs53zkad 1015 enq: TX-row lock contention name | mode 1415053316 Application waiting sid seq # EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS # WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE1 1015 59 SQL * Net message from client driver id 1413697536 54435000 # bytes 1 00000001 0 00 2723168908 6 Idle 0 69 WAITING2 1065 69 enq: TX-row lock contention name | mode 1415053316 54580004 usn <16 | slot 458790 00070026 sequence 92434 00016912 4217450380 1 Application 0 12 WAITING -- get SQL _id Value to query the SQL information. Select SQL _text, s. EXECUTIONS, s. ELAPSED_TIME/1000000 from v $ SQL s where SQL _id = 'bmv0jcs53zkad '; SQL _TEXT EXECUTIONS S. ELAPSED_TIME/10000001 insert into t_all_objs (owner, object_id, object_name) values ('test', 2013011703, 'test11 ') 0 11.992511 -- query the lock information Again select * from v $ lock where sid in (1015,106 5) order by sid, type; addr kaddr sid type ID1 ID2 lmode request ctime BLOCK1 31AA5BC4 31AA5BDC 1015 TM 19149 9 0 3 0 69 02 30D135B0 30D136CC 1015 TX 458790 92434 6 0 69 13 31AA5C88 31AA5CA0 1065 TM 191499 0 3 0 12 04 32034928 3203493C 1065 TX 458790 92434 0 4 12 05 30E30A50 30E30B6C 1065 TX 589865 106712 6 0 12 0 third case, test3 -- create a bitmap index create bitmap index ind_T_ALL_OBJS on T_ALL_OBJS (owner); -- sid = 1015 insert into t_all_objs (owner, object_id, object_name) values ("TEST", 2013011703, 'Test11'); -- sid = 1065 insert into t_all_objs (owner, object_id, object_name) values ('test', 2013011703, 'test12 '); -- Query select sid, SQL _id, blocking_session, event, p1text, p1, wait_class, state from v $ session where sid in (1015,106 5) in the third session ); select * from v $ session_wait where sid in (1015,106 5); SID SQL _ID BLOCKING_SESSION EVENT P1TEXT P1 WAIT_CLASS STATE1 1015 SQL * Net message from client driver id 1413697536 Idle WAITING2 1065 gsf39j000n6ys 1015 enq: TX-row lock contention name | mode 1415053316 Application waiting sid seq # EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS # WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE1 1015 74 SQL * Net message from client driver id 1413697536 54435000 # bytes 1 00000001 0 00 2723168908 6 Idle 0 24 WAITING2 1065 85 enq: TX-row lock contentio N name | mode 1415053316 54580004 usn <16 | slot 524294 00080006 sequence 106695 0001A0C7 4217450380 1 Application 0 15 WAITING www.2cto.com -- Obtain the SQL _id value and query the SQL information. Select SQL _text, s. EXECUTIONS, s. ELAPSED_TIME/1000000 from v $ SQL s where SQL _id = 'gsf39j000n6ys '; SQL _TEXT EXECUTIONS S. ELAPSED_TIME/10000001 0 11.988711 -- query the lock information Again select * from v $ lock where sid in (1015,106 5) order by sid, type; addr kaddr sid type ID1 ID2 lmode request ctime BLOCK1 31AA5BC4 31AA5BDC 1015 TM 191499 0 3 0 24 02 30D135B0 30D136CC 1015 TX 524294 106695 6 0 24 13 31AA5C88 31AA5CA0 1065 TM 191499 0 3 0 15 04 32034928 3203493C 1065 TX 524294 106695 0 4 15 05 30E30A50 30E30B6C 1065 TX 458798 92446 6 0 15 0 in database awrrpt I also see a large number insert operation, each operation takes a long time to execute. This is either an application logic issue or a bitmap index record update wait. The developer confirmed that the logic problem was not possible. The query table index was indeed a bitmap index.