SELECT FOR UPDATE SKIP LOCKED --- 一個未公開但十分有用的特性

SELECT FOR UPDATE SKIP LOCKED 選項是ORACLE的一個未公開的特性，它的含義是SELECT時跳過被鎖的記錄。

考慮下面的例子：

會話1：

SQL> select * from sex_dict ;

 

 SERIAL_NO S SEX_ INPUT_CO

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

         1 1 男    N

         9 0 未知  WZ

         2 2 女    N

         4 9 未定  WD

SQL> select * from sex_dict  where serial_no = 1 for update ;

 

 SERIAL_NO S SEX_ INPUT_CO

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

         1 1 男    N

 

會話2：

查詢並鎖住serial_no in (1,2) 的記錄

SQL> select * from sex_dict where serial_no in ( 1, 2) for update ;

 

此時會話2掛住，直到會話1事務結束。

 

加上”NOWAIT”選項

SQL>  select * from sex_dict where serial_no in ( 1, 2) for update nowait ;

 select * from sex_dict where serial_no in ( 1, 2) for update nowait

               *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

因為serial_no = 1的記錄被會話鎖住，所以這個操作沒有成功。

 

加上SKIP LOCKED選項

SQL>  select * from sex_dict where serial_no in ( 1, 2) for update nowait  skip locked;

 

 SERIAL_NO S SEX_ INPUT_CO

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

         2 2 女    N

 

會話2只鎖住serial_no=2的記錄，跳過serial_no=1的記錄。

 

這個特性適合例如訊息佇列的操作，當訊息到達時，處理訊息的各個客戶選取並鎖住一個訊息處理，但不能阻塞其它客戶處理訊息。其它客戶則處理沒有被鎖的訊息。這個可以參考的《》

利用這個特點，Tomac給出了一個不跳號序列號的方法