Shulou(Shulou.com)05/31 Report--

This article shows you how to analyze the waiting event enq TX row lock contention. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Typically, the wait event enq: TX-row lock contention for the Oracle database occurs in the following three situations.

The first case is a real row lock conflict in business logic, such as when a record is modified by multiple people at the same time. The request mode for this lock is 6.

The second case is a unique key conflict, such as multiple records with the same primary key field being inserted at the same time. The request mode for this lock is 4. This is also a problem of application logic.

The third case is the update conflict of the bitmap index, where multiple sessions update the same block of the bitmap index at the same time. The corresponding request mode for the session request lock is 4.

The physical structure of an bitmap index is the same as a normal index, which is also a B-tree structure. But the logical structure of the data records it stores is "key_value,start_rowid,end_rowid,bitmap".

The content is similar to this:

"'8088 pencils 00000000000pr 10000034441pr 1001000100001111000"

Bitmap is a binary that represents a record from START_ROWID to END_ROWID, 1 means equal to key_value, that is, the ROWID record of '8088', and 0 indicates that it is not the record.

After understanding the structure of the bitmap index, we can understand that when multiple records are inserted into a table with a bitmap index at the same time, the record in a block in the bitmap index is updated at the same time, which means that a certain record is updated at the same time, and row lock waiting occurs naturally. The greater the insertion concurrency, the more serious the wait.

Wait event enq: enq in TX-row lock contention is an abbreviation for enquence. Enquence is an internal lock that coordinates access to database resources.

All wait events that start with "enq:" indicate that the session is waiting for the internal lock held by another session to be released, and its name format is enq:enqueue_type-related_details. The enqueue_type here is TX,related_details and row lock contention. The database dynamic performance view v$event_name provides a list of all wait events that start with "enq:".

Although you see a lot of enq: TX-row lock contention waiting in awrrpt, these are hindsight messages. According to AWRRPT, we can't just wait for what the request pattern for the event is, 6 or 4.

If there is an enq: TX-row lock contention wait in the database, you can look at views such as v$session and v$session_wait.

In v$session and v$session_wait, if the eventcolumn you see is enq: TX-row lock contention, the session is waiting for a row lock. The request pattern for the wait event can be obtained from the p1 column of v$session and v$session_wait.

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%'

With this SQL, you can convert p1 into easy-to-read text.

In view of these three situations, tests are carried out respectively:

First of all, I will prepare the test table and data.

-- create test tables and data

SQL > create table t_all_objs as select owner,object_id,object_name from all_objects where 0room1

Table created.

SQL > alter table T_ALL_OBJS add constraint pk_t_all_objs primary key (OBJECT_ID)

Table altered.

SQL > insert into t_all_objs (owner,object_id,object_name) values ('TEST',2013011701,'test1')

1 row created.

SQL > insert into t_all_objs (owner,object_id,object_name) values ('TEST',2013011702,'test2')

1 row created.

SQL > commit

Commit complete.

In the first case, different sessions update the same record at the same time

Session1:

SQL > select sid from v$mystat where rownum select * from t_all_objs

OWNER OBJECT_ID OBJECT_NAME

-

TEST 2013011701 test1

TEST 2013011702 test2

SQL > update t_all_objs set object_name='test11' where object_id=2013011701

1 row updated.

Not commit

Session 2:

SQL > select sid from v$mystat where rownum update t_all_objs set object_name='test101' where object_id=2013011701

I've been waiting.

Session 3: query

SQL > select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (46, 52)

SID SQL_ID BLOCKING_SESSION EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_CLASS STATE

- --

46 SQL*Net message from client driver id 1650815232 # bytes 1 0 Idle WAITING

52 c53uad8st2u8t 46 enq: TX-row lock contention name | mode 1415053318 usn select * from v$lock where sid in (46, 552) order by sid, type

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-

000000009398FE58 000000009398FEB0 46 AE 100 0 4 0 3706 0

00007FABEA622FC0 00007FABEA623020 46 TM 87875 0 30 597 0

0000000091E37248 0000000091E372C0 46 TX 65556 13548 6 0 597 1

000000009398F820 000000009398F878 52 AE 100 0 4 0 573 0

00007FABEA622FC0 00007FABEA623020 52 TM 87875 0 30 543 0

000000009398FBB8 000000009398FC10 52 TX 65556 13548 0 6 543 0

6 rows selected.

The query found that the session less than 46 finally blocked the session, which was the root cause.

In the second case, records with the same primary key field are inserted in different sessions at the same time.

Session 1

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

SID

-

forty-three

SQL > select * from t_all_objs

OWNER OBJECT_ID OBJECT_NAME

-

TEST 2013011701 test1

TEST 2013011702 test2

SQL > insert into t_all_objs (owner,object_id,object_name) values ('TEST',2013011703,'test1')

1 row created.

Not commit

Session 2:

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

SID

-

fifty-five

SQL > insert into t_all_objs (owner,object_id,object_name) values ('TEST',2013011703,'test11')

I've been waiting.

Session 3: query

SQL > select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (43,55)

SID SQL_ID BLOCKING_SESSION EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_CLASS STATE

- --

43 SQL*Net message from client driver id 1650815232 # bytes 1 0 Idle WAITING

55 bsddu35jkskbz 43 enq: TX-row lock contention name | mode 1415053316 usn select * from v$lock where sid in (43,55) order by sid, type

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-

000000009398F660 000000009398F6B8 43 AE 100 0 4 0 376 0

00007FABEA621F88 00007FABEA621FE8 43 TM 87875 0 3 0 266 0

0000000091E292E0 0000000091E29358 43 TX 262149 13576 6 0 266 1

000000009398F040 000000009398F098 55 AE 100 040 371 0

00007FABEA621F88 00007FABEA621FE8 55 TM 87875 0 3 0 256 0

0000000091DDB308 0000000091DDB380 55 TX 327688 13773 6 0 256 0

000000009398F900 000000009398F958 55 TX 262149 13576 0 4 256 0

7 rows selected.

The last query is that session 43 blocks other sessions, which is the root cause.

In the third case, records with the same value of bitmap index columns in different sessions

Session 1:

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

SID

-

forty-four

SQL > select * from T_ALL_OBJS

OWNER OBJECT_ID OBJECT_NAME

-

TEST 2013011701 test1

TEST 2013011702 test2

SQL > create bitmap index ind_T_ALL_OBJS on T_ALL_OBJS (owner)

Index created.

SQL > insert into t_all_objs (owner,object_id,object_name) values ('TEST',2013011703,'test11')

1 row created.

Not commit

Session 2:

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

SID

-

forty

SQL > insert into t_all_objs (owner,object_id,object_name) values ('TEST',2013011703,'test12')

I've been waiting.

Session 3: query

SQL > select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (44,40)

SID SQL_ID BLOCKING_SESSION EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAIT_CLASS STATE

- --

40 8s2tzhjpgx1nc 44 enq: TX-row lock contention name | mode 1415053316 usn select * from v$lock where sid in (44,40) order by sid, type

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-

000000009398E688 000000009398E6E0 40 AE 100 0 40 415 0

00007FABEA622FC0 00007FABEA623020 40 TM 87875 0 30 110 0

000000009398FBB8 000000009398FC10 40 TX 655390 13564 0 4 110 0

0000000091E54F48 0000000091E54FC0 40 TX 589844 13794 6 0 110 0

000000009398F3C0 000000009398F418 44 AE 100 0 4 0 410 0

00007FABEA622FC0 00007FABEA623020 44 TM 87875 0 30 126 0

0000000091E18128 0000000091E181A0 44 TX 655390 13564 6 0 126 1

7 rows selected.

The final query session 44 is the source of blocking.

Simulated failure:

Session 1:

SQL > select sid from v$mystat where rownum select * from t_all_objs

OWNER OBJECT_ID OBJECT_NAME

-

TEST 2013011701 test1

TEST 2013011702 test2

-- insert data without submitting

SQL > update t_all_objs set object_name='test11' where object_id=2013011701

1 row updated.

Session 2:

SQL > select sid from v$mystat where rownum select * from t_all_objs

OWNER OBJECT_ID OBJECT_NAME

-

TEST 2013011701 test1

TEST 2013011702 test2

-- updates to the same row of data without submission

SQL > update t_all_objs set object_name='test101' where object_id=2013011701

Wait for ing.

Session 3:

-- query tx lock session sid,row_wait_object# information

Select sid,sql_id,status,blocking_session, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='enq: TX-row lock contention'

SID SQL_ID STATUS BLOCKING_SESSION ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#

--

52 c53uad8st2u8t ACTIVE 46 87875 1 143649 0

-- query the information of locked objects:

Select object_name from dba_objects where object_id in (87875)

OBJECT_NAME

-

T_ALL_OBJS

Select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID, OBJECT_TYPE from all_objects where object_name='T_ALL_OBJS'

OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

-

SYS T_ALL_OBJS 87875 87875 TABLE

-- query the sql executed by the locked sesson

Select sql_text from v$sql where sql_id in (select sql_id from v$session where sid=52)

SQL_TEXT

-

Update t_all_objs set object_name='test101' where object_id=2013011701

-- finally query V$lock:

Select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock where block=1 or request0

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-

46 TX 524304 13916 6 0 296 1

52 TX 524304 13916 0 6 284 0

Or query the lock wait relationship between sessions through the following SQL:

Select a.sid hold_sid, b.sid wait_sid, a.type, a.id1, a.id2, a.ctime

From v$lock a, v$lock b

Where a.id1 = b.id1

And a.id2 = b.id2

And a.block = 1

And b.block = 0

HOLD_SID WAIT_SID TY ID1 ID2 CTIME

-

46 52 TX 524304 13916 2717

Or as follows

Select decode (request,0,'holder:', 'waiter:') | |

Sid session_id, id1, id2, lmode, request, type

From v$lock

Where (id1, id2, type) in (select id1, id2, type from v$lock where request > 0)

Order by id1, request

SESSION_ID ID1 ID2 LMODE REQUEST TY

-

Holder: 46 524304 13916 6 0 TX

Waiter: 52 524304 13916 0 6 TX

Finally, it is known that the session with a sid of 46 is the source of blocking. Resolve whether the contact application is a session for submission, or kill drop

The above content is how to analyze the waiting event enq TX row lock contention. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.