Select For update語句淺析

Select …forupdate語句是我們經常使用手工加鎖語句。通常情況下，select語句是不會對資料加鎖，妨礙影響其他的DML和DDL操作。同時，在多版本一致讀機制的支援下，select語句也不會被其他類型語句所阻礙。

 

藉助for update子句，我們可以在應用程式的層面手工實現資料加鎖保護操作。本篇我們就來介紹一下這個子句的用法和功能。

 

下面是采自Oracle官方文檔《SQLLanguage Reference》中關於for update子句的說明：(請雙擊點開圖片查看)

 

 

從for update子句的文法狀態圖中，我們可以看出該子句分為兩個部分：加鎖範圍子句和加鎖行為子句。下面我們分別針對兩個方面的進行介紹。

 

加鎖範圍子句

 

在select…for update之後，可以使用of子句選擇對select的特定資料表進行加鎖操作。預設情況下，不使用of子句表示在select所有的資料表中加鎖。

 

//採用預設格式for update

SQL> select * from emp where rownum<2 for update;

 

EMPNO ENAME     JOB        MGR HIREDATE         SAL     COMM DEPTNO

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

 7369 SMITH     CLERK     79021980-12-17    800.00              20

 

 

此時，我們觀察v$lock和v$locked_object視圖，可以看到鎖資訊。

//事務資訊視圖

SQL> select addr,xidusn,xidslot,xidsqn from v$transaction;

 

ADDR        XIDUSN   XIDSLOT    XIDSQN

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

377DB5D0         7        19       808

//鎖對象資訊

SQL> select xidusn,xidslot,xidsqn,object_id,session_id, oracle_username from v$locked_object;

 

   XIDUSN   XIDSLOT    XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME

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

        7        19       808     73181        36 SCOTT

//

SQL> select owner,object_name from dba_objects where object_id=73181;

 

OWNER                         OBJECT_NAME

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

SCOTT                         EMP

 

//

SQL> select addr, sid, type, id1,id2,lmode, request, block from v$lock where sid=36;

 

ADDR     SID TYPE       ID1       ID2     LMODE   REQUEST BLOCK

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

37E808F0   36 AE         100         0         4         0   0

B7DE8A44  36 TM       73181         0         3         0  0

377DB5D0  36 TX      458771       808         6         0   0

 

 

從上面的情況看，預設情況下的for update語句，效果相當於啟動了一個會話層級的事務，在對應的資料表（select所涉及的所有資料表）上加入一個資料表級共用鎖定（TM，lmode=3）。同時，在對應的資料行中加入獨佔鎖（TX，lmode=6）。

 

根據我們以前的知識，如果此時有另一個會話視圖擷取對應資料行的獨佔許可權（無論是用update/delete還是另一個for update），都會以block而告終。

 

SQL> select sid from v$mystat where rownum<2;

 

      SID

----------

       37

 

SQL> select * from emp where empno=7369 for update;

 

//系統blocking

 

此時系統中狀態，切換到另一個使用者下進行觀察：

 

SQL> select addr, sid, type, id1,id2,lmode, request, block from v$lock where sid in (36,37);

 

ADDR  SID TYPE       ID1       ID2     LMODE   REQUEST     BLOCK

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

37E808F0        36 AE         100         0         4         0  0

37E80ED4        37 AE         100         0         4         0  0

37E80F48        37 TX      458771       808         0         6  0

B7DE8A44        37 TM       73181         0         3         0 0

B7DE8A44        36 TM       73181         0         3         0 0

377DB5D0        36 TX      458771       808         6         0 1

 

6 rows selected

 

SQL> select * from dba_waiters;

 

WAITING_SESSION HOLDING_SESSION LOCK_TYPE                 MODE_HELD                               MODE_REQUESTED                            LOCK_ID1  LOCK_ID2

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

            37             36Transaction               Exclusive                               Exclusive                                   458771       808

 

 

由此，我們可以擷取到結論：for update子句的預設行為就是自動啟動一個事務，藉助事務的鎖機制將資料進行鎖定。

 

 

Of子句是配合for update語句使用的一個範圍說明標記。從官方的文法結構看，後面可以跟一個或者多個資料列列表。這種文法情境常常使用在進行串連查詢的select中，對其中一張資料表資料進行鎖定。

 

SQL> select empno,ename,job,mgr,sal from emp,dept where emp.deptno=dept.deptno and empno=7369 for update of emp.empno;

 

EMPNO ENAME     JOB        MGR      SAL

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

 7369 SMITH     CLERK     7902   800.00

 

SQL> select addr, sid, type, id1,id2,lmode, request, block from v$lock where sid=36;

 

ADDR      SID TYPE       ID1       ID2     LMODE   REQUEST BLOCK

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

37E808F0        36 AE         100         0         4         0   0

B7E1C2E8        36 TM       73181         0         3        0   0

377DBC0C        36 TX       65566       747         6      0  0

 

 

上面的語句中，我們看到使用for update of指定資料列之後，鎖定的範圍限制在了所在的資料表。也就是說，當我們使用串連查詢配合of子句的時候，可以實現有針對性的鎖定。

 

同樣在串連查詢的時候，如果沒有of子句，同樣採用預設的模式，會如何呢。

 

SQL> select empno,ename,job,mgr,sal from emp,dept where emp.deptno=dept.deptno and empno=7369 for update;

 

EMPNO ENAME     JOB        MGR      SAL

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

 7369 SMITH     CLERK     7902   800.00

 

SQL> select addr, sid, type, id1,id2,lmode, request, block from v$lock where sid=36;

 

ADDR    SID TYPE       ID1       ID2     LMODE   REQUEST BLOCK

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

37E808F0        36 AE         100         0         4         0    0

B7E1C2E8        36 TM       73179         0         3         0  0

B7E1C2E8        36 TM       73181         0         3         0    0

377DBC0C        36 TX      458777       805         6         0   0

 

SQL> select owner,object_name from dba_objects where object_id=73179;

 

OWNER                         OBJECT_NAME

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

SCOTT                         DEPT

 

 

明顯可以看到，當我們沒有使用of子句的時候，預設就是對所有select的資料表進行lock操作。

 

 

加鎖行為子句

 

加鎖行為子句相對比較容易理解。這裡分別介紹。

 

Nowait子句

 

當我們進行for update的操作時，與普通select存在很大不同。一般select是不需要考慮資料是否被鎖定，最多根據多版本一致讀的特性讀取之前的版本。加入for update之後，Oracle就要求啟動一個新事務，嘗試對資料進行加鎖。如果當前已經被加鎖，預設的行為必然是block等待。

 

使用nowait子句的作用就是避免進行等待，當發現請求加鎖資源被鎖定未釋放的時候，直接報錯返回。

///session1中

SQL> select * from emp for update;

 

EMPNO ENAME     JOB        MGR HIREDATE         SAL     COMM DEPTNO

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

 7369 SMITH     CLERK     79021980-12-17    800.00              20

 7499 ALLEN     SALESMAN  76981981-2-20    1600.00   300.00    30

 7521 WARD      SALESMAN  76981981-2-22    1250.00   500.00    30

 7566 JONES     MANAGER   78391981-4-2     2975.00              20

 

//變換session，進行執行。

SQL> select * from emp for update nowait;

 

select * from emp for update nowait

 

ORA-00054:資源正忙,但指定以NOWAIT方式擷取資源,或者逾時失效

 

 

對應的還有就是wait子句，也就是預設的for update行為。一旦發現對應資源被鎖定，就等待blocking，直到資源被釋放或者使用者強制終止命令。

 

 

對wait子句還存在一個資料參數位，表示當出現blocking等待的時候最多等待多長時間。單位是秒層級。

//接上面的案例

SQL> select * from emp for update wait 3;

 

select * from emp for update wait 3

 

ORA-30006:資源已被佔用;執行操作時出現WAIT逾時

 

 

 

Skip locked參數

 

Skip locked參數是最新引入到for update語句中的一個參數。簡單的說，就是在對資料行進行加鎖操作時，如果探索資料行被鎖定，就跳過處理。這樣for update就只針對未加鎖的資料行進行處理加鎖。

 

//session1中，對一部分資料加鎖；

SQL> select * from emp where rownum<4 for update;

 

EMPNO ENAME     JOB        MGR HIREDATE         SAL     COMM DEPTNO

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

 7369 SMITH     CLERK     79021980-12-17    800.00              20

 7499 ALLEN     SALESMAN  76981981-2-20    1600.00   300.00    30

 7521 WARD      SALESMAN  76981981-2-22    1250.00   500.00    30

 

//在session2中；

SQL> select * from emp for update skip locked;

 

EMPNO ENAME     JOB        MGR HIREDATE         SAL     COMM DEPTNO

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

（篇幅原因，省略）

7934 MILLER    CLERK     77821982-1-23    1300.00              10

 

11 rows selected

 

 

總資料一共14行。Session1中，先lock住了3行資料。之後的seesion2中，由於使用的skip locked子句參數，將剩下的11條資料進行讀取到並且加鎖。