oracle lock Xiangjie

2010-04-19  来源:本站原创  分类:Database  人气:275 

Oracle introduced multiple granularity locking mechanism

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.

3.1 Oracle of TX locks (row-level locking, transaction 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 TM lock (table-level locking)

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.

3.3 Oracle's TM lock (table-level locking)

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):

Table 5: Oracle Database TM lock compatibility matrix
oracle lock Xiangjie

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:

Table 6: Oracle Database TM Lock Summary
oracle lock Xiangjie

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.

Oracle monitoring of multi-granularity locking mechanism

In order to monitor the status of Oracle system lock, we need to understand the view of several systems:

5.1 v $ lock view

v $ lock view lists the current system or are held for all the locks of the situation, the main fields as follows:

Table 7: v $ lock view shows the main field
oracle lock Xiangjie

Where the value in the TYPE field, this article only interested in TM, TX are two types of DML locks;

5.2 v $ locked_object view

v $ locked_object view lists the current system in which objects are being locked, the main fields as follows:

Table 8: v $ locked_object View field description
oracle lock Xiangjie

5.3 Oracle Lock Monitor script

According to the system view, you can prepare a script to monitor the status of the database lock.

5.3.1 showlock.sql

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');

相关文章
  • oracle lock Xiangjie 2010-04-19

    Oracle introduced multiple granularity locking mechanism 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) D

  • Detailed analysis of three ORACLE lock mechanism 2010-09-19

    ORACLE locking mechanism is mainly to control the concurrent operation, to ensure data consistency and accuracy. Here we learn ORACLE lock mechanism. Oracle RDBMS blockade can be divided into the following three categories: 1, the internal closure wi

  • Oracle lock table problem simple processing techniques 2011-03-27

    Oracle lock table problem simple processing techniques http://database.51cto.com 2010-04-16 14:27 Anonymous I want to comment (0) Abstract: In the development of Oracle database, we often experience frequent operation of Oracle tables, there will be

  • oracle lock waiting for the diagnosis and rule out 2010-03-02

    In ORACLE, the To ensure data consistency, the data in the database in operation, the system will carry out the lock on the data accordingly. When the procedure changes were made to submit (commit) or rolled back (rollback), the locked resources woul

  • oracle lock Introduction 2010-07-14

    Lock (lock) mechanism for managing concurrent access to shared resources. Note that I said "shared resource" instead of "database row." Oracle will lock the row-level data on the table, which is nice, but Oracle will be used in a numbe

  • About oracle lock escalation 2010-07-27

    Lock escalation occurs (lock escalation), the system will reduce the lock granularity. For example, the database system can be a table of 100 rows into a table-level locking locks. Now you are using a "lock to lock a whole," Generally speaking,

  • ORACLE lock management 2010-10-18

    ORACLE in the locks of the following models: 0: none 1: null null 2: Row-S line sharing (RS): shared table lock, sub share 3: Row-X line of exclusive (RX): for the changes, sub exclusive 4: Share a shared lock (S): stop other DML operations, share 5:

  • [Transfer] ORACLE lock management 2010-11-08

    ORACLE in the locks of the following models: 0: none 1: null null 2: Row-S line sharing (RS): shared table lock, sub share 3: Row-X line of exclusive (RX): for the changes, sub exclusive 4: Share a shared lock (S): stop other DML operations, share 5:

  • Oracle lock mode the higher the level of six more 2010-11-09

    ORACLE in the locks of the following models: 0: none 1: null null 2: Row-S line sharing (RS): shared table lock, sub share 3: Row-X line of exclusive (RX): for the changes, sub exclusive 4: Share a shared lock (S): stop other DML operations, share 5:

  • ORA-00031: session marked for kill on oracle lock table processing 2011-07-29

    ORACLE some operations in the process is killed, the status is set to "killed", but the lock does not release the resources for a long time, no way, when it had to restart the database. But this is not the best solution, now offers a solution to

  • View and delete oracle lock table 2010-08-20

    view table oracle check whether the table is locked locked SELECT a.sid, b.owner, object_name, object_type FROM v $ lock a, all_objects b WHERE TYPE = 'TM' and a.id1 = b.object_id; Found in the table are locked So that it can kill SELECT sid, serial

  • Oracle lock mode, the higher the level six more influence 2010-11-09

    ORACLE, the lock has the following modes: 0: none 1: null null 2: Row-S line sharing (RS): a shared table lock, sub share 3: Row-X line of exclusive (RX): for line changes, sub exclusive 4: Share a shared lock (S): stop other DML operation, share 5:

  • oracle lock data file can not identify the solution 2010-11-10

    Can not identify / lock data file Error is: SQL> startup ORACLE Routine has started . Total System Global Area 293601280 bytes Fixed Size 1290208 bytes Variable Size 209715232 bytes Database Buffers 75497472 bytes Redo Buffers 7098368 bytes Completed

  • oracle lock mechanism 2010-12-22

    Lock is a mechanism that is used to manage concurrent access to a shared resource. Solution to stop? Concurrent access to resources only when the lock will be used, but a single user mode, the database is not locked. Therefore, the purpose is to lock

  • Oracle lock easily comprehensible principles of articles --- 2011-02-09

    In modern multi-user multi-tasking system, bound to multiple users simultaneously access a shared object, this object may be tables, rows, or memory structure, in order to solve the concurrent access to multiple users brought data security , integrit

  • oracle lock table (kill the process at the OS level) 2011-03-16

    Close database SQL> shutdown immediate Close the database to ensure that does not kill off all the User Session When the instance can not be closed off when not using shutdown immediate, you can use shutdown abort shut down the instance, but closed a

  • oracle lock wait / deadlock Analysis 2011-04-18

    1. Simulation deadlock 1.1. Main table -- Create table create table WDZ1 ( WDZ1ID NUMBER not null, MEMO VARCHAR2(20) ) ; alter table WDZ1 add constraint XXXXXX primary key (WDZ1ID); 1.2. From the table (no foreign key index) -- Create table create ta

  • oracle lock table problem 2011-04-28

    1, Select * From v $ locked_object; lock table object under sessionid, to Tools - sessions to find the appropriate sessions to see the lock table sql statement Or direct execute: Select a.inst_id, c.sid, c.serial #, d.name, b.object_name, c.username,

  • Oracle lock on the analysis - ass mother travel DBA 2011-08-19

    1. What is the ORACLE database lock is a shared resource used by multiple users. When multiple users simultaneous access to data in the database will have multiple transactions simultaneously access the same data. If not controlled for concurrent ope

  • View oracle lock table 2010-04-15

    View the process of locking the table SQL statement 1: select sess.sid, sess.serial #, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v $ locked_object lo, dba_objects ao, v $ session sess where ao.object_id = lo.object_id a