Oracle lock mechanism

Although I used to see a lot of descriptions of the Oracle lock mechanism on the Internet, I always felt that there was something wrong with it, so it took a little time to refer to Tom Tyke's "Oracle 9I/10G/11G Programming Art" to sort out the knowledge about Oracle locks.

Disclaimer: This article belongs to personal reading, content does not guarantee 100% correct.

One, the type of lock for the Oracle database:

Depending on the object being protected, the Oracle database lock can be divided into the following major categories: 1,DML lock (data locks, lock), to protect the integrity of data; 2,DDL Lock (Dictionary locks, Dictionary Lock), used to protect the structure of database objects, such as tables, indexes, such as the structure of the definition; 3, Internal lock and latch (internal locks and latches), protect the internal structure of the database. Second, the following three kinds of lock structure are discussed in turn:  1.DML LockDML locks mainly include TM and TX locks, where TM locks are called intent or table-level locks, and TX locks are called row-level locks or transaction locks. 1.1 TM LockThe TM Lock contains the following types:

TM lock compatibility is as follows:

1.2 TX Lock

the original meaning of TX is transaction (transaction), when a transaction executes the data change for the first time (Insert, Update, Delete) or use Select ... When the FOR UPDATE statement is queried, it obtains a TX (transaction) lock that is not released until the transaction is finished (a commit or rollback operation is performed). in the same transaction, whether a row is locked or 1 million, the cost of the TX lock is the same for Oracle. This may be quite different from other databases for the following reasons:on each line of data for Oracle, there is a flag bit to indicate whether the row data is locked. This greatly reduces the maintenance overhead of row-level locks, and there is no possibility of lock escalation. Once the lock flag on the data row is set, it indicates that the row data is X-locked. Oracle has no S lock on the data line, in other words the TX lock has only one-row-level exclusive lock. (Note that the TX lock is also 6 in the lmode of the V$lock, but this 6 with the TM lock 6th X-Lock is a completely different two thing)

1.3 Illustrative examples

when a DML command is issued, the session gets a 3rd number TM lock, and a 6th TX lock for a specific line. The row level is only X-lock, and the lock mode is 6, and again this 6 does not refer to the TM's 6th-Number table lock. In addition, Oracle acquires only one TX lock in a transaction, but the number of table objects gets the number of TM table-level locks. Verify the following:

The statement for the query lock is:

Select Sid,type,id1,lmode,request,block from V$lock l where SID in (select session_id from V$locked_object) and type in (' TM ', ' TX ') order by 1;

1.4 Summary of DML locks:

reading will never stop writing. But the only exception is the Select ... for update. Write will never block read. when a row is modified, Oracle provides a consistent read of the data through the rollback segment. Note: The read and write instructions above do not block each other, which means that the DML lock level does not occur, but the contention for the internal latch of the database still occurs between read and write. See the post of the database internal latch for details.

2.DDL Lock

Important: DDL is the definition of the protection table structure. When a DDL command is issued, Oracle automatically adds a DDL lock on the object being processed, preventing the object from being modified by another user. When the DDL command finishes, the DDL lock is freed. DDL locking cannot be explicitly requested, and DDL locking is added to an object only if the structure of the object is modified or referenced. not all DDL triggers DDL locks, such as the now-created index statement, which only acquires an S-mode TM lock and therefore does not block reads. The online mode creates an indexed statement that only acquires a TM lock in RS mode, so even DML is not blocked. However, the transaction should be submitted as soon as possible, for reasons See blog: http://blog.itpub.net/7417681/viewspace-1061318/ It is important to note that DDL is always committed, even if execution is unsuccessful, so if a DDL statement is executed in a transaction, all things are committed. Validation is easy, executing a delete in a window and then executing the DDL, you will find that the record has been irreversibly deleted, rollback invalid. Therefore, be sure to use an autonomous transaction implementation for the DDL in the transaction. There are 3 DDL locks: (The first is the TM lock in x mode) 2.1 Exclusive DDL lockin general, DDL statements on a table get an X-mode TM lock, which is why only queries cannot be modified when the table structure changes. 2.2 Shared DDL locksA common scenario for sharing DDL locks is when you create a stored procedure, you try to add a shared DDL lock for all the tables involved, which allows similar DDL operations to be concurrent, but blocks all sessions that want exclusive DDL locks (that is, sessions that change the table structure).
2.3 interruptible Resolution lockwhen a session resolves a statement, a parse lock is applied to each object that the statement refers to, which is to brush out the parsed cache statement in the shared pool if the reference object is deleted or modified in some way.
  3. Internal latch mechanism (slight)See the blog post for an implementation of the internal latch mechanism of the Oracle buffer pool/shared pool.

Oracle lock mechanism