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

This article mainly explains "the principle of Oracle locking mechanism". Interested friends may wish to take a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn the principle of Oracle locking mechanism.

I. the dynamic performance views on locks in Oracle are mainly as follows:

V$LOCK

This view lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.

The ID1 field of the TM lock represents the ID number of the object, and the specific object can be found through DBA_OBJECTS.OBJECT_ID.

The ID1 of the TX lock represents the rollback segment number and transaction slot number of the transaction, and the ID2 represents the sequence number. (V$TRANSACTION.XIDSQN represents transaction slot number)

For the meaning of VLOCK.ID1 and ID2, please refer to the article: http://space.itpub.net/?uid-23135684-action-viewspace-itemid-715468

If the BLOCK field is equal to 1, this lock blocks the execution of other DML statements, which should normally be 0.

The meaning of the BLOCK field:

0, 'Not Blocking', / * Not blocking any other processes * /

1, 'Blocking', / * This lock blocks other processes * /

2, 'Global', / * This lock is global, so we can't tell * /

$ORACLE_HOME/rdbms/admin/catblock.sql

Special note: 0 means no blocking, not no lock.

If you want to query whether there are sessions blocking other sessions:

SELECT * FROM V$LOCK WHERE BLOCK=1

You can also see that other sessions are blocked by querying the two fields of blocking session N.BLOCKING session.

If the LMODE is 0 and request is greater than 1, it means that the acquisition of the lock failed and blocking of the corresponding lock occurred. For example, when a DML statement is executed, if TYPE=TM,LMODE=0,REQUEST=3, it means that the DML statement is blocked and waiting while acquiring an exclusive lock on the row of the table's TM lock.

Find the transaction session information that blocks the transaction:

Select * from v$lock where (id1,id2) = (SID of select id1,id2 from v$lock where sid= locked session)

V$LOCKED_OBJECT

This view lists all locks acquired by every transaction on the system.

V$LOCK_TYPE

two。 Among them, what we mainly look at is:

LOCK TYPE (by object)

V$LOCK_TYPE keeps all the information about LOCK TYPE.

The main ones are:

TM-DML enqueue

TX-Transaction enqueue

UL-User supplied

MR-Media Recovery

MR locks are used to protect data files so that they cannot be restored when the database is opened and the tablespace Online. When the process recovers the data file, it needs to acquire the MR lock exclusively. When the database is open, a MR lock is assigned to each file. ID1 stands for the file number and also contains 201 temporary files.

Starting with Oracle 11g, each logged-in session has a default AE lock.

LOCK MODE (by type)

0-none

1-null (NULL)

2-row-S (SS) / / Line sharing

3-row-X (SX) / / Line exclusive

4-share (S) / / sharing

5-S/Row-X (SSX) / / shared line exclusive

6-exclusive (X) / / exclusive

From the above LOCK MODE, we can also see that there are only two kinds of locks by type, shared (S) and exclusive (X), and their different combinations become different LOCK MODE. If an X lock is added, no other locks can be added to this row or table.

TM is the DML lock, the lock at the table level. TX is a transaction lock and a row-level lock. When performing a DML operation, add a TM lock to the table first, and then add a TX lock if the lock is successful. In general, there is only one TX lock in a session, and there may be multiple TM locks, and these TM share a single TX lock. Different statements have different types of TM locks, and the type is the LOCK MODE described below. TM locks are also added at the table level to prevent other sessions from adding exclusive locks to the table (such as executing DDL statements on the table). Multiple TM locks and TX locks can be added to a table. For example, A session updates a record on the TEST table with ID number 1, first adding a TM lock to the table, and successfully adding a TX lock to the row with ID 1. Blocking occurs if there is another session to update the record with ID 1 as well, because adding a TM lock succeeds, but adding a TX lock fails. If the updated record is ID=2, then it is successful. There are multiple TM and TX locks on the table. If the TM lock is successfully added to the table, then in addition to the corresponding record in the V$LOCK, the V$LOCKED_OBJECT also has the corresponding object record.

Row-level locks only exclusive locks do not share locks.

In addition, select statements do not add any locks, so normal TX,TM locks do not block the execution of select statements. The only thing that can block the execution of select statements is the latch lock. Once the latch lock blocks the execution of the select statement, it will be very large for the normal operation of the system, and even lead to the collapse of the system.

three。 Example

Example 1:

SQL > update test set segment_name='test' where wner='SYS'

4044 rows were updated.

SQL > select sid,type,lmode from v$lock where sid=128

SID TY LMODE

128 TM 3

128 TX 6

SQL > select session_id,locked_mode from v$locked_object

SESSION_ID LOCKED_MODE

--

128 3

Example 2:

SQL > select * from test for update

SQL > select sid,type,lmode from v$lock where sid=36

SID TY LMODE

36 TM 3

36 TX 6

SQL > select session_id,locked_mode from v$locked_object

SESSION_ID LOCKED_MODE

--

36 3

Sometimes the query v$LOCK will find that there are only TM locks and no TX locks, that is because 0 records are operated when executing DML statements or select... for update statements, so only TM locks are added, and no rows need to be locked with TX.

four。 Add locks manually:

From low to high level, SS can add all types of locks except X. The X lock cannot add any other locks.

The S lock of TM is added when the index is created, and the mode value is 4. No DML statement is allowed to be executed at this time, because no other exclusive type of TM lock can be added to the table. Of course, we can specify the ONLINE keyword when creating the index to avoid blocking DML statements.

/ / Line sharing SS

LOCK TABLE TABLE_NAME IN ROW SHARE MODE

/ / Line exclusive SX

LOCK TABLE TABLE_NAME IN ROW EXCLUSIVE MODE

/ / shared Lock S

LOCK TABLE TABLE_NAME IN SHARE MODE

/ / shared line exclusive SSX

LOCK TABLE TABLE_NAME IN SHARE ROW EXCLUSIVE MODE

/ / exclusive lock X

LOCK TABLE TABLE_NAME IN EXCLUSIVE MODE

Release the lock through ROLLBACK or COMMIT.

five。 Related parameters:

Dml_locks=0 (dml_locks related meaning, please see another article: http://space.itpub.net/23135684/viewspace-626728)

SQL > update test set segment_name='test' where wner='SYS'

4044 rows were updated.

SQL > select sid,type,lmode from v$lock where sid=128

SID TYPE LMODE

-

128 TX 6

SQL > select session_id,locked_mode from v$locked_object

No rows selected

SQL > drop table test

Drop table test

*

An error occurred on line 1:

ORA-00062: unable to get DML full table lock; DML_LOCKS is 0

This proves that if dml_locks=0 executes the dml statement, the table-level TM lock no longer exists, but the row-level TX transaction lock still exists. Since no locks can be added at the table level, the DDL statement cannot be executed to operate on the table.

At this point, I believe that you have a deeper understanding of the "lock mechanism principle of Oracle", you might as well come to the actual operation! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.