Oracle中select for update ...一些區別

select for update和select for update wait和select for update nowait的區別

CREATE TABLE "TEST6" 
(    
   "ID" VARCHAR2(30), 
   "NAME" VARCHAR2(30), 
   "AGE" NUMBER(2,0), 
   "SEX" VARCHAR2(2), 
   "ENAME" VARCHAR2(30), 
   "ADDTIME" DATE
)
insert into TEST6 (id, name, age, sex, ename, addtime) values ('1', '張三', 18, null, 'zhangsan', to_date('14-03-2017 00:00:09', 'dd-mm-yyyy hh24:mi:ss'));
insert into TEST6 (id, name, age, sex, ename, addtime) values ('2', '李四', null, '1', 'Lisi', to_date('01-03-2017 02:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into TEST6 (id, name, age, sex, ename, addtime) values ('3', '王五', 20, '0', 'wangwu', to_date('09-01-2017 08:55:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into TEST6 (id, name, age, sex, ename, addtime) values ('4', '趙六', 23, '0', 'zhaoliu', to_date('03-03-2016 04:00:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into TEST6 (id, name, age, sex, ename, addtime) values ('5', '馮七', 22, null, 'fengqi', to_date('08-03-2017 12:00:01', 'dd-mm-yyyy hh24:mi:ss'));

CREATE TABLE "TEST8" 
(    
   "ID" NUMBER, 
   "ORDERID" NUMBER, 
   "PRODUCTID" NUMBER, 
   "PRICE" NUMBER(10,2), 
   "QUANTITY" NUMBER
) 
insert into TEST8 (id, orderid, productid, price, quantity) values (1, 1, 1, 6, 10);
insert into TEST8 (id, orderid, productid, price, quantity) values (2, 1, 2, 4, 5);
insert into TEST8 (id, orderid, productid, price, quantity) values (3, 1, 3, 10, 2);
insert into TEST8 (id, orderid, productid, price, quantity) values (4, 2, 1, 3, 6);
insert into TEST8 (id, orderid, productid, price, quantity) values (5, 2, 2, 4, 6);

以上是基礎資料

在oracle中,如果只進行select語句的話,是不會進行加鎖的,也就是oracle會返回當前時刻的結果集,即使這個時候可能有另外一個程式在修改當前結果集的資料,因為沒有加鎖,所以oracle還是會正常的返回當前時刻的結果集,不會有任何影響。

他們三個共同點:

當使用select for update 或者select for update wait或者…,那麼oralce會給符合where條件的資料行加上一個行級鎖

1、select for update

但是如果你的select 語句加了for update,那麼就不是上面這回事了,當oracle發現select的當前結果集中的一條或多條正在被修改(注意:當資料被修改時,此時的資料行是被加鎖的),那麼他就會等到當前當前結果集被修改完畢並且commit之後才進行select操作,並對結果集進行加鎖。同樣的,如果查詢語句發出後,其他會話需要修改結果集中的一條(或幾條資料)也許要等到查詢結束(commit)之後,才可以執行修改操作。

程式碼如下:

新建SQL視窗1，(相當於新建一個session會話)

select * from test8 for update

for update 對整個結果集進行了加鎖,意味著在當前session進行commit之前,任何其他的session進行update、delete、insert操作都會進行等待

新建SQL視窗2(相當於新建一個session會話)

update test8 set price=6 where ID=1

顯示執行中,等待會話一的查詢執行完成

現在我們將會話一的事務提交(commit)

會話二的update語句執行成功

2、select for update nowait

for update和for update nowait都會對查詢到的當前結果集進行加鎖,所不同的是,當有另外的會話在修改當前結果集中的資料,select for nowait所進行的查詢操作不會進行等待,當發現結果集中的一些資料被加鎖,立刻返回 “ORA-00054錯誤，內容是資源正忙, 但指定以 NOWAIT 方式獲取資源”。測試程式碼如下:

新建一個SQL視窗1(相當於新建一個會話)

update test8 set price=3 where ID=1

更新test8表的一條資料,但是不進行commit操作

然後新建SQL視窗2(相當於新建一個會話)select for update nowait操作

select * from test8 for update nowait

總結分析:

因為會話一,並沒有commit所以test8中的ID=1的行被加鎖了,所以當會話二進行select for update nowait檢索到ID=1的資料行被加鎖了,就立刻返回 “ORA-00054錯誤，內容是資源正忙, 但指定以 NOWAIT 方式獲取資源”的錯誤。

接下來我們對會話一進行commit操作,

在執行會話二的select查詢,ok,可以查出來了,並且對當前資料集進行了加鎖操作,其他會話想要進行修改操作,必須等到會話二commit之後

3、select for update wait

它也會對查詢到的結果集進行加鎖,select for update wait與select for update nowait不同的地方是,當有另外的會話對它的查詢結果集中的某一行資料進行了加鎖,那麼它不會像nowait一樣,立即返回"ORA-00054錯誤",而是它支援一個引數,設定等待的時間,當超過了設定的時間,那一行資料還處於加鎖的狀態,那麼它也會返回“ORA-00054錯誤，內容是資源正忙, 但指定以 NOWAIT 方式獲取資源”。測試程式碼如下:

首先新建SQL視窗1(相當於新建一個會話)執行update 語句,但是不進行commit操作,那麼當前資料行將被lock

update test8 set price=3 where ID=1

接著新建SQL視窗2(相當於新建一個會話)，在執行select for update wait 6,如果當前查詢檢索的資料集中,有被加鎖了的行資料,那麼等待6秒,如果6秒後,其他會話,還沒有執行commit釋放被加了鎖的資料行的話,那麼返回“ORA-00054錯誤，內容是資源正忙, 但指定以 NOWAIT 方式獲取資源”。

select * from test8 for update wait 6

執行語句6秒後,報錯。

最後對會話一(SQL視窗一)進行commit操作

緊接著執行會話二(SQL視窗二)中的sql語句,此時被加鎖的資料行被釋放

正常的檢索除了資料行,當時當前資料集被加鎖,其他會話想操作此資料集,必須等會話二中的事務commit之後,才可以進行修改

4、OF子句

在多表查詢中如果需要對多表查詢的結果集進行加鎖,可以使用OF子句。

如果存在OF子句,那麼就對滿足OF子句的單表進行加鎖,如果不存在OF子句就對整個結果集進行加鎖,程式碼如下:

a、不使用OF子句

select a.ID,a.Name,b.price from test6 a
LEFT JOIN test8 b
ON a.ID=b.ID where b.ID>3
for update

沒有進行commit操作,此時對test6和test8中的ID>3的資料行都進行了加鎖,測試程式碼如下:

新建一個會話,執行以下語句:

select * from test6 for update skip locked

select * from test8 for update skip locked

測試結果證明,在沒有OF子句的情況下,對多表查詢的結果集進行select foe update,oracle會對滿足where 條件的所有資料行進行加鎖

b、使用OF子句

使用OF子句,那麼oracle就會對滿足OF子句的表進行加鎖,在多表查詢中。程式碼如下:

select a.ID,a.Name,b.price from test6 a
LEFT JOIN test8 b
ON a.ID=b.ID where b.ID>3
for update of a.ID

在不執行commit操作的情況,新建一個會話,執行一下語句:

select * from test6 for update skip locked

select * from test8 for update skip locked

比對測試結果,發現在OF子句的作用下,oracle對同時滿足where子句(設定要加鎖的資料行)和OF子句(主要設定加鎖的表)的資料行進行了加鎖。