Oracle 學習之效能最佳化（十）鎖

標籤：oracle 鎖

  鎖（lock)是用於防止在訪問相同的資源(包括使用者物件、系統對象、記憶體、Oralce資料字典中的共用資料結構，最常見的是資料庫表Table對象)時 ，事務之間的有害性 互動（存、取）的一種機制。

  不同類型的鎖，代表了目前使用者是允許還是阻止其它使用者對相同資源的同時存取，從而確保不破壞系統資料的完整性、一致性和並行性。

  加鎖是實現資料庫並發控制的一個非常重要的技術。當事務在對某個資料對象進行操作前，先向系統發出請求，對其加鎖。加鎖後事務就對該資料對象有了一定的控制，在該事務釋放鎖之前，其他的事務不能對此資料對象進行更新操作。

鎖的分類

• DML鎖：SELECT、INSERT、UPDATE、DELETE、MERGE操作

• DDL鎖：CREATE和ALTER語句操作

• 內部鎖和閂：Oracle使用這些鎖來包含內部資料結構，例如：Oracle查詢產生的執行計畫，執行計畫會儲存在庫緩衝中，當使用這個執行計畫時，會對其加一個閂（latch）
  

 

DML鎖

  用於確保一次只能一個人修改某行資料。而且你正常處理這個表時，別人不能刪除這個表。

1. TX鎖，事務發起第一個修改時，會得到一個TX鎖（事務鎖），而且會一直持有這個事務，直到事務結束（COMMIT或者ROLLBACK）。事務中修改或者select for update的每一行都會指向該事務的TX鎖。

2. TM鎖，用於確保在修改表內容時，表的結構不被改變。

下面舉例說明

登入到scott使用者，並確定session id

SQL> grant select any dictionary to scott;Grant succeeded.SQL> conn scott/tigerSQL> select sid from v$mystat where rownum=1;       SID----------37SQL>

另開啟一個會話，監控鎖的使用方式

SQL> set linesize 200SQL> select * from v$lock where sid=37;ADDR KADDR SID TY        ID1  ID2   LMODE    REQUEST CTIME    BLOCK---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------0000000090D8FC88 0000000090D8FCE0  37 AE        100    0       4  0   4810SQL>

會話1執行更新操作

SQL> update emp set ename=initcap(ename);14 rows updated.

會話2查看結果

SQL> /ADDR KADDR SID TY        ID1  ID2   LMODE    REQUEST CTIME    BLOCK---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------0000000090D8FC88 0000000090D8FCE0  37 AE        100    0       4  0   944000007FF9EEDA4AB0 00007FF9EEDA4B10  37 TM      87108    0       3  0     30000000008F673658 000000008F6736D0  37 TX     131074  908       6  0     30

多了一個事務鎖，一個TM鎖。

會話1中再執行另一個表的更新操作

SQL> update dept set dname=initcap(dname);4 rows updated.

會話2查看結果

SQL> /ADDR KADDR SID TY        ID1  ID2   LMODE    REQUEST CTIME    BLOCK---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------0000000090D8FC88 0000000090D8FCE0  37 AE        100    0       4  0  1174000007FF9EEDA7B58 00007FF9EEDA7BB8  37 TM      87108    0       3  0   233000007FF9EEDA7B58 00007FF9EEDA7BB8  37 TM      87106    0       3  0    270000000008F673658 000000008F6736D0  37 TX     131074  908       6  0   2330

事務鎖沒有變，但是又多了一個TM鎖。

對於TM鎖來說，id1的值是對象的ID

SQL> COL OBJECT_NAME FOR A30SQL> select OBJECT_NAME,OBJECT_ID from dba_objects where owner=‘SCOTT‘ AND OBJECT_NAME IN (‘DEPT‘,‘EMP‘);OBJECT_NAMEOBJECT_ID------------------------------ ----------DEPT    87106EMP    87108

對於TX鎖，id1是通過事務id轉換來的。

我們先查看下事務的相關資訊

SQL> SELECT addr,xidusn,xidslot,xidsqn FROM V$TRANSACTION;ADDR     XIDUSN    XIDSLOT   XIDSQN---------------- ---------- ---------- ----------000000008F673658  2     2      908

ADDR與TX鎖的ADDR對應，XIDUSN表示復原段編號，XIDSLOT表示事務表上的編號，XIDSQN表示sequence(覆蓋次數)

TX鎖的id1的值等於XIDUSN*power(2,16)+XIDSLOT

SQL> select 2*power(2,16)+2 from dual;2*POWER(2,16)+2--------------- 131074

事務表、復原塊、事務槽 三者之間的關係如下：

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/72/D8/wKiom1XuaU2wbsNRAAF48Zkh6Os546.jpg" title="事務與復原段.jpg" alt="wKiom1XuaU2wbsNRAAF48Zkh6Os546.jpg" />

對於TX鎖，並沒有一個視圖能提供事務修改了哪些行。行鎖的資訊是儲存在資料區塊中的。

下面我們將dept的資料區塊dump出，查看塊的詳細資料

SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) bno from dept;       FNO  BNO---------- ---------- 4  135 4  135 4  135 4  135

只佔用了一個資料區塊，將該塊dump

SQL> alter system dump datafile 4 block 135;System altered.SQL> SELECT    d.VALUE       || ‘/‘       || LOWER (RTRIM (i.instance, CHR (0)))       || ‘_ora_‘       || p.spid       || ‘.trc‘          trace_file_name  FROM (SELECT p.spid          FROM v$mystat m, v$session s, v$process p         WHERE m.statistic  2    3    4    5    6    7    8    9   10  # = 1 AND s.sid = m.sid AND p.addr = s.paddr) p,       (SELECT t.instance          FROM v$thread t, v$parameter v         WHERE     v.name = ‘thread‘               AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,       (SELECT VALUE           11   12   13   14   15   16  FROM v$parameter         WHERE name = ‘user_dump_dest‘) d;    17  TRACE_FILE_NAME--------------------------------------------------------------------------------/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5593.trc

查看trace檔案

Block header dump:  0x01000087 Object id on Block? Y seg/obj: 0x15442  csc: 0x00.fab7a  itc: 2  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1000080 ver: 0x01 opc: 0     inc: 0  exflg: 0  Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0009.002.00000364  0x00c167b0.009a.2e  C---    0  scn 0x0000.000e0ef00x02   0x0002.002.0000038c  0x00c00591.0088.26  ----    4  fsc 0x0000.00000000bdba: 0x01000087data_block_dump,data header at 0x7f23e518ea64===============tsiz: 0x1f98hsiz: 0x1apbl: 0x7f23e518ea64     76543210flag=--------ntab=1nrow=4frre=-1fsbo=0x1afseo=0x1f3cavsp=0x1f22tosp=0x1f220xe:pti[0]nrow=4offs=00x12:pri[0]offs=0x1f7e0x14:pri[1]offs=0x1f680x16:pri[2]offs=0x1f540x18:pri[3]offs=0x1f3cblock_row_dump:tab 0, row 0, @0x1f7etl: 26 fb: --H-FL-- lb: 0x2  cc: 3col  0: [ 2]  c1 0bcol  1: [10]  41 63 63 6f 75 6e 74 69 6e 67col  2: [ 8]  4e 45 57 20 59 4f 52 4btab 0, row 1, @0x1f68tl: 22 fb: --H-FL-- lb: 0x2  cc: 3col  0: [ 2]  c1 15col  1: [ 8]  52 65 73 65 61 72 63 68col  2: [ 6]  44 41 4c 4c 41 53tab 0, row 2, @0x1f54tl: 20 fb: --H-FL-- lb: 0x2  cc: 3col  0: [ 2]  c1 1fcol  1: [ 5]  53 61 6c 65 73col  2: [ 7]  43 48 49 43 41 47 4ftab 0, row 3, @0x1f3ctl: 24 fb: --H-FL-- lb: 0x2  cc: 3col  0: [ 2]  c1 29col  1: [10]  4f 70 65 72 61 74 69 6f 6e 73col  2: [ 6]  42 4f 53 54 4f 4eend_of_block_dumpEnd dump data blocks tsn: 4 file#: 4 minblk 135 maxblk 135

1）lb: 0x2 表示改行資料被鎖定，標誌為2，它表示ITL事務槽的第二條事務資訊；而第二條事務資訊

的flag為空白，表示沒有提交，所以該行被鎖定了(當然我們開需要查看事務表中的提交標誌)。

2）Lck=4 表示鎖定了4行資料。

鎖的mode有如下幾種

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/72/D8/wKiom1XubwWhtulEAALZ7CqgjkY557.jpg" title="lock mode.PNG" alt="wKiom1XubwWhtulEAALZ7CqgjkY557.jpg" />

死結-deadlock 
定義:當兩個使用者希望持有對方的資源時就會發生死結. 
即兩個使用者互相等待對方釋放資源時,oracle認定為產生了死結,在這種情況下,將以犧牲一個使用者作為代價,另一個使用者繼續執行,犧牲的使用者的事務將復原. 
例子： 
會話1，執行如下操作

SQL> conn scott/tigerConnected.SQL> update dept set dname=lower(dname);4 rows updated.

會話2，執行如下操作

SQL> conn scott/tigerConnected.SQL> update emp set ename=lower(ename);14 rows updated.

會話1，再執行對emp的更新

SQL> update emp set ename=lower(ename);

此時會話1被阻塞。

會話2，執行對dept表的更新

SQL>  update dept set dname=lower(dname);

此時會話2也被阻塞，但是會話1會報一個死結的錯誤

SQL> update emp set ename=lower(ename);update emp set ename=lower(ename)       *ERROR at line 1:ORA-00060: deadlock detected while waiting for resource

會話1需要提交或者復原，會話2才能正常執行。

Oracle的死結問題實際上很少見，如果發生，基本上都是不正確的程式設計造成的，經過調整後，基本上都會避免死結的發生。 

本文出自 “叮咚” 部落格，請務必保留此出處http://lqding.blog.51cto.com/9123978/1692746

Oracle 學習之效能最佳化（十）鎖