Oracle 學(xué)習(xí)之性能優(yōu)化（十）鎖

  鎖（lock)是用于防止在訪問相同的資源(包括用戶對(duì)象、系統(tǒng)對(duì)象、內(nèi)存、Oralce數(shù)據(jù)字典中的共享數(shù)據(jù)結(jié)構(gòu)，最常見的是數(shù)據(jù)庫表Table對(duì)象)時(shí) ，事務(wù)之間的有害性 交互（存、取）的一種機(jī)制。

  不同類型的鎖，代表了當(dāng)前用戶是允許還是阻止其它用戶對(duì)相同資源的同時(shí)存取，從而確保不破壞系統(tǒng)數(shù)據(jù)的完整性、一致性和并行性。

  加鎖是實(shí)現(xiàn)數(shù)據(jù)庫并發(fā)控制的一個(gè)非常重要的技術(shù)。當(dāng)事務(wù)在對(duì)某個(gè)數(shù)據(jù)對(duì)象進(jìn)行操作前，先向系統(tǒng)發(fā)出請(qǐng)求，對(duì)其加鎖。加鎖后事務(wù)就對(duì)該數(shù)據(jù)對(duì)象有了一定的控制，在該事務(wù)釋放鎖之前，其他的事務(wù)不能對(duì)此數(shù)據(jù)對(duì)象進(jìn)行更新操作。

鎖的分類

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

• DDL鎖：CREATE和ALTER語句操作

• 內(nèi)部鎖和閂：Oracle使用這些鎖來包含內(nèi)部數(shù)據(jù)結(jié)構(gòu)，例如：Oracle查詢生成的執(zhí)行計(jì)劃，執(zhí)行計(jì)劃會(huì)保存在庫緩存中，當(dāng)使用這個(gè)執(zhí)行計(jì)劃時(shí)，會(huì)對(duì)其加一個(gè)閂（latch）
  

DML鎖

  用于確保一次只能一個(gè)人修改某行數(shù)據(jù)。而且你正常處理這個(gè)表時(shí)，別人不能刪除這個(gè)表。

1. TX鎖，事務(wù)發(fā)起第一個(gè)修改時(shí)，會(huì)得到一個(gè)TX鎖（事務(wù)鎖），而且會(huì)一直持有這個(gè)事務(wù)，直到事務(wù)結(jié)束（COMMIT或者ROLLBACK）。事務(wù)中修改或者select for update的每一行都會(huì)指向該事務(wù)的TX鎖。

2. TM鎖，用于確保在修改表內(nèi)容時(shí)，表的結(jié)構(gòu)不被改變。

下面舉例說明

登錄到scott用戶，并確定session id

SQL> grant select any dictionary to scott;

Grant succeeded.

SQL> conn scott/tiger

SQL> select sid from v$mystat where rownum=1;

       SID
----------
	37

SQL>

另開啟一個(gè)會(huì)話，監(jiān)控鎖的使用情況

SQL> set linesize 200
SQL> 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	   481		0

SQL>

會(huì)話1執(zhí)行更新操作

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

14 rows updated.

會(huì)話2查看結(jié)果

SQL> /

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

多了一個(gè)事務(wù)鎖，一個(gè)TM鎖。

會(huì)話1中再執(zhí)行另一個(gè)表的更新操作

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

4 rows updated.

會(huì)話2查看結(jié)果

SQL> /

ADDR		 KADDR			 SID TY        ID1	  ID2	   LMODE    REQUEST	 CTIME	    BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0000000090D8FC88 0000000090D8FCE0	  37 AE        100	    0	       4	  0	  1174		0
00007FF9EEDA7B58 00007FF9EEDA7BB8	  37 TM      87108	    0	       3	  0	   233		0
00007FF9EEDA7B58 00007FF9EEDA7BB8	  37 TM      87106	    0	       3	  0	    27		0
000000008F673658 000000008F6736D0	  37 TX     131074	  908	       6	  0	   233		0

事務(wù)鎖沒有變，但是又多了一個(gè)TM鎖。

對(duì)于TM鎖來說，id1的值是對(duì)象的ID

SQL> COL OBJECT_NAME FOR A30
SQL> select OBJECT_NAME,OBJECT_ID from dba_objects where owner='SCOTT' AND OBJECT_NAME IN ('DEPT','EMP');

OBJECT_NAME			OBJECT_ID
------------------------------ ----------
DEPT				    87106
EMP				    87108

對(duì)于TX鎖，id1是通過事務(wù)id轉(zhuǎn)換來的。

我們先查看下事務(wù)的相關(guān)信息

SQL> SELECT addr,xidusn,xidslot,xidsqn FROM V$TRANSACTION;

ADDR		     XIDUSN    XIDSLOT	   XIDSQN
---------------- ---------- ---------- ----------
000000008F673658	  2	     2	      908

ADDR與TX鎖的ADDR對(duì)應(yīng)，XIDUSN表示回滾段編號(hào)，XIDSLOT表示事務(wù)表上的編號(hào)，XIDSQN表示sequence(覆蓋次數(shù))

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

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

2*POWER(2,16)+2
---------------
	 131074

事務(wù)表、回滾塊、事務(wù)槽 三者之間的關(guān)系如下：

對(duì)于TX鎖，并沒有一個(gè)視圖能提供事務(wù)修改了哪些行。行鎖的信息是保存在數(shù)據(jù)塊中的。

下面我們將dept的數(shù)據(jù)塊dump出，查看塊的詳細(xì)信息

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

只占用了一個(gè)數(shù)據(jù)塊，將該塊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/Fsc
0x01   0x0009.002.00000364  0x00c167b0.009a.2e  C---    0  scn 0x0000.000e0ef0
0x02   0x0002.002.0000038c  0x00c00591.0088.26  ----    4  fsc 0x0000.00000000
bdba: 0x01000087
data_block_dump,data header at 0x7f23e518ea64
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x7f23e518ea64
     76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f3c
avsp=0x1f22
tosp=0x1f22
0xe:pti[0]	nrow=4	offs=0
0x12:pri[0]	offs=0x1f7e
0x14:pri[1]	offs=0x1f68
0x16:pri[2]	offs=0x1f54
0x18:pri[3]	offs=0x1f3c
block_row_dump:
tab 0, row 0, @0x1f7e
tl: 26 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 0b
col  1: [10]  41 63 63 6f 75 6e 74 69 6e 67
col  2: [ 8]  4e 45 57 20 59 4f 52 4b
tab 0, row 1, @0x1f68
tl: 22 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 15
col  1: [ 8]  52 65 73 65 61 72 63 68
col  2: [ 6]  44 41 4c 4c 41 53
tab 0, row 2, @0x1f54
tl: 20 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 1f
col  1: [ 5]  53 61 6c 65 73
col  2: [ 7]  43 48 49 43 41 47 4f
tab 0, row 3, @0x1f3c
tl: 24 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 29
col  1: [10]  4f 70 65 72 61 74 69 6f 6e 73
col  2: [ 6]  42 4f 53 54 4f 4e
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 135 maxblk 135

1）lb: 0x2 表示改行數(shù)據(jù)被鎖定，標(biāo)志為2，它表示ITL事務(wù)槽的第二條事務(wù)信息；而第二條事務(wù)信息

的flag為空，表示沒有提交，所以該行被鎖定了(當(dāng)然我們開需要查看事務(wù)表中的提交標(biāo)志)。

2）Lck=4 表示鎖定了4行數(shù)據(jù)。

鎖的mode有如下幾種

死鎖-deadlock 
定義:當(dāng)兩個(gè)用戶希望持有對(duì)方的資源時(shí)就會(huì)發(fā)生死鎖. 
即兩個(gè)用戶互相等待對(duì)方釋放資源時(shí),oracle認(rèn)定為產(chǎn)生了死鎖,在這種情況下,將以犧牲一個(gè)用戶作為代價(jià),另一個(gè)用戶繼續(xù)執(zhí)行,犧牲的用戶的事務(wù)將回滾. 
例子： 
會(huì)話1，執(zhí)行如下操作

SQL> conn scott/tiger
Connected.
SQL> update dept set dname=lower(dname);

4 rows updated.

會(huì)話2，執(zhí)行如下操作

SQL> conn scott/tiger
Connected.
SQL> update emp set ename=lower(ename);

14 rows updated.

會(huì)話1，再執(zhí)行對(duì)emp的更新

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

此時(shí)會(huì)話1被阻塞。

會(huì)話2，執(zhí)行對(duì)dept表的更新

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

此時(shí)會(huì)話2也被阻塞，但是會(huì)話1會(huì)報(bào)一個(gè)死鎖的錯(cuò)誤

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

會(huì)話1需要提交或者回滾，會(huì)話2才能正常執(zhí)行。

Oracle的死鎖問題實(shí)際上很少見，如果發(fā)生，基本上都是不正確的程序設(shè)計(jì)造成的，經(jīng)過調(diào)整后，基本上都會(huì)避免死鎖的發(fā)生。