Oracle 中 for update 和 for update nowait 的區別

標籤：style   blog   http   color   io   使用   ar   strong   for   

 

原文出處http://bijian1013.iteye.com/blog/1895412

 

一.for update 和 for update nowait 的區別
首先一點，如果只是select 的話，Oracle是不會加任何鎖的，也就是Oracle對 select 讀到的資料不會有任何限制，雖然這時候有可能另外一個進程正在修改表中的資料，並且修改的結果可能影響到你目前select語句的結果，但是因為沒有鎖，所以select結果為當前時刻表中記錄的狀態。
如果加入了for update，則Oracle一旦發現（符合查詢條件的）這批資料正在被修改，則不會發出該select語句查詢，直到資料被修改結束（被commit），馬上自動執行這個select語句。
同樣，如果該查詢語句發出後，有人需要修改這批資料（中的一條或幾條），它也必須等到查詢結束後（commit）後，才能修改。
for update nowait和 for update 都會對所查詢到得結果集進行加鎖，所不同的是，如果另外一個線程正在修改結果集中的資料，for update nowait 不會進行資源等待，只要發現結果集中有些資料被加鎖，立刻返回 “ORA-00054錯誤，內容是資源正忙, 但指定以 NOWAIT 方式擷取資源”。
for update 和 for update nowait 加上的是一個行級鎖，也就是只有符合where條件的資料被加鎖。如果僅僅用update語句來更改資料時，可能會因為加不上鎖而沒有響應地、莫名其妙地等待，但如果在此之前，for update NOWAIT語句將要更改的資料試探性地加鎖，就可以通過立即返回的錯誤提示而明白其中的道理，或許這就是For Update和NOWAIT的意義之所在。
經過測試，以for update 或 for update nowait方式進行查詢加鎖，在select的結果集中，只要有任何一個記錄在加鎖，則整個結果集都在等待系統資源（如果是nowait，則拋出相應的異常）

二.for update nowait 與 for update 的目的
鎖定表的所有行，排斥其他針對這個表的寫操作。確保只有當前事務對指定表進行寫操作。
for update nowait和 for update的區別：
別的事務要對這個表進行寫操作時，是等待一段時間還是馬上就被資料庫系統拒絕而返回。制定採用nowait方式來進行檢索，所以當探索資料被別的session鎖定中的時候，就會迅速返回ORA-00054錯誤，內容是資源正忙。所以在程式中我們可以採用nowait方式迅速判斷當前資料是否被鎖定中，如果鎖定中的話，就要採取相應的業務措施進行處理。

執行個體：

開啟一個PL/SQL，執行update deployop.tt t set t.a = ‘X‘ where t.a = ‘1‘;

再開啟另一個PL/SQL，執行select * from tt t where t.a in (‘1‘,‘2‘) for update nowait; 返回RA-00054錯誤，內容是資源正忙。
如在上面視窗中執行select * from tt t where t.a in (‘1‘,‘2‘) for update，結果只是阻塞，不返回錯誤，如下所示：

三.SELECT...FOR UPDATE 語句的文法
SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED];
其中：
OF 子句用於指定即將更新的列，即鎖定行上的特定列。
WAIT 子句指定等待其他使用者釋放鎖的秒數，防止無限期的等待。
"使用FOR UPDATE WAIT"子句的優點如下：
1.防止無限期地等待被鎖定的行;
2.允許應用程式中對鎖的等待時間進行更多的控制
3.對於互動式應用程式非常有用，因為這些使用者不能等待不確定
4.若使用了skip locked，則可以越過鎖定的行，不會報告由wait n 引發的"資源忙"異常報告

執行個體：

在plsql develope中開啟兩個sql視窗
在1視窗中運行如下sql
select * from t where a=‘1‘ for update;
在2視窗中運行sql1
sql1:select * from t where a=‘1‘; 這一點問題也沒有，因為行級鎖不會影響純粹的select語句
再運行sql2
sql2:select * from t where a=‘1‘ for update; 則這一句sql在執行時，永遠處於等待狀態，除非視窗1中sql被提交或復原。
如何才能讓sql2不等待或等待指定的時間呢？ 我們再運行sql3
sql3:select * from t where a=‘1‘ for update nowait; 則在執行此sql時，直接報資源忙的異常。
若執行 select * from t where a=‘1‘ for update wait 6; 則在等待6秒後，報資源忙的異常。
如果我們執行sql4
sql4:select * from t where a=‘1‘ for update nowait skip Locked; 則執行sql時，即不等待，也不報資源忙異常，略過鎖定的行，只顯示未鎖定的行

例：

視窗1：

視窗2：略過鎖定的a=1的行，只顯示未鎖定的a=2的行

 

四.select for update of

這個of子句在牽連到多個表時，具有較大作用，如不使用of指定鎖定的表的列，則所有表的相關行均被鎖定，若在of中指定了需修改的列，則只有與這些列相關的表的行才會被鎖定。

執行個體1：

select * from tt,tt2 where tt.a = tt2.a2 for update;對二張表都進行了整表鎖定

select * from tt2 for update wait 3;試圖鎖定tt2表，3秒後拋出“ORA-30006: 資源已被佔用;執行操作時出現WAIT 逾時”的錯誤

執行個體2：

select * from tt,tt2 where tt.a = tt2.a2 for update of a;只鎖定tt表，對tt2表不鎖定

select * from tt2 for update wait 3;成功鎖定了tt2表

select * from tt for update wait 3;試圖鎖定tt表，3秒後拋出“ORA-30006: 資源已被佔用;執行操作時出現WAIT 逾時”的錯誤

可以得到結論，for update of columns 用在多表串連鎖定時，可以指定要鎖定的是哪幾張表，而如果表中的列沒有在for update of 後面出現的話，就意味著這張表其實並沒有被鎖定，其他使用者是可以對這些表的資料進行update操作的。這種情況經常會出現在使用者對帶有串連查詢的視圖進行操作環境下。使用者只鎖定相關表的資料，其他使用者仍然可以對視圖中其他原始表的資料來進行操作。

小結：

 

Oracle 中 for update 和 for update nowait 的區別