全面解析oracle中的鎖機制2

預設情況下

T@ora>create table t1 as select * from t ;

Table created.

Elapsed: 00:00:00.07

T@ora>select rowid,ora_rowscn,a from t1;

ROWID                         ORA_ROWSCN          A

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

AAARN5AAGAAAXHcAAA   45387504          1

AAARN5AAGAAAXHcAAB   45387504          2

AAARN5AAGAAAXHcAAC   45387504          3

AAARN5AAGAAAXHcAAD   45387504          4

AAARN5AAGAAAXHcAAE   45387504          5

AAARN5AAGAAAXHcAAF   45387504          6

AAARN5AAGAAAXHcAAG   45387504          7

AAARN5AAGAAAXHcAAH   45387504          8

AAARN5AAGAAAXHcAAI   45387504          9

AAARN5AAGAAAXHcAAJ   45387504         10

10 rows selected.

Elapsed: 00:00:00.20

T@ora>update t1 set a = 0 where a <3;

2 rows updated.

Elapsed: 00:00:00.01

T@ora>commit;

Commit complete.

Elapsed: 00:00:00.00

ROWID                           ORA_ROWSCN          A

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

AAARN5AAGAAAXHcAAA   45387522          0

AAARN5AAGAAAXHcAAB   45387522          0

AAARN5AAGAAAXHcAAC   45387522          3 《----這裡開始後面的記錄都沒做修改，但是ora_rowscn卻改變了

AAARN5AAGAAAXHcAAD   45387522          4

AAARN5AAGAAAXHcAAE   45387522          5

AAARN5AAGAAAXHcAAF   45387522          6

AAARN5AAGAAAXHcAAG   45387522          7

AAARN5AAGAAAXHcAAH   45387522          8

AAARN5AAGAAAXHcAAI   45387522          9

AAARN5AAGAAAXHcAAJ   45387522         10

增加ROWDEPENDENCIES 

T@ora>create table t2 ROWDEPENDENCIES  as select * from t ;

Elapsed: 00:00:00.06

T@ora>select rowid,ora_rowscn,a from t2;

ROWID                              ORA_ROWSCN          A

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

AAARN6AAGAAAXHkAAA   45387561          1

AAARN6AAGAAAXHkAAB   45387561          2

AAARN6AAGAAAXHkAAC   45387561          3

AAARN6AAGAAAXHkAAD   45387561          4

AAARN6AAGAAAXHkAAE   45387561          5

AAARN6AAGAAAXHkAAF   45387561          6

AAARN6AAGAAAXHkAAG   45387561          7

AAARN6AAGAAAXHkAAH   45387561          8

AAARN6AAGAAAXHkAAI   45387561          9

AAARN6AAGAAAXHkAAJ   45387561         10

Elapsed: 00:00:00.03

T@ora>update t2 set a = 0 where a <3;

ROWID                          ORA_ROWSCN          A

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

AAARN6AAGAAAXHkAAA   45387578          0

AAARN6AAGAAAXHkAAB   45387578          0

AAARN6AAGAAAXHkAAC   45387561          3 《--沒有修改的scn就不會改變

存儲空間的影響，dump出來就可以看到2個存儲結構不一樣

普通表

tab 0, row 0, @0x1f1e

tl: 8 fb: --H-FL-- lb: 0x2  cc: 2

col  0: [ 1]  80

col  1: [ 2]  c1 02

2個number類型

tab 0, row 0, @0x1edc

tl: 14 fb: --H-FL-- lb: 0x2  cc: 2

dscn 0x0000.02b48f29

多出6個位元組記錄dscn。

将SCN 轉換為牆上時鐘時間：

使用透明的ORA_ROWSCN 列還有一個好處：可以把SCN 轉換為近似的牆上時鐘時間（有+/–3 秒的偏差），進而發現行最後一次修改發生在什麼時間。例如，可以執行以下查詢：

ops$tkyte@ORA10G> select deptno, ora_rowscn, scn_to_timestamp(ora_rowscn) ts2 from dept;

DEPTNO ORA_ROWSCN          TS

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

10            34676381         25-APR-05 02.37.04.000000000 PM

20            34676364         25-APR-05 02.34.42.000000000 PM

30           34676364          25-APR-05 02.34.42.000000000 PM

40           34676364          25-APR-05 02.34.42.000000000 PM

在此可以看到，在表的最初建立和更新DEPTNO = 10 行之間，我等了大約3 分鐘。不過，從SCN 到牆上時鐘時間的這種轉換有一些限制：資料庫的正常運作時間隻有5 天左右。例如，如果檢視一個“舊”表，查找其中最舊ORA_ROWSCN（注意，在此我作為SCOTT 登入；沒有使用前面的新表）：

scott@ORA10G> select min(ora_rowscn) from dept;

MIN(ORA_ROWSCN)

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

364937

如果我試圖把這個SCN 轉換為一個時間戳，可能看到以下結果（取決于DEPT 表有多舊！）：

scott@ORA10G> select scn_to_timestamp(min(ora_rowscn)) from dept;

select scn_to_timestamp(min(ora_rowscn)) from dept

*

ERROR at line 1:

ORA-08181: specified number is not a valid system change number

ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

ORA-06512: at line 1

是以從長遠看不能依賴這種轉換。

3、樂觀悲觀鎖總結：

到底是用樂觀鎖呢還是悲觀鎖呢？現實生産環境中，使用樂觀鎖的機會遠多于悲觀鎖，最大的原因就是悲觀鎖需要與資料庫建立一個連接配接，這個連接配接會耗費很多的資源，特别是在使用者數超過一百以上，每個使用者有一個連接配接，這樣是不現實的。

一般用樂觀鎖定的版本戳，然後再加一個時間戳，這樣一來，就可以查詢到每一行最後一次修改的時間啦，真是省勁快捷，hash方法那個如果碰到LONG,LONG RAW,CLOB,BLOB這等大資料就死翹翹了。。

4、阻塞和死鎖：

資料庫中有5條常見的語句會發生阻塞，他們分别是：insert，update，delete，merge，select for update。

對于一個阻塞的SELECT FOR UPDATE，解決方案很簡單：隻需增加NOWAIT 子句，它就不會阻塞了。這樣一來, 你的應用會向最終使用者報告，這一行已經鎖定。另外4 條DML 語句才有意思。我們會分别分析這些DML 語句，看看它們為什麼不應阻塞，如果真的阻塞了又該如何修正。

1、阻塞的insert：

INSERT 阻塞的情況不多見。最常見的情況是，你有一個帶主鍵的表，或者表上有惟一的限制，但有兩個會話試圖用同樣的值插入一行。如果是這樣，其中一個會話就會阻塞， 直到另一個會話送出或者復原為止：如果另一個會話送出，那麼阻塞的會話會收到一個錯誤，指出存在一個重複值；倘若另一個會話復原，在這種情況下，阻塞的會話則會成功。還有一種情況，可能多個表通過引用完整性限制互相連結。對子表的插入可能會阻塞，因為它所依賴的父表正在建立或删除。如果應用允許最終使用者生成主鍵/惟一列值，往往就會發生INSERT

阻塞。為避免這種情況，最容易的做法是使用一個序列來生成主鍵/惟一列值。序列（sequence）設計為一種高度并發的方法，用在多使用者環境中生成惟一鍵。如果無法使用序列，那你可以使用以下技術，也就是使用手工鎖來避免這個問題，這裡的手工鎖通過内置的DBMS_LOCK 包來實作。

當然，如果表的主鍵是一個INTEGER，而你不希望這個主鍵超過1 000 000 000，那麼可以跳過散列，直接使用這個數作為鎖ID。要适當地設定散清單的大小（在這個例子中，散清單的大小是1 024），以避免因為不同的串散列為同一個數（這稱為散列沖突）而人工地導緻資源忙消息。散清單的大小與特定的應用（資料）有關，并發插入的數量也會影響散清單的大小。最後，還要記住，盡管Oracle 有無限多個行級鎖，但是enqueue 鎖（這是一種隊列鎖）的個數則是有限的。如果在會話中插入大量行，而沒有送出，可能就會發現建立了太多的enqueue

隊列鎖，而耗盡了系統的隊列資源（超出了ENQUEUE_RESOURCES 系統參數設定的最大值），因為每行都會建立另一個enqueue 鎖。如果确實發生了這種情況， 就需要增大ENQUEUE_RESOURCES 參數的值。還可以向觸發器增加一個标志，允許打開或關閉這種檢查。例如，如果我準備插入數百條或數千條記錄，可能就不希望啟用這個檢查。

2、阻塞的update、delete和merge：

select for updatre nowait 能夠避免丢失更新的問題。他能驗證你自從擷取資料到執行修改這期間沒有别的事務來修改這個資料庫，鎖住這一行，防止update和delect阻塞。不論是樂觀鎖還是悲觀鎖，都會用這一句實作這些功能，隻是悲觀鎖會在你擷取這一行之後就執行了鎖定，而樂觀鎖是在即将送出的時候才執行的。

3、死鎖：

一般來看死鎖的出現常見原因有兩個：外鍵未加索引和 表上的位圖索引遭到并發更新。

在以下情況,oracle修改父表後會對字表加一個索引：1、如果更新了父表的主鍵，因為外鍵上沒有索引，是以子表上會被鎖住；2、删除了父表的一行，子表也會被鎖住。

以上這種鎖，隻有在執行對應的DML語句是才會發生，而不是整個事務期間都會鎖住字表，這樣可以減少死鎖的可能性，但是即使這樣，死鎖還是可以發生的。

删除父表中的一行可能導緻子表被鎖住，由此産生的問題更多。我已經說過，如果删除父表P 中的一行，那麼在DML 操作期間，子表C 就會鎖定，這樣能避免事務期間對C 執行其他更新（當然，這有一個前提，即沒有人在修改C；如果确實已經有人在修改C，删除會等待）。此時就會出現阻塞和死鎖問題。通過鎖定整個表C，資料庫的并發性就會大幅下降，以至于沒有人能夠修改C 中的任何内容。另外，出現死鎖的可能性則增加了，因為我的會話現在“擁有”大量資料，直到送出時才會交出。其他會話因為C

而阻塞的可能性也更大；隻要會話試圖修改C 就會被阻塞。是以，我開始注意到，資料庫中大量會話被阻塞，這些會話持有另外一些資源的鎖。實際上，如果其中任何阻塞的會話鎖住了我的會話需要的資源，就會出現一個死鎖。在這種情況下，造成死鎖的原因是：我的會話不允許别人通路超出其所需的更多資源（在這裡就是一個表中的所有行）。