在《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/,如需转载,请注明出处,否则将追究法律责任。