在《Select For update語句淺析》（http://space.itpub.net/17203031/viewspace-694383）中，筆者詳細介紹了for update字句的用法。一個朋友在做實驗的時候，說出現一些和文章中結果不同的現象。仔細研究下，還真發現不少玄妙。特此記錄下來。（感謝xhl同學）

問題

在文章中，for update的方式會將select後面所涉及到的資料表全部加以鎖定。筆者實驗的環境中，也的確如此。

//在oracle11g下的實驗

SQL> conn scott/tiger@ora11g;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

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

SID

----------

44

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=44;

ADDR SID TYPE ID1 ID2 LMODE REQUEST BLOCK

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

51A80764 44 AE 100 0 4 0 0

B6CA8AB8 44 TM 73179 0 3 0 0

B6CA8AB8 44 TM 73181 0 3 0 0

50ABF340 44 TX 196629 2924 6 0 0

我們發現同文章中的情況和結果相同。同時我們觀察下執行計劃。

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

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1873239076

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

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

| 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 0

| 1 | FOR UPDATE | | | | |

| 2 | NESTED LOOPS | | 1 | 32 | 1 (0)| 0

| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 29 | 1 (0)| 0

|* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 0

|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 4 | 12 | 0 (0)| 0

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

Predicate Information (identified by operation id):

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

4 - access("EMPNO"=7369)

5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

18 rows selected

當相同的語句相同的環境，我們轉移到oracle10g下，也就是朋友實驗的環境下，情況就有所差異了。

//在oracle10g環境下

SQL> conn scott/tiger@otstest;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

SQL> select * from v$version where rownum<2;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

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

SID

----------

131

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 700.00

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

ADDR SID TYPE ID1 ID2 LMODE REQUEST BLOCK

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

27F3F1D8 131 TM 51151 0 3 0 0

27FA4BB8 131 TX 393219 23597 6 0 0

和我們預想的差異出現了，在oracle10g下，同樣的語句只會對一個資料表進行加鎖。那麼產生這種差異的原因是什麼呢？我們同樣觀察一下執行計劃。

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

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 222764943

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

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

| 0 | SELECT STATEMENT | | 1 | 29 | 1 (0)| 00:

| 1 | FOR UPDATE | | | | |

|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 29 | 1 (0)| 00:

|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:

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

Predicate Information (identified by operation id):

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

2 - filter("EMP"."DEPTNO" IS NOT NULL)

3 - access("EMPNO"=7369)

16 rows selected

從執行計劃上看，執行計劃沒有涉及到資料表DEPT。但是SQL語句中有包括DEPT的部分，看來for update沒有對dept表的鎖定原因是和執行計劃有關。

那麼，仔細研究那句SQL語句就可以發現，雖然這個SQL中包括了Dept資料表的內容。但是無論是條件where字句後，還是顯示欄位列表中，都沒有該資料表的列資訊內容。這個也許就是出現奇怪現象的原因。

Oracle10g在生成執行計劃的時候，發現該SQL中雖然包括dept資料表，但是沒有實際的含義，所以進行最佳化改寫的時候，就將dept剔除出了執行計劃。而oracle11g顯然不認可這種方式的改寫，將dept重新納入了執行計劃。

如果我們加入dept欄位在where或者列表中，那麼是不是就會實現dept兩個資料表的鎖定了？下面分別在oracle11g和10g上進行試驗。

//Oracle10g情況

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

（篇幅原因，省略）

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

ADDR SID TYPE ID1 ID2 LMODE REQUEST BLOCK

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

28434394 131 TO 8706 1 3 0 0

27F3F1D8 131 TM 51149 0 3 0 0

27F3F29C 131 TM 51151 0 3 0 0

27FA4BB8 131 TX 393261 23597 6 0 0

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

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1873239076

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

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

| 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 0

| 1 | FOR UPDATE | | | | |

| 2 | NESTED LOOPS | | 1 | 32 | 1 (0)| 0

|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 29 | 1 (0)| 0

|* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 0

|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 4 | 12 | 0 (0)| 0

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

Predicate Information (identified by operation id):

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

3 - filter("EMP"."DEPTNO" IS NOT NULL)

4 - access("EMPNO"=7369)

5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

19 rows selected

在11g的情況下，同樣會進行兩個資料表的鎖定，執行計劃如下：

//Oracle 11g的情況

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1873239076

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

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

| 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 0

| 1 | FOR UPDATE | | | | |

| 2 | NESTED LOOPS | | 1 | 32 | 1 (0)| 0

| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 29 | 1 (0)| 0

|* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 0

|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 4 | 12 | 0 (0)| 0

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

Predicate Information (identified by operation id):

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

4 - access("EMPNO"=7369)

5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

18 rows selected

當我們將dept相應的內容加入到select或者where之後，dept就會出現在執行計劃中。執行計劃中的for update操作，就會實現將資料表dept的鎖定。

這個案例，告訴我們下面幾個方面問題：

ü 從執行計劃角度看，for update鎖定的範圍一定是for update操作所涉及的所有資料表。如果執行計劃中for update下沒有資料表，就一定不會被鎖定；

ü 不同版本的Oracle最佳化器，在生成執行計劃時差異很大，這種差異可能造成一些時候的困擾；

ü Oracle會對輸入的SQL進行一定程度改寫，根據一些即定義好的規則進行改寫。有時候會將一些內容加以剔除，間接影響到執行計劃了；

For Update操作分析——不同Oracle版本之間的差異研究

相關文章