Shulou(Shulou.com)06/01 Report--

Reference: http://www.killdb.com/2015/07/13/%E5%85%B3%E4%BA%8Eenq-tx-row-lock-contention%E7%9A%84%E6%B5%8B%E8%AF%95%E5%92%8C%E6%A1%88%E4%BE%8B%E5%88%86%E6%9E%90.html

Testing and case Analysis of enq: TX-row lock contention

1. Primary key or unique index

Session 1:

SQL > select sid from v$mystat where rownum=1

SID

-

seventy-four

SQL > create table t1_tx (id number primary key,name varchar2 (20))

Table created.

SQL > insert into t1_tx values (1)

1 row created.

SQL > commit

Commit complete.

SQL > insert into t1_tx values (2) xxoo')

1 row created.

Not submitted.

Session 2:

SQL > select sid from v$mystat where rownum=1

SID

-

forty-five

SQL > insert into t1_tx values (2) xxoo')

Hang .

Session 3:

SQL > set lines 200

SQL > col event for A30

SQL > select inst_id

2 sid

3 chr (bitand (p1,-16777216) / 16777215) | |

4 chr (bitand (p1, 16711680) / 65535) "Name"

5 (bitand (p1, 65535)) "Mode"

6 event

7 sql_id

8 blocking_session

9 FINAL_BLOCKING_SESSION

10 from gv$session

11 where event like 'enq%'

INST_ID SID Name Mode EVENT SQL_ID BLOCKING_SESSION FINAL_BLOCKING_SESSION

1 45 TX 4 enq: TX-row lock contention 4s99cmp3khb1b 74 74

SQL >

SQL > select sid,serial#,username,sql_id,status from v$session where sid=74

SID SERIAL# USERNAME SQL_ID STATUS

-

74 23 HR INACTIVE

If sql_id is empty, it means that the session is inactive, and the session should be submitted or rolled back.

SQL > select * from v$Lock where block=1

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-

000000008E9E80C0 000000008E9E8138 74 TX 524298 9978 6 0 221 1

Block is 1, blocking the session.

As you can see, when there is a primary key or unique index for a table, when a session operation primary key is not committed, and other sessions also operate the same primary key, then they must wait, and its holding mode=4; blocks the holding mode=6 of blocker.

2 、 Bitmap INDEX

Session 1:

SQL > select * from t1_tx

ID NAME

--

1 wang

2 wang

3 xxoo

4 xxoo

SQL >

SQL > select sid from v$mystat where rownum=1

SID

-

seventy-four

SQL > create bitmap index idx_bitmap_name on t1_tx (name)

Index created.

SQL > update t1_tx set name='tx' where id=3

1 row updated.

Not submitted.

Session 2:

SQL > select sid from v$mystat where rownum=1

SID

-

forty-five

SQL > update t1_tx set name='bitmap' where id=4

Hang .

Session 3:

SQL > col event for A30

SQL > select inst_id

2 sid

3 chr (bitand (p1,-16777216) / 16777215) | |

4 chr (bitand (p1, 16711680) / 65535) "Name"

5 (bitand (p1, 65535)) "Mode"

6 event

7 sql_id

8 blocking_session

9 FINAL_BLOCKING_SESSION

10 from gv$session

11 where event like 'enq%'

INST_ID SID Name Mode EVENT SQL_ID BLOCKING_SESSION FINAL_BLOCKING_SESSION

1 45 TX 4 enq: TX-row lock contention 7wanaturqndn1 74 74

SQL >

SQL > set lines 200 pagesize 200

SQL > select * from table (dbms_xplan.display_cursor ('& sql_id', NULL, 'ALL'))

Enter value for amp: 7wanaturqndn1

Old 1: select * from table (dbms_xplan.display_cursor ('& sql_id', NULL, 'ALL'))

New 1: select * from table (dbms_xplan.display_cursor ('7wanaturqndnn1), NULL,' ALL'))

PLAN_TABLE_OUTPUT

-

SQL_ID 7wanaturqndn1, child number 0

-

Update t1_tx set name='bitmap' where id=4

Plan hash value: 1842098942

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | UPDATE STATEMENT | 1 (100) | | |

| | 1 | UPDATE | T1_TX |

| | * 2 | INDEX UNIQUE SCAN | SYS_C0010951 | 1 | 25 | 1 (0) | 00:00:01 |

SQL > select * from v$Lock where block=1

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-

000000008E9E80C0 000000008E9E8138 74 TX 262171 2920 6 0 264 1

SQL >

SQL > select sid,serial#,username,sql_id,event from v$session where sid=74

SID SERIAL# USERNAME SQL_ID EVENT

74 23 HR SQL*Net message from client

SQL > select owner,index_name,index_type from dba_indexes where table_name='T1_TX'

OWNER INDEX_NAME INDEX_TYPE

-

HR IDX_BITMAP_NAME BITMAP

HR SYS_C0012427 NORMAL

We can see that if there is a bitmap index on the table, then when multiple sessions are updated at the same time in update, there is bound to be tx waiting.

At this point, waiter applies for the tx lock mode=4, while blocker holds mode=6, and it is impossible to query the blocker session to sql_id through v$session attempts.

3. The data is located in the same block

Session 3:

SQL > conn hr/hr

Connected.

SQL > select dbms_rowid.rowid_object (rowid) obj#

2 dbms_rowid.rowid_relative_fno (rowid) rfile#

3 dbms_rowid.rowid_block_number (rowid) block#

4 dbms_rowid.rowid_row_number (rowid) row#

5 from t1_tx

6 order by 4

OBJ# RFILE# BLOCK# ROW#

--

90536 4 4087 0

90536 4 4087 1

90536 4 4087 2

90536 4 4087 3

SQL >

Session 1:

SQL > select sid from v$mystat where rownum=1

SID

-

forty-five

SQL > select * from t1_tx

ID NAME

--

1 wang

2 wang

3 tx

4 bitmap

SQL > update t1_tx set name='enmotech' where id=2

1 row updated.

SQL > commit

Commit complete.

SQL >

Session 2:

SQL > select sid from v$mystat where rownum=1

SID

-

seventy-four

SQL > update t1_tx set name='xyz'where id=4

1 row updated.

SQL > commit

Commit complete.

SQL >

Even if I open two sessions and execute 100w times, there will be no tx lock.

Session 1:

SQL > declare

2 c number

3 begin

4 for i in 1.. 1000000 loop

5 update t1_tx set name = 'shit1' where id = 2

6 end loop

7 end

8 /

PL/SQL procedure successfully completed.

SQL >

Session 2:

SQL > declare c number

2 begin

3 for i in 1.. 1000000 loop

4 update t1_tx set name = 'tMushi 'where id = 3

5 end loop

6 end

7 /

PL/SQL procedure successfully completed.

SQL >

Session 3:

SQL > set lines 200 pages 999

SQL > col event for A60

SQL > select inst_id,event,count (*) from gv$session where status='ACTIVE' and (wait_class'Idle' or event not like 'SQL*Net%') group by inst_id,event order by 1Pol 3

INST_ID EVENT COUNT (*)

-

1 smon timer 1

1 Streams AQ: waiting for time management or cleanup tasks 1

1 Streams AQ: qmn slave idle wait 1

1 Space Manager: slave idle wait 1

1 SQL*Net message to client 1

1 VKTM Logical Idle Wait 1

1 pmon timer 1

1 Streams AQ: qmn coordinator idle wait 1

1 DIAG idle wait 2

1 rdbms ipc message 17

10 rows selected.

SQL > /

INST_ID EVENT COUNT (*)

-

1 log file parallel write 1

1 smon timer 1

1 Streams AQ: waiting for time management or cleanup tasks 1

1 Streams AQ: qmn slave idle wait 1

1 buffer busy waits 1

1 Streams AQ: qmn coordinator idle wait 1

1 SQL*Net message to client 1

1 VKTM Logical Idle Wait 1

1 pmon timer 1

1 log buffer space 1

1 Disk file operations I/O 1

1 Space Manager: slave idle wait 1

1 DIAG idle wait 2

1 rdbms ipc message 15

14 rows selected.

SQL >

We can see that there will be no wait for different sessions to update different lines in the same block. If the update is the same line, then there will be wait if it is not submitted to the situation for execution.

4. Foreign key

Session 1:

SQL > select sid from v$mystat where rownum=1

SID

-

seventy-four

SQL >

SQL > create table T1 (id number, name varchar2 (20), product_id number)

Table created.

SQL > create table T2 (id number primary key,name varchar2 (20))

Table created.

SQL > alter table T1 add constraint FK_PRODUCTID foreign key (PRODUCT_id) references T2 (ID)

Table altered.

SQL > select index_name,table_name from user_indexes where table_name='T1'

No rows selected

SQL > insert into T2 values (1maxiaa')

1 row created.

SQL > insert into T2 values (2dd')

1 row created.

SQL > insert into T2 values (3meme cc')

1 row created.

SQL > commit

Commit complete.

SQL > insert into T2 values (5mcc')

1 row created.

Not submitted.

Session 2:

SQL > select sid from v$mystat where rownum=1

SID

-

forty-five

SQL >

SQL > insert into T1 values (1)

Hang... The child table operation will be suspended all the time

Session 3:

SQL > l

1 select inst_id

2 sid

3 chr (bitand (p1,-16777216) / 16777215) | |

4 chr (bitand (p1, 16711680) / 65535) "Name"

5 (bitand (p1, 65535)) "Mode"

6 event

7 sql_id

8 blocking_session

9 FINAL_BLOCKING_SESSION

10 from gv$session

11 * where event like 'enq%'

SQL > /

INST_ID SID Name Mode EVENT SQL_ID BLOCKING_SESSION FINAL_BLOCKING_SESSION

1 45 TX 4 enq: TX-row lock contention btxh61ngubrv8 74 74

SQL > select sql_text from v$sql where sql_id='btxh61ngubrv8'

SQL_TEXT

-

Insert into T1 values (1)

SQL > select sid,serial#,username,sql_id,status from v$session where sid=74

SID SERIAL# USERNAME SQL_ID STATUS

-

74 23 HR INACTIVE

In fact, we can find that this situation exists regardless of whether the child table has primary key constraints or not, only the primary table operation is not committed.

1. The reasons are generally as follows:

1) there is a primary key or uniqueness constraint on the table, and multiple sessions operate on the same record

2) there is a reading of the main foreign key in the table, the main table is not submitted, so the child table must wait.

3) there is a bitmap Index on the table, which is the same as the existence of duplicate values in uniqeue index, in which one session operation and other sessions must wait.

4) the table is self-associated with foreign keys, and the previous transaction is not committed, which will cause the subsequent session to wait.

two。 For the enq: TX-row lock contention mentioned on the Internet, it may also be waiting for the index block to split. Theoretically, if you are waiting for the index block to split, then it should be accompanied by enq: TX-index contention waiting for the event to occur.

3. For enq: TX-row lock contention, when querying through the v$session view, the waiting session band lock mode is usually 4, while the blocker session band lock mode is usually 6, and the sql_id for querying blocker sessions is generally empty. This is a normal phenomenon, v$session shows the current state, not historical data.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.