TM and TX Locks for Oracle

Depending on the data that is protected, Oracle's database locks fall into the following major categories:

1.DML Lock (data locks Lock) for data integrity protection;

2.DDL Lock (dictionary locks Dictionary Lock), used to protect the structure of database objects, such as the structure definition of tables and indexes;

3. Internal lock or latch (internal locks or latches) for protection of internal structures

In Oracle databases, DML locks mainly include TM and TX locks, where TM locks are called table-level locks, and TX locks are called transaction or row-level locks.

When Oracle executes DML statements, the system automatically requests a TM-type lock on the table to be operated on. When the TM lock is obtained, the system automatically requests the TX type of lock and resets the lock flag bit of the data row that is actually locked. In this way, checking the compatibility of the TX lock before the transaction is locked, it is necessary to check the compatibility of the TM lock mode and greatly improve the efficiency of the system. TM locks include SS, SX, S, X and other modes, which are represented in the database by 0-6. Different SQL operations produce different types of TM locks.

There is only an X lock (exclusive lock) on the data line. In an Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time, and the lock remains until the transaction is committed or rolled back. When two or more sessions execute DML statements on the same record on a table, the first session is locked on that record, and the other sessions are waiting. When the first session is committed, the TX lock is released and other sessions can be locked.

When a TX lock wait occurs on an Oracle database, failure to handle it often causes the Oracle database to hang or cause a deadlock to occur, resulting in ORA-60 errors. These phenomena can cause great harm to the actual application, such as long time not responding, large number of transaction failures, etc.

There are several modes of the ORACLE Lock:

0:none
1:null Empty
2:row-s line Sharing (RS): Shared table lock, Sub share
3:row-x Line Exclusive (RX): For row modification, sub exclusive
4:share shared Lock (S): block other DML operations, Share
5:s/row-x shared Row Exclusive (SRX): Block other transactional operations, Share/sub exclusive
6:exclusive Exclusive (X): Standalone access use, exclusive

We can find at least 2 things from this table. The first is what each database operation corresponds to what kind of lock, the second is between each kind of lock, if encountered after the conflict, the so-called conflict is whether the current database operation is compacted. If two operations lock on the same line, there will be a conflict, The post-operation will wait for the previous operation to complete before it is completed, otherwise it will be compacted there, and if it is not the same row, then the action will not wait. Take an example to illustrate: suppose now that a action is: Update the record of id=1, and b action: Delete the record of id=2, According to the table, the lock at the TM level when operating on a is RX,TX level only one is X, there will be a TM level lock on B at the RX,TX level with only one x, and according to the table, when Rx encounters Rx, if 2 operations are not the same record, then there will be no conflict. So AB two operations will be in accordance with their respective first TM lock, plus a TX lock, and then successfully perform their own operation, will not tamper. If the record ID of the B operation is swapped for 1, then two actions are recorded as the same record, the TM lock will exhibit a conflict, so the B operation will wait for the a operation to complete after the commit (that is, a TX lock is released), b then generate a TX lock and a TM lock to complete the operation, otherwise it will be compacted, Wait for a to release the TX lock.

CREATE TABLE chgrant_20170327 as SELECT * from Chgrant;

Update chgrant_20170327 set updatetime= ' where operno= ' HANA0026 ' and options= ' 1 ' and changeid= ' 47 ';

SELECT * from V$lock where type in (' TX ');

The results are as follows:

Execute the UPDATE statement without committing:

Update chgrant_20170327 set updatetime= ' where operno= ' HANA0026 ' and options= ' 1 ' and changeid= ' 47 ';

To see the TX lock again, the result is:

Significantly more than one lock with a SID of 867 to view all the locks for Sid 867:

SELECT * from V$lock where SID in (' 867 ');

The result is:

The result corresponds to the data of two tables:

1. Take the TM lock ID1, query the dba_objects table:

SELECT * from dba_objects where object_id= ' 3824866 ';

Results:

2. Take the addr of the TX lock and check the V$transaction table:

SELECT * from V$transaction;

Results:

Another window is opened for the delete operation, and the transaction is blocked:

Delete from chgrant_20170327 where operno= ' HANA0026 ' and options= ' 1 ' and changeid= ' 47 ';

To view the TX lock again:

SELECT * from V$lock where type in (' TX ');

Results:

A lock with a SID of 875 is added to view all locks with Sid 875:

SELECT * from V$lock where SID in (' 875 ');

Results:

Then return to view the v$lock conditions for these two new locks (Sid 867 and 875):

SELECT * from V$lock where SID in (' 867 ', ' 875 ');

Results:

Note that the block with Sid 867 (that is, the update operation) becomes ' 1 '.

Now rollback that update operation (867), view V$lock:

SELECT * from V$lock where SID in (' 867 ', ' 875 ');

Results:

867 of the lock records are visible and have been released.

At this point the delete operation of the other window is finally returned, and now it is rollback.

SELECT * from V$lock where SID in (' 867 ', ' 875 ');

Results:

The locks of two transactions are released.

Finally, add an operation that creates the lock operation when the index is created:

Querying the V$lock table while creating an index

It can be found that when the index is created, 2 TM locks are generated, and the lock categories are 4 and 3, and we query which of the 2 TMS are locked by the respective objects:

According to the results of the query, it is found that the lmode=4 object_id 55160 object corresponds to the TTT table, and lmode=4 corresponds to the TM s lock.

Original article Address:

http://blog.csdn.net/vertual/article/details/34540555

Http://blog.sina.com.cn/s/blog_95b5eb8c0101i22x.html

TM and TX Locks for Oracle