TX lock analysis

Two days ago, we saw some 00060 (deadlock) alarms in the alert logs. I checked the log file and found some strange phenomena. For example, some locks are generated during insert, and some deadlocks are generated for the same object. So while solving these problems, we carefully studied the Tx lock and summarized various situations that produced the Tx lock.

Data Record locked
We know that all the transactions in Oracle are produced by row-level locks. That is to say, when a transaction performs update operations (update and delete) on the table, it only locks the data records that need to be updated in the data block. This type of lock is our most common lock. See the following example:

SQL> Create Table t_lock (a number, B varchar2 (20), c char (10) initrans 1 maxtrans 3; Table created SQL> insert into t_lock values (1, 1, 1 ); 1 row inserted SQL> commit; Commit complete Session 1:

SQL> Update t_lock Set B = '2' where a = 1; 1 row updated Session 2:

SQL> Delete t_lock where a = 1; Session 1 locks the record with a = 1. When the session tries to delete the record, it is hung.

SQL> select * From dba_waiters;

Waiting_session holding_session lock_type mode_held unlock lock_id1 lock_id2 blocks ----------- ------------------ -------- 28 20 transaction exclusive 1048671 40361 and if the t_lock data block is dumped, you can also see the lock flag on the record row.

TL: 19 FB: -- H-FL -- LB: 0x1 cc: 3 where 0 X1 corresponds to the entry number of the transaction that generates the lock in the ITL list of the data block.

The lock generated when the ITL entry in the data block reaches the maximum limit
ITL (Interested Transaction list) is simply a transaction column that is "interested" in the data block. That is, the transaction that modifies the data block. The maximum number of transactions that can be updated on a data block of a table at the same time is specified by the maxtrans parameter during table creation. However, if the table is created on the tablespace Based on the 9i new feature assm (automatic segment space management), the specified maxtrans will be invalid, and all tables will be 255. For example, in the preceding example, maxtrans is set to 3 (the tablespace is not an assm), so at the same time, only three transactions can be used to update a data block. Let's look at the example below.

Insert more data into the preceding table:

SQL> insert into t_lock values (2, 2); 1 row inserted SQL> insert into t_lock values (3, 3); 1 row inserted SQL> insert into t_lock values (4, 4, 4); 1 row inserted SQL> insert into t_lock values (5, 5); 1 row inserted SQL> commit; Commit complete Session 1:

SQL> Update t_lock Set B = '2' where a = 1; 1 row updated Session 2:

SQL> Update t_lock Set B = '2' where a = 2; 1 row updated Session 3:

SQL> Update t_lock Set B = '2' where a = 3; 1 row updated Session 4:

SQL> Update t_lock Set B = '2' where a = 4; 3 ITL slots are occupied before 1 row updated, and hung is occupied when 4th transactions apply for ITL again.

SQL> select * From dba_waiters;

Waiting_session holding_session lock_type mode_held unlock lock_id1 lock_id2 ------ ------------------ -------- 19 27 Transaction exclusive 1048671 40361 dump data block, you can see that ITL has reached the maximum number of restricted items:

SEG/obj: 0x87c0 CSC: 0x00. a337dee9 ITC: 3 flg:-Typ: 1-data FSL: 0 fnx: 0x0 Ver: 0x01 ITL Xid UBA flag lck scn/fsc0x01 0x0008. 053.20.9ef3 0x00802e6a. 71b4. 2b ---- 1 FSC 0x0000.000000000x02 0x000d. 037.20.a98a 0x0080655a. ac1f. 2b ---- 1 FSC 0x0000.000000000x03 0x000c. 004.20.acb9 0x008006a6. 06bc. 28 ---- 1 FSC 0x0000.00000000 here we need to raise another question. If the transaction operation is not an update or delete operation, but an insert operation, will it reach the limit of maxtrans? Let's take a test:

Session 1:

SQL> Update t_lock Set B = '2' where a = 1; 1 row updated Session 2:

SQL> Update t_lock Set B = '2' where a = 2; 1 row updated Session 3:

SQL> Update t_lock Set B = '2' where a = 3; 1 row updated Session 4:

SQL> insert into t_lock values (6, 6); 1 row inserted at this time, although the ITL entries have been allocated to three transactions, however, the fourth transaction was not hung during the insert operation.

Check the dump content of the data block:

Object ID on block? Y seg/obj: 0x87c0 CSC: 0x00. a337df1b ITC: 3 flg: O Typ: 1-data FSL: 0 fnx: 0xa03000c Ver: 0x01 ITL Xid UBA flag lck scn/fsc0x01 0x0003. 00b. running a3ab 0x0080116c. 0b0f. 2a ---- 1 FSC 0x0000.000000000x02 0x0000. 000.00000000 0x00806a5a. as1e. 2b ---- 1 FSC 0x0000.000000000x03 0x000e. 037.109a458 0x14401ff2. 17d8. 2b ---- 1 FSC 0x0000.00000000 we found that only three transactions are on this data block. What about another transaction? Don't worry. Dump the next data block to see it:

Object ID on block? Y seg/obj: 0x87c0 CSC: 0x00. a337df22 ITC: 1 flg: O Typ: 1-data FSL: 0 fnx: 0x0 Ver: 0x01 ITL Xid UBA flag lck scn/fsc0x01 0x0004. 038.20.a6dc 0x00806fbf. bb4e. 0e ---- 1 FSC 0x0000.00000000 originally! When performing the insert operation, if you find that the data block has reached the maximum limit of maxtrans, you can retrieve the next idle data block from freelist for the insert operation, regardless of whether the data block has reached the limit of pctused.

The maxtrans limit is reached when there is an index on the table.
The above test is performed when there is no index on the table. When an index is created on a table, it becomes much more complicated.

 

· Scenario 1:
SQL> drop table t_lock; Table dropped SQL> Create Table t_lock (a number, B varchar2 (20), c char (10) initrans 1 maxtrans 5; table created SQL> insert into t_lock values (1, 1); 1 row inserted SQL> insert into t_lock values (2, 2 ); 1 row inserted SQL> insert into t_lock values (3,3, 2); 1 row inserted SQL> insert into t_lock values (4,4, 2 ); 1 row inserted SQL> insert into t_lock values (5, 5, 1); 1 row inserted SQL> commit; Commit complete SQL> Create index t_lock_idx on t_lock (a) maxtrans 3; index created Session 1:

SQL> Delete from t_lock where a = 1; 1 row deleted Session 2:

SQL> Delete from t_lock where a = 2; 1 row deleted (Note: all the above operations will index columns)

 

Session 3:

SQL> Update t_lock2 Set B = 2 Where B = 2; 1 row updated (note: this session does not involve index columns)

 

Session 4:

SQL> Delete from t_lock where a = 3;

4th sessions are hung:

SQL> select * From dba_waiters;

Waiting_session holding_session lock_type mode_held mode_requested lock_id1 lock_id2 --------------- -------- 19 20 transaction exclusive share 393284

Maxtrans on our table is 5, which is not reached here, while maxtrans on index is 3, and 4th transactions are hung. Here, we can draw a conclusion: if there is an index on the table, if it reaches the maxtrans limit on the index, the subsequent transactions will be hung. However, please note that the operations of the 3rd sessions do not involve index columns. What if I remove the 3rd sessions?

· Scenario 2:
Session 1:

SQL> Delete from t_lock where a = 1; 1 row deleted

Session 2:

SQL> Delete from t_lock where a = 2; 1 row deleted (Note: all the above operations will index columns)

 

Session 3:

SQL> Delete from t_lock where a = 3;

3rd sessions are hung:

SQL> select * From dba_waiters;

Waiting_session holding_session lock_type mode_held mode_requested lock_id1 lock_id2 ---- --------------- --------- -------- 27 20 transaction exclusive share 1048605

Here, 3rd sessions are occupied by hung. That is to say, in the case of index, the ITL number is limited to Minx (maxtrans_of_index1-1, maxtrans_of_index2-1..., maxtrans_of_table ).

 

As mentioned above, if we find that the ITL number of data blocks has reached the limit of maxtrans during insert, We will insert a new data block to avoid session blocking. So after the index is available, is it still like this? See the following situation.

 

· Scenario 3:
Session 1:

SQL> Delete from t_lock where a = 1; 1 row deleted

Session 2:

SQL> Delete from t_lock where a = 2; 1 row deleted (Note: all the above operations will index columns)

 

Session 3:

SQL> insert into t_lock values (6, 6 );

3rd sessions are hung:

SQL> select * From dba_waiters;

Waiting_session holding_session lock_type mode_held mode_requested lock_id1 lock_id2--------------- --------------- -------- 19 20 transaction exclusive share 131099 40859

As we can see, although a maxtrans-compliant session is an insert operation, it is still blocked. It is not intended to re-allocate a block without an index. I think oracle is doing this to keep the index block compact. Otherwise, imagine that if the index block is distributed like the data block, the significance of index scan is not significant.

 

The index condition is complex, so it is difficult to know how to handle it in Oracle. However, although logically analyzed, the locked object should be an index, but it is found in the V $ locked_object view. When Oracle locks, it regards the index as a part of the table rather than an independent resource (in fact, if the index is dumped out, its row lock mechanism should be similar to that on the data block. The difference is that when performing the update operation, the index block is first deleted, then insert, but the data block is directly updated )?

Bitmap Index Entry locked
If the index is a bitmap index, a TX lock can also be generated.

We know that bitmap index is generally based on a field with a small number of distinct values. Oracle creates a structure similar to a binary bitmap index. Each entry in the bitmap corresponds to a value. Each value corresponds to multiple rowids. Similar to the following:

0: rowid_1, clerk, rowid_11, rowid_20... 1: rowid_2, rowid_3, rowid_7, rowid_13... 2: rowid_4, rowid_6, rowid_8, rowid_12 ......

If multiple sessions need to update the bitmap index columns of different records corresponding to an entry at the same time, competition will occur, resulting in blocking of sessions. Take the following example:

SQL> Create bitmap index t_lock_bmidx on t_lock (c); index created Session 1:

SQL> Update t_lock set C = 0 where a = 1; 1 row updated

Session 2:

SQL> Update t_lock set C = 0 where a = 5;

Session 2 is hung.

SQL> select * From dba_waiters;

Waiting_session holding_session lock_type mode_held mode_requested lock_id1 lock_id2 ----------------- --------------------- -------- 27 20 transaction exclusive share 196635

Note that the records updated by the preceding two transactions are a = 1 and A = 5, and their C values are both 1. Therefore, the 2nd sessions are hung.

 

TX lock caused by unique constraints
The Uniqueness constraint is very simple, that is, to ensure that the bound field does not have more than one duplicate record. Therefore, the transaction will add the corresponding TX lock to ensure that the constraints are not violated. If the results of the two transactions commit will cause a constraint conflict, the subsequent sessions will be blocked.

SQL> ALTER TABLE t_lock 2 add constraint t_lock_pk primary key (a); table altered

Session 1:

SQL> insert into t_lock values (6, 6); 1 row inserted

Session 2:

SQL> Update t_lock set a = 6 where a = 1;

We can see that 2nd sessions have been hung.

SQL> select * From dba_waiters;

Waiting_session holding_session lock_type mode_held mode_requested lock_id1 lock_id2--------------- --------------- -------- 27 28 transaction exclusive share 196683 41866

Others
In fact, there are some other situations when the Tx lock is generated. All of the above are common situations and situations.

The TX lock is generated to control concurrent processes and data integrity. The generation of TX locks in the system should be very common (if you query the V $ lock view in the production system, you can see a lot of TX locks ). The key issue is how to ensure that small transactions are not blocked by the Tx lock of large transactions, and how to avoid deadlocks. We hope that the above analysis will help you locate and solve these problems.