Introduction to Oracle Locks (GO)

This article transferred from: http://blog.csdn.net/gyb2013/article/details/6929697

One, what is the lock:

The lock mechanism of Oracle is a lightweight locking mechanism that does not lock the data by building a lock list, but instead directly stores the lock as a block of data in the block header. This is done through ITL, where a transaction modifies the data in the block and must obtain an ITL in that block. For an introduction to ITL (transaction slots), refer to http://blog.csdn.net/gybyylx/article/details/6893639.

Description: In an Oracle database, there are no locks that really belong to an object or data. The information for an Oracle lock is a physical property of the data block, not logically belonging to a table or a row.

Second, why the need to lock:

Why does Oracle have to make a lock mechanism? Very simple, that's because it's needed. Think about the door of our own house, why should we lock it up? We must all know that we are afraid of the loss of the home or others to take away, if there is no other family in your family there is no need to lock the door. Oracle lock is also a reason, if it is a single-user system, there is no need for this lock, that is, because of multi-user concurrency, we have to ensure the security of resources (that is, Oracle data integrity and consistency) to derive this lock out. Oracle uses its locking mechanism to implement data concurrency and data consistency between transactions.

Three, the mode of the lock:

Oracle has two modes of lock: Exclusive (exclusive lock, i.e. x Lock) and shared lock (share lock, S lock).

When a data object is added to an exclusive lock, other transactions cannot read it (can read undo) and modify it. Data objects with shared locks can be read by other transactions, but cannot be modified. The database uses these two basic types of locks to control the concurrency of a database's transactions.

iv. Types of locks:

Depending on the object being protected, the Oracle database lock can be divided into the following major categories:

DML Lock (data locks, lock): Used to protect the integrity of the data.

DDL Lock (dictionary locks, Dictionary Lock): A structure definition that is used to protect the structure of database objects, such as tables, indexes, and so on.

Internal lock and latch (internal locks and latches): protects the internal structure of the database, for example, data files. The management of internal locks and latches is fully automated by Oracle.

1.DML Lock:

DML locks are designed to guarantee data integrity in the case of concurrency, where 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. DML statements can automatically obtain the required table-level lock (TM) and row-level (transaction) lock (TX).
A. Table level lock (TM)

TM locks are used to ensure that when the contents of a table are modified, the structure of the table does not change, for example, to prevent related tables from being removed during DML statement execution. When a user performs a DDL or DML operation on a table, it gets a table-level lock on the table.

TM Lock type for Oracle

Lock mode lock description Interpreting SQL operations

0 None

1 null Empty Select

2 SS (row-s) row-level shared locks, other objects can only query these data rows for the Select for update, the lock for update, the lock Row share

3 SX (row-x) row level exclusive lock, do not allow DML operation before commit Insert, Update, Delete, lock Row Share

      4                S (Share)                         Shared Locks                                                                  Create Index, Lock Share

5 SSX (s/row-x) shared row-level exclusive locks lock share row exclusive

6 X (Exclusive) Row It locks Alter table, drop able, DROP index, Truncate table, lock Exclusive

B. Transaction (Row level) lock (TX)

When the transaction initiates the first modification, it gets a TX lock (transaction Lock), and the lock is held until the transaction commits (commits) or rolls back (ROLLBACK). The TX lock is used as a queueing mechanism so that other sessions can wait for the transaction to execute. Each row that is modified in a transaction or selected through Select for UPDATE "points to" a related TX lock for that transaction. A transaction to modify the data in the block, you must obtain an ITL in the block and, through the transaction table in the ITL and undo segment headers, you can know whether the transaction is in the active phase. When a transaction modifies a block (in fact, it modifies a row), it checks the flag bit in the row header of the line, and if the flag bit is 0 (the row is not locked by the active transaction), the flag bit is modified to be the ordinal of the ITL that the transaction obtains in that block, so that the current transaction is locked to the record. You can then modify the row data, which is how the Oracle row lock implementation works.

2.DDL Lock:

DDL locks (DDL Lock) are automatically added to an object in a DDL operation to protect those objects from being modified by other sessions. For example, if I perform a DDL operation on ALTER TABLE T, an exclusive DDL lock is added to the table T to prevent other sessions from getting DDL and TM locks on the table. DDL locks are held until the DDL statement is executed, releasing the DDL lock as soon as the operation executes. In practice, DDL statements are typically wrapped in an implicit commit (or commit/rollback pair) to perform these tasks. For this reason, DDL is guaranteed to be committed in Oracle. Each create, ALTER, and so on is actually executed as follows (shown here in pseudo-code):
Begin
Commit;
Ddl-statement
Commit;
Exception
When others and then rollback;
End;

Therefore, the DDL is always committed (even if the commit is unsuccessful). It is important to know that DDL is submitted at the outset. It is first committed, so if it must be rolled back, it will not roll back your transaction. If you execute the DDL, it will make all of the unfinished work you do to be permanent, even if the DDL is unsuccessful. If you need to execute the DDL, but do not want it to commit your existing transaction, you can use an autonomous thing (autonomous transaction). DDL locks are divided into the following categories:

Exclusive DDL Lock (Exclusive DDL Lock): This prevents other sessions from getting their own DDL Lock or TM (DML) lock. This means that you can query a table during DDL operations, but you cannot modify the table in any way.
Shared DDL locks (Share DDL Lock): These locks protect the structure of referenced objects from being modified by other sessions, but allow data to be modified.
Interruptible Parse Lock (breakable parse locks): These locks allow an object (such as a query plan cached in a shared pool) to register its dependencies with another object. If you execute ddl,oracle on a dependent object, you will see a list of objects that have already registered dependencies on the object and invalidate those objects. Therefore, these locks are "interruptible" and they do not prevent DDL from appearing.

3. Latch (LATCH): is a lightweight serialization device that coordinates multi-user access to shared data structures, objects, and files. About latch Follow-up separate introduction.

4. Manual Lock:

1. Manually lock the data through an SQL statement (for example, select for Update).
2. Create our own locks through the Dbms_lock package.

Pessimistic blockade and optimistic blockade
A, pessimistic blockade
Pessimistic lock as the name implies, is the conflict of data to take a pessimistic attitude, that is to say that the data will certainly conflict, so when the data began to read when the data locked:
Select: For update (NOWAIT)
Select * from TAB1 for update
After the user issues this command, Oracle will establish a row-level blockade of the data in the returned set to prevent other users from modifying it.
If a DML or DDL operation is performed by another user at this time on the data returned by the result set above, an error message is returned or a blockage occurs.
1: The update or delete operation on the returned result set is blocked.
2: DDL operation on this table will be reported: Ora-00054:resource busy and acquire with nowait specified.
Cause analysis
At this point, Oracle has added row-level locks to the returned result set, and all other modifications or deletions to the data must wait for the lock to be released, and the external phenomenon is that other operations will be blocked, this operation commits or rollback.
The same transaction for this query will add a table-level lock to the table, do not allow any DDL operations on the table, or the ora-00054 error will be reported: Resource busy and acquire with nowait specified.
B, optimistic blockade
Optimistic locking is that the data generally does not cause conflict, so when the data is submitted to update the time, the data will be formally conflicting or not detected, if a conflict is found, let the user return the wrong information, let the user decide how to do. There is a potential danger that the chosen result set is not locked, and there is a possibility that another user might be able to change it (missing update issues). Optimistic lock and pessimistic lock each have advantages and disadvantages, depending on the actual situation to be determined.

For an introduction to Oracle pessimistic and optimistic locking, refer to: http://blog.csdn.net/gybyylx/article/details/4474270

This article only gives an overview of the Oracle lock, and further explores the Oracle lock mechanism.

Introduction to Oracle Locks (GO)