Under the protection of different objects, Oracle database lock can be divided into the following categories:
(1) DML lock (data locks, data locks): used to protect the integrity of data;
(2) DDL lock (dictionary locks, dictionary locks): used to protect the structure of database objects (such as tables, views, indexes of the structure definition);
(3) Internal locks and latches (internal locks and latches): Protection of the internal database structure;
(4) Distributed locks (Distributed Lock): for the OPS (Parallel Server);
(5) PCM locks (Parallel Cache Management lock): for the OPS (Parallel Server).
Oracle lock in the most significant is the DML (also called data locks, data locks) lock. From the block size (block size of the object) point of view, Oracle DML lock consists of two levels, namely, row-level locking and table-level lock.
Oracle did not understand many of the technical staff may think that every representative of a TX lock rows of data are blocked, it is not. TX is the original meaning of Transaction (Services), when a transaction for the first time to perform data changes (Insert, Update, Delete), or use SELECT ... FOR UPDATE statement in a query, it is to get a TX (transaction) locks until the end of the transaction ( implementation of the COMMIT or ROLLBACK operation), the lock was released. Therefore, a TX lock, the transaction can be locked by the corresponding number of rows of data (more than in our time is to start with a transaction, then SELECT ... FOR UPDATE NOWAIT).
Each line in the Oracle data, there is a flag to represent the row data is locked. Unlike Oracle DB2 as a linked list to maintain the data in each row is locked, thus greatly reducing the row-level lock maintenance overhead, but also largely avoided the use of row-level locking DB2 similar often happens when inadequate for the number lock lock escalation. Rows of data, once the lock flag is set, it indicates that the data is added row X lock, Oracle did not in the data line S lock.
3.2.1 leads to intention to lock
Table is formed by the line, when we to a table lock on the one hand need to check whether the application of the lock the original table-level lock compatible; the other hand, have to check whether the lock table compatible with each line on the lock. For example, a transaction to add a table S lock, if the table row has been added another transaction X lock, then the application of the lock should also be blocked. If the data in the table a lot of the overhead line by line, checking the lock symbol will be large, system performance will be affected. To solve this problem, the table-level lock to introduce a new type of lock that the situation in their respective firms, which leads to the "intention locks" concept.
The meaning of intent lock on a node is added if the intent lock, it indicates that the lower node node is locked; on either node locked, it must first increase its intention to lock the upper node. Such as: a line on the table in any lock, it must first increase its intention to host a table lock, then lock the row. As a result, transaction lock on the table when the table is no longer need to check the records of every row lock flag, the system efficiency can be greatly enhanced.
3.2.2 Types of intent lock
By the two basic types of locks (S Lock, X lock) can naturally derive two intent locks:
Intent shared locks (Intent Share Lock, IS for short lock): If you want to lock a database object increases S, the first of its parent node to add IS lock descendant nodes that it intended (intention) plus S lock;
Intent exclusive lock (Intent Exclusive Lock, referred to as IX lock): If you want to add X to a database object lock, the first of its parent node to add IX lock descendant nodes that it intended (intention) plus X lock.
In addition, the basic lock types (S, X) with the intent lock type (IS, IX) can be combined between the new lock type, in theory, can combine the four kinds, namely: S + IS, S + IX, X + IS, X + IX, but little analysis is not difficult to see that, actually only S + IX new meaning to the other three combinations did not improve the strength of the lock (that is: S + IS = S, X + IS = X, X + IX = X, where "=" refers to the intensity of the same lock). The intensity of the so-called lock refers to the exclusion of other locks.
So that we can also introduce a new type of lock:
Shared intent exclusive lock (Shared Intent Exclusive Lock, called SIX lock): If a database object plus SIX lock that locks it plus S plus IX lock, that is SIX = S + IX. For example: transaction on a table plus SIX lock, then the transaction to read the entire table (so to lock the table plus S), and it will update the individual line (so to increase IX lock on the table).
This database objects added by the lock may have 5 types: the S, X, IS, IX, SIX.
With the intent of multi-granularity locking block any transaction T method to lock a database object, you must first increase its intention of locking the upper node. For blockade should be a top-down order of; release blockade is a bottom-up order should be carried out; with intent lock granularity blockade method to improve the system concurrency, reducing locking and unlocking overhead.
Oracle's DML locks (data locks) is the use of the above mentioned methods of multi-size block, the row-level locking although only one (or X locks), but the TM lock (table-level locking) type of a total of 5 species are known as shared locks (S locks), an exclusive lock (X locks), row-level Share lock (RS lock), row-level exclusive lock (RX lock), shared row-level exclusive lock (SRX locks), and above that the S, X, IS, IX, SIX corresponding. Note that because Oracle only provides line-level X locks, lock it with the RS (via SELECT ... FOR UPDATE statement is received) is the corresponding row lock X lock (but in fact the bank data has not been modified), which with the theoretical IS lock is different. Lock compatibility means that when an application in the table (line) with a lock on, the other applications are able to in the table (line) with the corresponding lock on, if added, suggesting that the two lock are compatible, otherwise it is not compatible with two locks, can not access the same data object concurrency.
The following table shows the compatible Oracle Database TM lock matrix (Y = Yes, said the request compatible; N = No, that is not compatible with the request; - that there is no locking request):
On the one hand, when the Oracle implementation of the SELECT ... FOR UPDATE, INSERT, UPDATE, DELETE and so on DML statements, the system automatically in the table to operate on application-level RS lock (SELECT ... FOR UPDATE) or RX lock (INSERT, UPDATE, DELETE) When the table-level lock access, the system then automatically apply for TX locks, and the actual locking of the data row lock flag bit position (point Gai TX lock); on the other hand, procedures or operator can also LOCK TABLE statement to specify get some type of TM lock. The following table summarizes the author of the SQL statements generated Oracle TM lock situation:
We can see that the usual DML operations (SELECT ... FOR UPDATE, INSERT, UPDATE, DELETE), obtained in the table-level locks only intent (RS or RX), the blockade of its real size or line level; addition, Oracle A striking feature of the database is the default, simply read data (SELECT) is not locked, Oracle through the rollback segment (Rollback segment) to ensure that users do not read "dirty" data. These have increased the degree of concurrency system.
As the intention of locking the lock and data-line flag of the introduction of Oracle reduces the cost of maintaining row-level locking, the application of these technologies to enable Oracle to efficiently deal with a high degree of concurrent transaction requests.
In order to monitor the status of Oracle system lock, we need to understand the view of several systems:
v $ lock view lists the current system or are held for all the locks of the situation, the main fields as follows:
Where the value in the TYPE field, this article only interested in TM, TX are two types of DML locks;
v $ locked_object view lists the current system in which objects are being locked, the main fields as follows:
According to the system view, you can prepare a script to monitor the status of the database lock.
The first script showlock.sql, the script by connecting v $ locked_object and two all_objects view shows which object is what the session lock:
/* showlock.sql */ column o_name format a10 column lock_type format a20 column object_name format a15 select rpad(oracle_username,10) o_name,session_id sid, decode(locked_mode,0,'None',1,'Null',2,'Row share', 3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type, object_name ,xidusn,xidslot,xidsqn from v$locked_object,all_objects where v$locked_object.object_id=all_objects.object_id; 5.3.2 showalllock.sql
The second script showalllock.sql, the script displays the current all the major TM, TX lock information;
/* showalllock.sql */ select sid,type,id1,id2, decode(lmode,0,'None',1,'Null',2,'Row share', 3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,request,ctime,block from v$lock where TYPE IN('TX','TM');