oracle資料庫事務transaction 不同的鎖lock型別

資料庫鎖概述

        oracle在事務執行會自動對操作資源進行鎖定，防止其它事務對同一個資源做破壞性存取。資料庫自動根據操作資源型別不同，對資源加上各種型別的鎖。

        oracle根據操作資源的型別，把鎖分為如下分類

•          DML鎖：保護資料，例如表鎖鎖定這個表，行級鎖鎖定選擇的行

•          DDL鎖：保護物件的定義，例如表和檢視的資料字典

•          系統鎖：保護內部資料庫的結構，例如資料檔案，latch，mutexes和內部鎖定，這些都是自動實現的

相關閱讀  
oracle資料庫事務transaction隔離級別isolation level的選擇依據

dml鎖

     一個DML鎖，又叫做資料鎖，用來保證多個使用者併發事務執行時的資料完整性。

例如:一個DML鎖可以防止兩個使用者在線上商店購買最後一本書。DML語句自動獲取如下型別的鎖：行級鎖TX和表鎖TM

示例

    SQL> update t_test set a=1;

    2 rows updated.

可見 產生事務會 在原有基礎上新增2種鎖，一為表鎖tm,其鎖模式為行級排它鎖,二為行鎖tx，其鎖模式為排它鎖

SQL> /

    SID TY     ID1 ID2 LMODE  REQUEST

---------- -- ---------- ---------- ---------- ----------

25 AE     133  0     4 0

25 TX   65566      1359 6 0  dml鎖

25 TM   76989  0     3 0  dml鎖

SQL> desc dba_dml_locks;

 Name   Null?    Type

 ----------------------------------------- -------- ----------------------------

 SESSION_ID    NUMBER

 OWNER   NOT NULL VARCHAR2(128)

 NAME   NOT NULL VARCHAR2(128)

 MODE_HELD    VARCHAR2(13)

 MODE_REQUESTED    VARCHAR2(13)

 LAST_CONVERT    NUMBER

 BLOCKING_OTHERS    VARCHAR2(40)

 

SQL> select session_id,owner,name,mode_held,mode_requested,last_convert,blocking_others from dba_dml_locks

SESSION_ID OWNER NAME     MODE_HELD   MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS

---------- -------------------- -------------------- ------------- ------------- ------------ ----------------------------------------

25     USER_DDL T_TEST     Row-X (SX)    None 1052         Not Blocking

  

        oracle在鎖定行所在的資料塊（事務需要修改的資料塊）中儲存鎖的相關資訊。資料庫使用佇列機制獲取行級鎖，如果事務需要一個未鎖定行的鎖，那麼事務在資料塊中在放一個鎖,事務修改的每一行都會指向資料塊頭部（ITL）中的事務ID。當事務結束時，事務ID仍然留在資料塊頭部的ITL中。如果不同的事務想要修改一行資料，資料庫會使用ITL中原來事務的ID，透過查詢相關動態檢視判斷事務是否還存在，及鎖是否存在，如果鎖仍然是活動的，那麼會話排隊等待事務結束後的通知，如果鎖不活動了，那麼，事務得到鎖，並更新ITL表

小結

•    oracle會在資料塊中記錄鎖的資訊及事務的資訊

•    oracle會話如需要獲取表記錄的鎖，先查詢表記錄所屬資料塊是否已存在鎖，在資料塊中儲存事務及鎖的資料結構叫itl

•    itl在資料塊的頭塊

•    oracle會話發現修改資料塊有活動事務，即持鎖，它會等待

•    oracle dml鎖是採用排隊機制實現即先到先到，後到後等演算法

•    oracle的在獲取到資料塊進行修改時，需要在資料塊頭部的itl修改資料塊的事務狀態,表明資料塊正被修改

•    tm鎖即表鎖，會有5種不同的鎖模式，之前文章講過，不再複述

ddl鎖

       當DDL操作或者關聯操作某物件時，DDL鎖保護物件的定義。只有在DDL語句中修改或者引用的物件才被鎖定，資料庫不會鎖定整個資料字典。oracle資料庫代表DDL事務自動實現DDL鎖。

       使用者不能顯式獲得DDL鎖。例如，一個使用者建立一個儲存過程，資料庫自動獲得儲存過程中引入的物件的DDL鎖。DDL鎖阻止儲存過程編譯過程中這些物件的修改和刪除

小結

•     ddl鎖儲存資料定義的資料結構

•     ddl鎖只有在修改資料定義的資料結構或引用資料定義的資料結構，才會持有ddl鎖

•     修改資料定義的資料結構操作，比如:create table,alter table類似的語句

•     引用資料定義的資料結構操作，比如：基於源表建立儲存過程或呼叫執行儲存過程（因為必須保證在執行儲存過程期間依賴基表的完整性）

•     ddl鎖由oracle自身控制，人為無法控制，無法顯式獲取ddl鎖

•     ddl鎖一般看不到，因為ddl操作極快

•     ddl鎖底層是透過鎖定資料字典實現

SQL> desc dba_ddl_locks;

 Name   Null?    Type

 ----------------------------------------- -------- ----------------------------

 SESSION_ID    NUMBER

 OWNER    VARCHAR2(128)

 NAME    VARCHAR2(1000)

 TYPE    VARCHAR2(40)

 MODE_HELD    VARCHAR2(9)

 MODE_REQUESTED    VARCHAR2(9)

即使沒有執行資料庫事務，仍存在ddl鎖，下述ddl鎖就是保護各種物件型別的定義結構不被破壞

SQL> select session_id,owner,name,type,mode_held,mode_requested from dba_ddl_locks;

SESSION_ID OWNER NAME     TYPE      MODE_HELD MODE_REQU

---------- -------------------- -------------------- ---------------------------------------- --------- ---------

32 SYS KUPU$UTILITIES     Table/Procedure/Type      Null None

32 SYS STANDARD     Table/Procedure/Type      Null None

32 SYS STANDARD     Table/Procedure/Type      Null None

32 SYS DBMS_PRVT_TRACE      Table/Procedure/Type      Null None

74 SYSTEM SYSTEM     18      Null None

76 SYSTEM SYSTEM     18      Null None

75 SYSTEM SYSTEM     18      Null None

74 SYS DBMS_OUTPUT     Body      Null No     

SQL> grant execute on dbms_lock to system;

Grant succeeded.

create or replace procedure proc_t_test

as

v_cnt int;

begin

dbms_lock.sleep(300);

select count(a) into v_cnt from t_test;

end;

/

--未執行相關與DDL前2個測試會話各為25及74的執行資訊

SQL> select session_id,owner,name,type,mode_held,mode_requested from dba_ddl_locks where session_id in (25,74)

SESSION_ID OWNER   NAME      TYPE       MODE_HELD MODE_REQU

---------- --------------- -------------------------------------------------- ---------------------------------------- --------- ---------

25 SYSTEM   SYSTEM          18            Null None

74 SYSTEM   SYSTEM          18            Null None

74 SYS   DBMS_OUTPUT      Body            Null None

25 SYS   DBMS_LOCK      Body            Null None

74 SYS   DBMS_OUTPUT      Table/Procedure/Type    Null None

25 LBACSYS   LBAC_EVENTS      Body            Null None

25 LBACSYS   LBAC_EVENTS      Table/Procedure/Type    Null None

25 SYS   DBMS_APPLICATION_INFO  Body            Null None

74 SYS   DBMS_APPLICATION_INFO  Body            Null None

25 SYS   DBMS_STANDARD      Table/Procedure/Type    Null None

74 SYS   PLITBLM          Table/Procedure/Type    Null None

SESSION_ID OWNER   NAME      TYPE       MODE_HELD MODE_REQU

---------- --------------- -------------------------------------------------- ---------------------------------------- --------- ---------

74       SYSTEM          73            Share None

25       SYSTEM          73            Share None

25 SYS   DBMS_APPLICATION_INFO  Table/Procedure/Type    Null None

74 SYS   DBMS_APPLICATION_INFO  Table/Procedure/Type    Null None

25 MDSYS   GETMDSYSEVENT      Table/Procedure/Type    Null None

25 SYS   DBMS_LOCK      Table/Procedure/Type    Null None

25 SYS   DATABASE          18            Null None

74 SYS   DATABASE          18            Null None

19 rows selected.

---會話25

執行儲存過程

SQL> exec proc_t_test;

---會話74

正在執行儲存過程期間刪除儲存過程

卡住

SQL> drop procedure proc_t_test;

SQL> select session_id,owner,name,type,mode_held,mode_requested from dba_ddl_locks where session_id in (25,74) and mode_held='Exclusive' or mode_requested='Exclusive'

SESSION_ID OWNER  NAME     TYPE  MODE_HELD MODE_REQU

---------- ------------------------------ -------------------------------------------------- -------------------- --------- ---------

74 SYSTEM  PROC_T_TEST     Table/Procedure/Type Exclusive None

SQL> /

SESSION_ID OWNER  NAME     TYPE          MODE_HELD  MODE_REQU

---------- ------------------------------ -------------------------------------------------- -------------------- 

25 SYSTEM  PROC_T_TEST     Table/Procedure/Type   Null  None  持鎖會話（持鎖模式為null）

74 SYSTEM  PROC_T_TEST     Table/Procedure/Type   Exclusive None  等待會話 (請求持鎖模式為排它模式)

SQL> select distinct type from dba_ddl_locks;

TYPE

--------------------

73

Table/Procedure/Type

18

10

Body

23

6 rows selected.

---可見產生ddl鎖，不會體現在v$lock中

SQL> select sid,type,id1,id2,lmode,request from v$lock where sid in (25,74);

    SID TY     ID1 ID2 LMODE  REQUEST

---------- -- ---------- ---------- ---------- ----------

74 AE     133  0     4 0

25 AE     133  0     4 0

--可見產生ddl鎖，持ddl鎖的會話等待事件為library cache pin

    SID STATUS   BLOCKING_SESSION EVENT

---------- -------- ---------------- ----------------------------------------------------------------

25 ACTIVE              PL/SQL lock timer

74 ACTIVE  25          library cache pin

--等待事件對應如下的library cache pin,library cache pin對sga的library cache記憶體資料結構的一種儲存機制

SQL> col type for a50

SQL> col name for a35

SQL> col id1_tag for a30

SQL> col id2_tag for a30

SQL> col description for a50

SQL> select type,name,id1_tag,id2_tag,is_user,description from v$lock_type where lower(description) like '%library%';

TYPE   NAME       ID1_TAG    ID2_TAG              IS_ DESCRIPTION

---------- ----------------------------------- -------------------- ------------------------- --- ----------------------------------------------------------------------------------------------------

V   Library Cache Lock 3       hash value    hash value      NO  Synchronizes accesses to library cache objects

E   Library Cache Lock 2       hash value    hash value      NO  Synchronizes accesses to library cache objects

L   Library Cache Lock 1       hash value    hash value      NO  Synchronizes accesses to library cache objects

Y   Library Cache Pin 3       hash value    hash value      NO  Synchronizes accesses to the contents of library cache objects

G   Library Cache Pin 2       hash value    hash value      NO  Synchronizes accesses to the contents of library cache objects

N   Library Cache Pin 1       hash value    hash value      NO  Synchronizes accesses to the contents of library cache objects

IV   Library Cache Invalidation   object #    time stamp      NO  Synchronizes library cache object invalidations across instances

7 rows selected.

ddl鎖又分為幾種型別：

•   排它ddl鎖

      排它DDL鎖阻止其他會話獲得DDL和DML鎖。例如刪除一個表的操作會阻止同時在表中新增一列的DDL操作，反之亦然。排它的DDL鎖在整個DDL操作時有效，執行結束會自動提交

•   共享ddl鎖

      共享DDL鎖防止其它衝突的DDL操作，但是允許類似的DDL操作併發執行。例如當執行DDL操作時，會對引用的所有表加DDL共享鎖，其它事務可以建儲存過程時加共享DDL鎖，但

      是不允許加排它DDL鎖

•   易碎解析鎖

      sql或者pl/sql會持有應用物件的解析鎖。解析鎖被用來實現當引用的物件被修改或者刪除時，共享sql區域會失效。解析鎖之所以易碎，是因為它不允許DDL操作，當DDL衝突

      時，會被打碎

小結

   上述的ddl鎖和dml鎖不太一樣，不好理解。後續會有專門文章分享。

系統鎖

oracle使用系統鎖保護內部的資料庫和記憶體結構，使用者不能操作這些內部鎖,它由資料庫自己控制。

系統鎖分為 latch,mutexes,內部鎖internal lock

閂latch

latch是為了保護sga記憶體資料結構的一致，實現的一種底級鎖機制

SQL> select count(*) from v$latchname;

  COUNT(*)

----------

       902

SQL> select distinct type from v$latchname;

TYPE

----

SGA

OSP

SQL> 

SQL> select name,hash,type from v$latchname where lower(name) like '%library%';

NAME       HASH TYPE

---------------------------------------------------------------- ---------- ----

library cache load lock 2952162927 SGA          

mutex

mutex不同上於上述的latch,latch保護一組物件，而mutex更低階，它僅保護一個物件，它是程式碼層面的，相當底層

SQL> select mutex_type from v$mutex_sleep;

MUTEX_TYPE

--------------------------------

Row Cache

Library Cache

Cursor Pin

內部鎖

它是高階別，比latch和mutex更復制，用於其它用途。資料庫具有一些型別的內部鎖：

• 資料字典快取鎖

這種鎖時間短，當資料字典實體被修改時，用來保護相關內容。這種鎖確保語句解析期間，能夠看到物件的一致性檢視。資料

字典鎖是共享和排它的。解析結束時，共享鎖被釋放，DDL操作結束時，排它鎖被釋放

• 檔案和日誌管理鎖

這種鎖保護各種檔案，例如，內部鎖保護控制檔案，確保一個時間點只有一個程式能夠修改。另外的鎖能協調歸檔和線上日誌。當

多例項共享模式掛載資料庫或者單例項排它掛載時會對資料檔案加鎖。由於檔案鎖標識著檔案的狀態，這些鎖持續的時間一般都比較長

• 表空間和undo段鎖

用來保護表空間和undo段，例如所有例項必須對錶空間是否線上達成一致

聯絡方式