oracle deadlock 之（一）--鎖機制介紹

    關於oracle的鎖的介紹，11g的官方文件對oracle的鎖的介紹
    
   
    好了，不多廢話，下面我們進入正題
    
提綱如下

    鎖的用途
    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 .
DDL Locks	Protect the structure of schema objects—for example, the dictionary definitions of tables and views. See .
System Locks	Protect internal database structures such as data files. Latches, mutexes, and internal locks are entirely automatic. See .

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:

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 , 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.  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  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  (see ).

資料庫使用佇列機制來獲取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  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 .

資料字典鎖（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 , 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 , 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  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  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,  such as DBWn and LGWR allocate memory from the 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 , 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 自旋
程式 ----------------------------------------------->  latch  ------------------------------------------------->  程式 ----------------------------------------> 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  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  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  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 段