Oracle10genq: TX

In 10 Gb, enqueueTX waits for four types: 1.enq: TX

In 10g, enqueue TX waits are divided into four categories: 1. enq: TX-row lock contention 2. enq: TX-index contention 3. enq: TX-ITL 4. enq: T

In 10g, the enqueue TX wait is divided into four types:
1. enq: TX-row lock contention
2. enq: TX-index contention
3. enq: TX-ITL
4. enq: TX-contention
The first three types of contention are obvious, and the first 4th types indicate other types of transaction contention, that is, all except the first three types are included in them.
Enq: TX-contention may occur in many cases. For example, if DML is executed in one session and alter tablespace XXX read only is executed in another session, the waiting event will appear.
Test conditions:
Single Instance status:
Session 1:
SQL> select sid from v $ mystat where rownum <2;
SID
----------
145
SQL> select table_name, tablespace_name from user_tables where table_name = 'info ';
TABLE_NAME TABLESPACE_NAME
------------------------------------------------------------
INFO USERS
SQL> update info set note = upper (note );
35 rows have been updated.
SQL> (note not submitted)
Session 2:
SQL> select sid from v $ mystat where rownum <2;
SID
----------
148
SQL> alter tablespace users read only;
Because session 1 is not submitted and users tablespace is still in use, session 2 waits.
Session 3:
SQL> select sid, event, p1, p2, p3 from v $ session_wait where sid = 148;
Sid event P1 P2 P3
-----------------------------------------------------------------------------------------------------
148 enq: TX-contention 1415053316 65563 166

The query finds that session 2 is waiting for the enq: TX-contention event. The meanings of p1, p2, and p3 can be obtained from the following view.
SQL> SELECT name, parameter1, parameter2, parameter3 from v $ event_name where name = 'enq: TX-contention ';
NAME PARAMETER1 PARAMETER2 PARAMETER3
-------------------------------------------------------------------------------------
Enq: TX-contention name | mode usn <16 | slot sequence
From the above results, we can see that:
Parameter1 indicates the name and mode of enqueue. The high 16 bits of parameter2 represent the xidusn of the transaction, the low 16 bits represent the xidslot of the transaction, and parameter3 represent the xidsqn of the transaction, that is, p2, and p3 represent a specific transaction.
Combined with v $ transaction and v $ session, you can know the session information that blocks session 2.
Check the name and mode of enqueue
SQL> SELECT sid, CHR (BITAND (p1,-16777216)/16777215) |
2 CHR (BITAND (p1, 16711680)/65535) enq,
3 DECODE (BITAND (p1, 65535), 1, 'null', 2, 'sub-share ',
4 3, 'sub-Exclusive ', 4, 'shares', 5, 'share/Sub-Exclusive ',
5 6, 'clusive ', 'other') lock_mode
6 FROM v $ session_wait WHERE sid = 148;
Sid enq LOCK_MODE
------------------------------
148 TX Share
Check sessions that are blocked in session 2:
SQL> select sid from v $ session where taddr in
2 (select B. addr from v $ session_wait a, v $ transaction B
3 where a. event = 'enq: TX-contention 'and trunc (a. p2/power (2, 16) = xidusn
4 and (bitand (a. p2, to_number ('ffff', 'xxxx') + 0) = xidslot and a. p3 = xidsqn );
SID
-------
145

Here we can see that the transaction that causes session 2 to wait is executed by session 1.
Here we can use another method to find the session blocking session 2:
1. First, check the session 2 request and transaction lock:
SQL> select sid, id1, id2, trunc (id1/power (2, 16) rbs, bitand (id1, to_number ('ffffff', 'xxxx') + 0 slot, id2 seq, lmode, request, type from v $ lock where type = 'tx 'and sid = & sid;
Input sid value: 148
Original Value 2: from v $ lock where type = 'tx 'and sid = & sid
New Value 2: from v $ lock where type = 'tx 'and sid = 148
SID ID1 ID2 RBS SLOT SEQ LMODE REQUEST TY
-------------------------------------------------------------------------------
148 65563 166 1 27 166 0 4 TX
148 196646 168 3 38 168 6 0 TX
148 wait for the lock of request 4 (share.
Note that ID1 and ID2 here are exactly the same as p2 and p3 in v $ session_wait, indicating a transaction.
SQL> select sid, event, p1, p2, p3 from v $ session_wait where sid = 148;
Sid event P1 P2 P3
-----------------------------------------------------------------------------------------------------
148 enq: TX-contention 1415053316 65563 166
2. view the locks used by transactions (ID1, ID2 ).
SQL> select sid, trunc (id1/power () rbs, bitand (id1, to_number ('ffff', 'xxxx') + 0 slot, id2 seq, lmode, request
From v $ lock where type = 'tx 'and id1 = & id1 and id2 = & id2;
Input id1 value: 65563
Input id2 value: 166
Original Value 2: from v $ lock where type = 'tx 'and id1 = & id1 and id2 = & id2
New Value 2: from v $ lock where type = 'tx 'and id1 = 65563 and id2 = 166
SID RBS SLOT SEQ LMODE REQUEST
---------------------------------------------------------
148 1 27 166 0 4
145 1 27 166 6 0
Here we can see that 148 is requesting the share transaction lock, while 145 holds the exclusive transaction lock, thus causing session 2 to wait.
RAC: similar to a single instance
Run session on node 1:
SQL> conn test/test
Connected.
SQL> select sid from v $ mystat where rownum <2;
SID
----------
617
SQL> insert into info values (1 );
1 row created.
Do not submit

Run session on node 2:
SQL> alter tablespace test read only;
Wait.
Run the following command on any node:
SQL> select c. inst_id, c. sid
From gv $ session_wait a, gv $ transaction B, gv $ session c
Where a. event = 'enq: TX-contention'
And trunc (a. p2/power (2, 16) = B. xidusn
And (bitand (a. p2, to_number ('ffff', 'xxxx') + 0) = B. xidslot
And a. p3 = B. xidsqn
And c. taddr = B. addr;
INST_ID SID
--------------------
1 617

Enqueue (queue waiting ):
Enqueue is a lock mechanism to protect shared resources. This locking mechanism protects shared resources to avoid data corruption due to concurrent operations. For example, it protects a row of records by locking to avoid simultaneous updates by multiple users. Enqueue uses a queuing mechanism, that is, FIFO (first-in-first-out), to control resource usage.

Before Oracle 10 Gb, The Enqueue event is a collection of lock events. If the wait event in the database is significant, we need to further track which category of lock caused the database wait.

From Oracle 10 Gb, Oracle segments queue waits. The v $ event_name view can query the wait events after these segments. The following is a brief example:

Sys @ CCDB> select name, wait_class
2 from v $ event_name
3 where name like '% enq % ';
NAME WAIT_CLASS
-------------------------------------------------------------
Enq: PW-flush prewarm buffers Application
Enq: RO-contention Application
Enq: RO-fast object reuse Application
Enq: KO-fast object checkpoint Application
Enq: TM-contention Application
Enq: ST-contention Configuration
Enq: TX-row lock contention Application
Enq: TX-allocate ITL entry Configuration
Enq: TX-index contention Concurrency
Enq: TW-contention Administrative
Enq: HW-contention Configuration
......

Oracle locks can be divided into Exclusive locks (X) and shared locks (S) by type, or a combination of locks. Exclusive lock (X) is also called an exclusive lock. Before an exclusive lock is released, no other types of lock can be applied to an object, other types of shared locks can be applied to objects, but exclusive locks cannot be applied.

If you divide the lock into DML locks, DDL locks, and memory locks according to the transaction type (also known as Latch ). Oracle uses Enqueue wait in the database to record the lock, and uses Latch Free wait event to record the Latch. Enqueue waits for Common ST, HW, TX, TM, etc.

1. Most important locks: TM and TX locks
For databases, the most common lock types are TM and TX locks.

The TX lock is usually called a transaction lock. When a transaction starts, such as executing INSERT/DELETE/UPDATE/MERGE operations or using SELECT... when the for update statement is queried, the transaction lock is obtained first until the transaction ends. The TX lock of Oracle is obtained at the row level, and each data row has a lock bit (1b-Lock Byte) to determine whether the record is locked, at the same time, an ITL data structure exists in the Header of each data block to record transaction information. When you need to modify data, first, you need to obtain the rollback segment space used to store the pre-image information, and then the transaction information is also recorded on the ITL. Through the ITL, You can associate the rollback information with the data block, therefore, Oracle row-level locks are obtained on data blocks. Row-level locks only have exclusive locks and do not share the data blocks.

Tmlocks are usually called table-level locks. They can be obtained by manually issuing the lock command, or by using DML operations and select for update. Table-level locks can prevent other processes from adding X exclusive locks to tables, when you modify data, other tasks can use DDL to modify the table structure or truncate or drop tables. You can observe the lock information in the v $ lock view. The TYPE field indicates the lock TYPE. The tmlock LMODE field represents different levels of tmlocks, including 2-row-S (SS), 3-row-X (SX), 4-share (S), 5-S/Row-X (SSX) and 6-exclusive (X ).

When you execute the DML operation, first add the tmlock. If you can get the lock, continue to apply the TX transaction lock. In a session, only one TX transaction lock exists. Before commit or rollback, all DML operations of the session belong to one transaction and use one rollback segment, occupies a rollback segment transaction Slot ).

The following table uses the SCOTT user to lock a row of records. Do not submit it for the moment:

Scott @ CCDB> update emp set sal = 4000 where e-mapreduce = 7788;
1 row updated.

You can view the lock information in the v $ lock view of another session;

Sys @ CCDB> select sid, username from v $ session where username = 'Scott ';
SID USERNAME
----------------------------------------
1075 SCOTT
Sys @ CCDB> select * from v $ lock where sid = 1075;
Addr kaddr sid ty ID1 ID2 LMODE REQUEST CTIME BLOCK
--------------------------------------------------------------------------------------------------------
2017100008f836260 2017100008f8362b8 1075 AE 99 0 4 0 1208 0
20172ba14e74a7f8 20172ba14e74a858 1075 TM 69539 0 3 0 16 0
2017100008df49a30 2017100008df49aa8 1075 TX 65551 30498 6 0 16 0

At this time, the row-level exclusive lock on the table will block the DDL statement for the table:

Sys @ CCDB> truncate table scott. emp;
Truncate table scott. emp
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

In addition, TM-locked ID1 represents the locked object number:

Sys @ CCDB> select owner, object_name from dba_objects where object_id = 69539;
OWNER OBJECT_NAME
------------------------------
SCOTT EMP

The TX lock ID1 represents the transaction rollback segment number and transaction slot number, and ID2 represents the sequence number:

Sys @ CCDB> select trunc (65551/power (65551), mod (, power () from dual;
TRUNC (65551/POWER (65551) MOD (, POWER ))
----------------------------------------------
1 15

You can also find the transaction information through the v $ transaction View (note that XIDSQN is exactly the ID2 information of the TX lock ):

Sys @ CCDB> select XIDUSN, XIDSLOT, XIDSQN from v $ transaction;
XIDUSN XIDSLOT XIDSQN
------------------------------
1 15 30498

If the rollback segment information is analyzed and combined with the ITL transaction slot, you can clearly see the meaning of the lock and the entire transaction processing process.

2. The most common locks: MR and AE locks
Many of my friends may have noticed that in the v $ lock view, the most common is the MR lock, that is, the Media Recovery ):

Sys @ CCDB> select * from v $ lock where type = 'Mr ';
Addr kaddr sid ty ID1 ID2 LMODE REQUEST CTIME BLOCK
--------------------------------------------------------------------------------------------------------
201710000bc2ee378 201710000bc2ee3d0 1097 MR 1 0 4 0 6984045 0
201710000bc2ee448 201710000bc2ee4a0 1097 MR 2 0 4 0 6984045 0
201710000bc2ee518 201710000bc2ee570 1097 MR 3 0 4 0 6984045 0
201710000bc2ee5e8 201710000bc2ee640 1097 MR 4 0 4 0 6984045 0
201710000bc2ee6b8 201710000bc2ee710 1097 MR 5 0 4 0 6984045 0
201710000bc2ee788 201710000bc2ee7e0 1097 MR 6 0 4 0 6984045 0
201710000bc2ee858 201710000bc2ee8b0 1097 MR 7 0 4 0 6984045 0
201710000bc2ee940 201710000bc2ee998 1097 MR 8 0 4 0 6984045 0
201710000bc2eea10 201710000bc2eea68 1097 MR 201 0 4 0 6984045 0
201710000bc2f12f8 201710000bc2f1350 1097 MR 9 0 4 0 1132526 0
10 rows selected.

The MR lock is used to protect database files so that files cannot be recovered when the database is opened or when the tablespace is Online. When the process recovers data files, it needs to obtain the MR lock exclusive. When the database is opened, an MR lock is assigned to each file. Note that in the above output, ID1 represents the file number, which also contains the temporary file No. 201.

Starting from the Oracle Database 11g, apart from obtaining the MR lock for each file, each session logged on to the Database now obtains an AE lock by default:

Sys @ CCDB> select * from v $ lock where type = 'AE' and rownum <= 5;
Addr kaddr sid ty ID1 ID2 LMODE REQUEST CTIME BLOCK
--------------------------------------------------------------------------------------------------------
201710000bc2edf68 201710000bc2edfc0 822 AE 99 0 4 0 2761930 0
201710000bc2ee108 201710000bc2ee160 946 AE 99 0 4 0 3458645 0
201710000bc2ee1d8 201710000bc2ee230 1003 AE 99 0 4 0 207674 0
201710000bc2ee2a8 201710000bc2ee300 1092 AE 99 0 4 0 6984538 0
201710000bc2eeae0 201710000bc2eeb38 991 AE 99 0 4 0 3458644 0

Currently, MR Locking and AE locking are the most common locks in databases.

Sys @ CCDB> select name from v $ event_name where name like '% AE % ';
NAME
------------------------------------------------------------
Enq: AE-lock

3. ST (space transaction lock)
The ST lock is mainly used for the interval allocation of the tablespace (DMT) for space management and dictionary management. In DMT, it is typical for the contention of uet $ and fet $ data dictionary tables. For versions that support LMT. Use local tablespace management as much as possible, or manually pre-allocate a certain number of zones (Extent) to reduce severe queue competition during dynamic expansion.

The following cases illustrate the serious performance problems that ST locks may cause.

DB Name DB Id Instance Inst Num Release OPS Host
---------------------------------------------------------------------------
DB 40757346 tqgzs 1 8.1.7.4.0 NO server
Snap Id Snap Time Sessions
---------------------------------
Begin Snap: 2845-03 02:10:16 46
End Snap: 2848-10-03 03:40:05 46
Elapsed: 89.82 (mins)

For a Statspack report, the sampling time is a very important dimension. Any waiting time is insufficient for reference.

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
---------------------------------------------------------------------------
Enqueue 53,793 16,192,686 67.86
Rdbms ipc message 19,999 5,927,350 24.84
Pmon timer 1,754 538,797 2.26
Smon timer 17 522,281 2.19
SQL * Net message from client 94,525 520,104 2.18
-------------------------------------------------------------

In Statspack analysis, Top 5 wait events are the most important part. In this system, except for the enqueue wait event, the other four are idle wait events, so you do not need to pay attention to them. Let's take a look at the enqueue wait event. During the sampling interval of 89.82 (mins), the total number of enqueue waits for up to 16,192,686 (cs), that is, about 45 hours. This wait is too significant. In fact, this system is also experiencing great difficulties. after observing the waiting queue, we should pay attention to the waiting resources of the queue. For more information about the quick jump Statspack, see the following details:

Enqueue activity for DB: DB Instance: aaa Snaps: 2845-2848
-> Ordered by waits desc, gets desc
Enqueue Gets Waits
--------------------------------
ST 1,554 1,554
-------------------------------------------------------------

We can see that the main queue is waiting for the ST to lock. For DMT, we say this wait is closely related to the contention between FET $ and UET $. Let's look back at the capture of SQL statements:

-> Endbuffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL nodes des the resources used
All SQL statements called within the PL/SQL code. As inpidual SQL
Statements are also reported, it is possible and valid for the summed
Total % to exceed 100.
Buffer Gets Executions Gets per Exec % Total Hash Value
------------------------------------------------------------
4,800,073 10,268 467.5 51.0 2913840444
Select length from fet $ where file #=: 1 and block #=: 2 and ts #=: 3
803,187 10,223 78.6 8.5 528349613
Delete from uet $ where ts # =: 1 and segfile # =: 2 and segblock # =: 3
Nd ext # =: 4
454,444 10,300 44.1 4.8 1839874543
Select file #, block #, length from uet $ where ts # =: 1 and segfile # =:
2 and segblock # =: 3 and ext # =: 4
23,110 10,230 2.3 0.2 3230982141
Insert into fet $ (file #, block #, ts #, length) values (: 1,: 2,: 3,: 4)
21,201 347 61.1 0.2 1705880752
Select file # from file $ where ts # =: 1
....
9,505 12 792.1 0.1 1714733582
Select f. file #, f. block #, f. ts #, f. length from fet $ f, ts $ t whe
Re t. ts # = f. ts # and t. dflextpct! = 0 and t. bitmapped = 0
6,426 235 27.3 0.1 1877781575
Delete from fet $ where file #=: 1 and block #=: 2 and ts #=: 3

As you can see, frequent database operations on UET $ and FET $ system tables have become the main bottlenecks of the system.

So far, the problem can be accurately located for the system, and the corresponding solution is easily identified. Using LMT in Oracle 8.1.7 to replace DMT is the fundamental solution to the problem, of course, the implementation should be considered comprehensively, and the actual situation should be much more complicated.

-The End-