oracle deadlock 之（一）--锁机制介绍

    关于oracle的锁的介绍，11g的官方文档对oracle的锁的介绍
     http://docs.oracle.com/cd/E11882_01/server.112/e40540/consist.htm#CNCPT020
   
    好了，不多废话，下面我们进入正题
    
提纲如下

    锁的用途
    oracle 如何锁数据
        事务和数据并发（transactions and data concurrency）
            锁的模式（modes of locking）
            锁的持续时间（ lock duration）
    oracle 锁的种类
         DML locks
             row locks   (TX)
             table locks (TM)
         DDL locks
               exclusive DDL locks
               share DDL   locks
               breakable parse locks
        systemlocks
                latches
               mutexes
               internal locks

锁的用途
     通常来说，在多用户的数据库中使用多种形式的数据锁（lock）来解决与数据并发、数据一致性和完整性的问题。锁(lock)是在多个事务访问和操作相同的资源时，防止数据被破坏的一种机制。
    资源包括下面两种一般类型的对象： 

•      User objects, such as tables and rows (structures and data)
•      System objects not visible to users, such as shared data structures in the memory and data dictionary rows

oracle如何锁数据（how oracle locks data）
    

Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource—either user objects such as tables and rows or system objects not visible to users, such as shared data structures in memory and data dictionary rows.

锁是一种为了防止多事务访问相同资源时对资源保护的一种机制。资源包括用户的对象，例如表和行数据，也包括用户不可见的系统对象，例如在内存和数据字典当中的行的共享数据结构。

In all cases, Oracle automatically obtains necessary locks when executing SQL statements, so users need not be concerned with such details. Oracle automatically uses the lowest applicable level of restrictiveness to provide the highest degree of data concurrency yet also provide fail-safe data integrity. Oracle also allows the user to lock data manually.

在执行sql语句时，oracle自动获取必要的锁，为了提高数据的并发性和保护数据的完整性，oracle自动获取最低应用级别的锁。oracle也允许用户手工锁定数据。

事务和数据的并发（transactions and data concurrency)
 

Oracle provides data concurrency and integrity between transactions using its locking mechanisms. Because the locking mechanisms of Oracle are tied closely to transaction control, application designers need only define transactions properly, and Oracle automatically manages locking.

Keep in mind that Oracle locking is fully automatic and requires no user action. Implicit locking occurs for all SQL statements so that database users never need to lock any resource explicitly. Oracle's default locking mechanisms lock data at the lowest level of restrictiveness to guarantee data integrity while allowing the highest degree of data concurrency.

需要注意的是，oracle的锁是自动获取，不需要用户去操作。在执行所有sql语句时，oracle都会隐式的锁定所需要的资源，不需要数据库用户显式的锁定资源。

锁的模式（modes of locking)

Oracle uses two modes of locking in a multiuser database:

Exclusive lock mode prevents the associates resource from being shared. This lock mode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.
排它锁，防止资源被共享。排他锁用于修改数据。第一个事务排他的方式锁定一个资源，即只有这个事务可以修改这个资源，直到这个排它锁释放。
Share lock mode allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource.
共享锁，允许资源被共享。多个用户可以共享读取数据，持有共享锁防止并发写。 多个事务可以获取相同资源的共享锁。

锁的持续时间（lock duration）

All locks acquired by statements within a transaction are held for the duration of the transaction, preventing destructive interference including dirty reads, lost updates, and destructive DDL operations from concurrent transactions. The changes made by the SQL statements of one transaction become visible only to other transactions that start after the first transaction is committed.

Oracle releases all locks acquired by the statements within a transaction when you either commit or undo the transaction. Oracle also releases locks acquired after a savepoint when rolling back to the savepoint. However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions will continue to wait until after the original transaction commits or rolls back completely.

上面的意思就是，oracle事务会自动获取相应的锁，直到该事务结束，才会释放所有的锁和资源。事务结束标志 commit或者rollback；

oracle锁的种类（type of locks）

Oracle Database automatically locks a resource on behalf of a transaction to prevent other transactions from doing something that requires exclusive access to the same resource. The database automatically acquires different types of locks at different levels of restrictiveness depending on the resource and the operation being performed.

Note:
The database never locks rows when performing simple reads.

Oracle Database locks are divided into the following categories.

Lock	Description
DML Locks	Protect data. For example, table locks lock entire tables, while row locks lock selected rows. See "DML Locks".
DDL Locks	Protect the structure of schema objects—for example, the dictionary definitions of tables and views. See "DDL Locks".
System Locks	Protect internal database structures such as data files. Latches, mutexes, and internal locks are entirely automatic. See "System Locks".

The following sections discuss DML locks, DDL locks, and System Locks.

        

DML Locks

A DML lock, also called a data lock, guarantees the integrity of data accessed concurrently by multiple users. For example, a DML lock prevents two customers from buying the last copy of a book available from an online bookseller. DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations.

DML锁，又称之为 数据锁，用于多用户并发访问数据时保证数据的完整性。

DML statements automatically acquire the following types of locks:

Row Locks (TX)
Table Locks (TM)

In the following sections, the acronym in parentheses after each type of lock or lock mode is the abbreviation used in the Locks Monitor of Oracle Enterprise Manager (Enterprise Manager). Enterprise Manager might display TM for any table lock, rather than indicate the mode of table lock (such as RS or SRX).

            

Row Locks (TX)

A row lock, also called a TX lock, is a lock on a single row of table. A transaction acquires a row lock for each row modified by an INSERT, UPDATE,DELETE, MERGE, or SELECT ... FOR UPDATE statement. The row lock exists until the transaction commits or rolls back.

行锁，又称之为 TX锁，用于锁定表中的单个行。一个事务中INSERT,UPDATE,DELETE,MERGE,和SELECT .. FOR UPDATE都会获取修改的行的行锁。事务commit或者rollback，行锁自动释放。

Row locks primarily serve as a queuing mechanism to prevent two transactions from modifying the same row. The database always locks a modified row in exclusive mode so that other transactions cannot modify the row until the transaction holding the lock commits or rolls back. Row locking provides the finest grain locking possible and so provides the best possible concurrency and throughput.

行锁主要以队列机制来防止两个事务修改相同的行。数据库通常会以排他模式锁定被修改的行，以阻止其他事务去修改，直到该事务commit或rollback后才会释放持有的锁。

Note:
If a transaction terminates because of database instance failure, then block-level recovery makes a row available before the entire transaction is recovered.

如果一个事务由于数据库实例失败而导致中断，那么整个事务进行恢复之前会进行数据块级别的恢复，此时的行是available的

If a transaction obtains a lock for a row, then the transaction also acquires a lock for the table containing the row. The table lock prevents conflicting DDL operations that would override data changes in a current transaction. Figure 9-2 illustrates an update of the third row in a table. Oracle Database automatically places an exclusive lock on the updated row and a subexclusive lock on the table.

一个事务获得一个行锁，那它同时也会获得行所在的表的table lock。table lock 防止冲突的DDL操作覆盖掉当前事务修改的数据。如下图

               

行锁的存储（Storage of Row Locks）

Unlike some databases, which use a lock manager to maintain a list of locks in memory, Oracle Database stores lock information in the data block that contains the locked row.

oracle将锁相关的信息存储在locked row 所在的data block中

The database uses a queuing mechanism for acquisition of row locks. If a transaction requires a lock for an unlocked row, then the transaction places a lock in the data block. Each row modified by this transaction points to a copy of the transaction ID stored in the block header (see "Overview of Data Blocks").

数据库使用队列机制来获取row locks。如果一个事务需要lock an unlocked row，那么该事务会在行所在的data lock中放置一个锁。由该事务修改的每行数据指向一个存储在block header中的事务id的副本。

When a transaction ends, the transaction ID remains in the block header. If a different transaction wants to modify a row, then it uses the transaction ID to determine whether the lock is active. If the lock is active, then the session asks to be notified when the lock is released. Otherwise, the transaction acquires the lock.

当一个事务结束后，该事务的ID仍然保留在block header中。如果一个不同的事务想去修改一个行，那么他会使用这个事务ID 去判断这个锁是否是active。如果这个锁是active状态，那么该会话去要求当锁被释放时通知会话。否则，该事务将获得这个锁。

Table Locks (TM)

A table lock, also called a TM lock, is acquired by a transaction when a table is modified by an INSERT, UPDATE, DELETE, MERGE, SELECT with theFOR UPDATE clause, or LOCK TABLE statement. DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction.

A table block ,又称为TM 锁，当一个事务对表进行insert,update,delete,merge,select.. for update,lock table操作时，该事务会获取table lock，防止DDL操作与该事务冲突。


A table lock can be held in any of the following modes:

• Row Share (RS) 

  This lock, also called a subshare table lock (SS), indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. A row share lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.
  
  行共享锁（Row Share RS）说明事务要更新数据，是Table lock中限制最少的锁，为表提供了最大程度的并发性

• Row Exclusive Table Lock (RX)

  This lock, also called a subexclusive table lock (SX), generally indicates that the transaction holding the lock has updated table rows or issuedSELECT ... FOR UPDATE. An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, SX locks allow multiple transactions to obtain simultaneous SX and subshare table locks for the same table.
  
  行独占表锁（Row Exclusive Table Lock: RX) 说明事务已经更新了表中的行或者执行了select .. for update。

• Share Table Lock (S)

  A share table lock held by a transaction allows other transactions to query the table (without using SELECT ... FOR UPDATE), but updates are allowed only if a single transaction holds the share table lock. Because multiple transactions may hold a share table lock concurrently, holding this lock is not sufficient to ensure that a transaction can modify the table.

• Share Row Exclusive Table Lock (SRX)

  This lock, also called a share-subexclusive table lock (SSX), is more restrictive than a share table lock. Only one transaction at a time can acquire an SSX lock on a given table. An SSX lock held by a transaction allows other transactions to query the table (except for SELECT ... FOR UPDATE) but not to update the table.

• Exclusive Table Lock (X)

  This lock is the most restrictive, prohibiting other transactions from performing any type of DML statement or placing any type of lock on the table.

Summary of Table Locks

SQL Statement	Mode of Table Lock	Lock Modes Permitted?
		RS	RX	S	SRX	X
SELECT...FROM table...	none	Y	Y	Y	Y	Y
INSERT INTO table ...	RX	Y	Y	N	N	N
UPDATE table ...	RX	Y*	Y*	N	N	N
DELETE FROM table ...	RX	Y*	Y*	N	N	N
SELECT ... FROM tableFOR UPDATE OF ...	RS	Y*	Y*	Y*	Y*	N
LOCK TABLE tableIN ROW SHARE MODE	RS	Y	Y	Y	Y	N
LOCK TABLE tableIN ROW EXCLUSIVE MODE	RX	Y	Y	N	N	N
LOCK TABLE tableIN SHARE MODE	S	Y	N	Y	N	N
LOCK TABLE tableIN SHARE ROW EXCLUSIVE MODE	SRX	Y	N	N	N	N
LOCK TABLE tableIN EXCLUSIVE MODE	X	N	N	N	N	N

RS: row share

RX: row exclusive

S: share

SRX: share row exclusive

X: exclusive

*Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.

DDL Locks

A data dictionary (DDL) lock protects the definition of a schema object while an ongoing DDL operation acts on or refers to the object. Only individual schema objects that are modified or referenced are locked during DDL operations. The database never locks the whole data dictionary.

数据字典锁（DDL lock) 用于正在执行DDL操作引用对象时，保护一个schema object的定义。只有被修改或者被引用单个的schema objects在执行DDL操作时被锁定。数据库从来不会锁定整个数据字典

Oracle Database acquires a DDL lock automatically on behalf of any DDL transaction requiring it. Users cannot explicitly request DDL locks. For example, if a user creates a stored procedure, then Oracle Database automatically acquires DDL locks for all schema objects referenced in the procedure definition. The DDL locks prevent these objects from being altered or dropped before procedure compilation is complete.

oracle数据库任何DDL 事务都会自动获取一个DDL锁。用户无法显式的去请求DDL锁。举例来说，一个用户创建一个存储过程，那么oracle数据库会自动获取存储定义中所引用的所有的schema objects的DDL锁。DDL锁防止在存储过程编译完成之前，这些对象被修改或者删除。

Exclusive DDL Locks 独占DDL锁

An exclusive DDL lock prevents other sessions from obtaining a DDL or DML lock. Most DDL operations, except for those described in "Share DDL Locks", require exclusive DDL locks for a resource to prevent destructive interference with other DDL operations that might modify or reference the same schema object. For example, DROP TABLE is not allowed to drop a table while ALTER TABLE is adding a column to it, and vice versa.

独占DDL锁，禁止其他会话获取一个DDL锁或者DML锁。大多数DDL操作，除了下面要讲到的共享DDL锁之外，在资源上获取的是独占DDL锁，以防止其他DDL操作修改或者引用相同的schema objects对其产生破坏。举例来说，某张表上正在执行ALTER TABLE add column操作，此时对该表执行DROP TABLE 操作，drop table 操作将会不允许。

Exclusive DDL locks last for the duration of DDL statement execution and automatic commit. During the acquisition of an exclusive DDL lock, if another DDL lock is held on the schema object by another operation, then the acquisition waits until the older DDL lock is released and then proceeds.

独占DDL锁持续的时间在DDL语句开始执行到自动提交。如果一个操作需要获取某个schema object的DDL锁，而此时的schema object的DDL锁被另外的操作持有，那么该操作会等待this old DDL lock，直到被释放。

Share DDL Locks

A share DDL lock for a resource prevents destructive interference with conflicting DDL operations, but allows data concurrency for similar DDL operations.

For example, when a CREATE PROCEDURE statement is run, the containing transaction acquires share DDL locks for all referenced tables. Other transactions can concurrently create procedures that reference the same tables and acquire concurrent share DDL locks on the same tables, but no transaction can acquire an exclusive DDL lock on any referenced table.

A share DDL lock lasts for the duration of DDL statement execution and automatic commit. Thus, a transaction holding a share DDL lock is guaranteed that the definition of the referenced schema object remains constant during the transaction.

Breakable Parse Locks（可中断解析锁）

A parse lock is held by a SQL statement or PL/SQL program unit for each schema object that it references. Parse locks are acquired so that the associated shared SQL area can be invalidated if a referenced object is altered or dropped. A parse lock is called a breakable parse lock because it does not disallow any DDL operation and can be broken to allow conflicting DDL operations.

A parse lock is acquired in the shared pool during the parse phase of SQL statement execution. The lock is held as long as the shared SQL area for that statement remains in the shared pool.

System Locks

Oracle Database uses various types of system locks to protect internal database and memory structures. These mechanisms are inaccessible to users because users have no control over their occurrence or duration.

oracle 数据库使用一系列类型的系统锁来保护内部数据库和内存结构。用户是无法访问和控制这些系统锁的机制。

Latches

Latches are simple, low-level serialization mechanisms that coordinate multiuser access to shared data structures, objects, and files. Latches protect shared memory resources from corruption when accessed by multiple processes. Specifically, latches protect data structures from the following situations:

latch是一种简单的低级别的串行机制的锁，用来协助处理多用户访问共享数据结构，对象和文件。latch用来保护被多个进程访问的共享内存资源。
特别是下面的几种情况，latch保护数据结构：

• Concurrent modification by multiple sessions     多个会话并发修改
• Being read by one session while being modified by another session  
• Deallocation (aging out) of memory while being accessed    

Typically, a single latch protects multiple objects in the SGA. For example, background processes such as DBWn and LGWR allocate memory from theshared pool to create data structures. To allocate this memory, these processes use a shared pool latch that serializes access to prevent two processes from trying to inspect or modify the shared pool simultaneously. After the memory is allocated, other processes may need to access shared pool areas such as the library cache, which is required for parsing. In this case, processes latch only the library cache, not the entire shared pool.

一般来说，一个单独的latch保护SGA中的多个对象。举例来说，像DBWR和LGWR这样的后台进程在share pool中分配内来存创建数据结构。为了分配内存，这些进程使用share pool 中的latch来实现串行访问，防止两个进程并发的去检查或者修改share pool。后台进程在内存分配后，其他进程可能需要去访问share pool areas，例如解析所需要的library cache。

Unlike enqueue latches such as row locks, latches do not permit sessions to queue. When a latch becomes available, the first session to request the latch obtains exclusive access to it. Latch spinning occurs when a process repeatedly requests a latch in a loop, whereas latch sleeping occurs when a process releases the CPU before renewing the latch request.

与row lock这样的队列锁不同，latch不允许session 排队。当一个latch变为可用状态，第一个请求该latch的session将会以独占方式获得并访问latch。latch自旋发生在 一个进程在一个循环中重复请求一个latch阶段。latch睡眠 发生在 一个进程在重新请求latch之前释放了CPU的阶段。
也就是说，某个进程在一个周期内重复请求某个latch，此时就会发生latch 自旋。如果进程在周期内没有获得latch，该进程会释放CPU，进入latch 睡眠，睡眠周期结束后，进程会重新去请求latch。
在latch自旋阶段，某个进程仍然没有获得该latch的话，该进程会释放CPU，进入latch 睡眠阶段。latch睡眠周期结束后，进程会继续请求latch

              latch 自旋                                                               latch睡眠                                                     latch 自旋
进程 -----------------------------------------------&gt  latch  -------------------------------------------------&gt  进程 ----------------------------------------&gt latch
             周期内 进程重复请求latch                                   进程释放CPU                                                周期内进程重复请求latch

Typically, an Oracle process acquires a latch for an extremely short time while manipulating or looking at a data structure. For example, while processing a salary update of a single employee, the database may obtain and release thousands of latches. The implementation of latches is operating system-dependent, especially in respect to whether and how long a process waits for a latch.

一般情况下，当oracle进程操作或者查看数据结构时，会获取一个时间非常短的latch。举例来说，在处理 更新一个单个的员工的薪资时，数据库可能会获取和释放成千上万个latch。latch的实现依赖于操作系统，特别是一个进程是否等待一个latch，等待多长时间。

An increase in latching means a decrease in concurrency. For example, excessive hard parse operations create contention for the library cache latch. The V$LATCH view contains detailed latch usage statistics for each latch, including the number of times each latch was requested and waited for.

latch的增加 意味着并发性的降低。举例来说，过量的硬解析会造成library cache latch的争夺。动态性能视图V$LATCH中包含每个latch的详细信息。

Mutexes（互斥锁）

A mutual exclusion object (mutex) is a low-level mechanism that prevents an object in memory from aging out or from being corrupted when accessed by concurrent processes. A mutex is similar to a latch, but whereas a latch typically protects a group of objects, a mutex protects a single object.

互斥锁是 防止被并发进程访问的单个的内存中的对象 被age out或者腐化的低级别的机制。互斥锁类似于latch，但是latch是保护一组对象，互斥锁是保护单个对象。

Mutexes provide several benefits:
互斥锁有如下优点

• A mutex can reduce the possibility of contention.    减少争用

Because a latch protects multiple objects, it can become a bottleneck when processes attempt to access any of these objects concurrently. By serializing access to an individual object rather than a group, a mutex increases availability.

latch保护多个对象，当进程并发访问这些对象时，latch很容易成为瓶颈。串行的访问单个对象而不是一组对象，互斥锁增加了可用性。

• A mutex consumes less memory than a latch.          单个互斥锁内存消耗比latch少。
• When in shared mode, a mutex permits concurrent reference by multiple sessions.          共享模式下，一个互斥锁多个session并发引用。

Internal Locks （内部锁）

Internal locks are higher-level, more complex mechanisms than latches and mutexes and serve various purposes. The database uses the following types of internal locks:

内部锁是一种比latch和互斥锁更复杂和更高级别的锁。数据库使用下面几种类型的内部锁

• Dictionary cache locks （数据字典缓存锁）

  These locks are of very short duration and are held on entries in dictionary caches while the entries are being modified or used. They guarantee that statements being parsed do not see inconsistent object definitions. Dictionary cache locks can be shared or exclusive. Shared locks are released when the parse is complete, whereas exclusive locks are released when the DDL operation is complete.
  
  这些锁持续非常短的时间，并且该锁被正在修改或者使用的数据字典缓存中的条目持有。该锁用于防止正在被解析的语句看到不一致的对象的定义。数据字典缓存锁可以共享或者独占。当解析完成时，释放共享锁。DDL操作执行完成时，释放独占锁。

• File and log management locks（文件和日志 管理锁）

  These locks protect various files. For example, an internal lock protects the control file so that only one process at a time can change it. Another lock coordinates the use and archiving of the online redo log files. Data files are locked to ensure that multiple instances mount a database in shared mode or that one instance mounts it in exclusive mode. Because file and log locks indicate the status of files, these locks are necessarily held for a long time.
  
  这些锁用于保护一系列的文件。举例来说，一个内部锁保护控制文件在一个时间只能被一个进程修改。另外一种锁用于配合使用和归档在线redo log files。数据文件被锁以确保多个实例共享模式下挂载数据库或者单实例下独占模式下挂载数据库。由于文件和日志文件锁用来标识文件的状态，因此这些锁是需要持有很长时间。

• Tablespace and undo segment locks （表空间和undo段锁）

  These locks protect tablespaces and undo segments. For example, all instances accessing a database must agree on whether a tablespace is online or offline. Undo segments are locked so that only one database instance can write to a segment.
  
  这些锁用于保护表空间和undo段。举例说明，所有的实例访问一个数据库，必须同意一个表空间是online或者offline。undo 段锁用于保证只有一个数据库实例能够写入undo 段 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28697282/viewspace-1484385/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28697282/viewspace-1484385/