在《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

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

| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| T

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

|   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

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

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Tim

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

|   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

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

| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| T

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

|   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

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

| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| T

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

|   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进行一定程度改写，根据一些即定义好的规则进行改写。有时候会将一些内容加以剔除，间接影响到执行计划了；

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-694555/，如需转载，请注明出处，否则将追究法律责任。