oracle 游标 并发,Oracle10.2并发条件下更新游标数据的研究

本文测试在pl/sql编程中，更新游标数据的2种方式以及并发条件下各种方式的实际表现。2种方式的效率问题不在此文讨论之列！

一、环境准备

数据库：Oracle10.2.0.4

测试工具：PL/SQL Developer9

二、数据准备

我们使用oracle自带的演示用户scott登录数据库，为清楚看到数据变化，执行以下语句：

SQL> update emp t set t.sal=1000;

14 rows updated

SQL> commit;

Commit complete

Emp表的sal字段初始化为1000

查询emp表：

SELECTt.empno, t.ename,t.job, t.sal,ROWIDrdfromemp t;

结果如图1所示：

图1

Sal字段已经全部更新为1000

三、创建存储过程ps_cursor_for_update

CREATEORREPLACEPROCEDUREps_cursor_for_update

(p_salPLS_INTEGER)IS

CURSORcIS

SELECTt.empno, t.ename,

t.hiredate,ROWIDrd

FROMEMP t

WHEREt.sal =1000

FORUPDATE;

v_emp_record c%ROWTYPE;

v_rowsPLS_INTEGER:=0;

BEGIN

OPENc;

LOOP

FETCHc

INTOv_emp_record;

EXITWHENc%NOTFOUND;

UPDATEEMPSETsal = p_salWHERECURRENTOFc;

v_rows :=SQL%ROWCOUNT;

dbms_output.put_line(v_rows);

ENDLOOP;

COMMIT;

CLOSEc;

ENDps_cursor_for_update;

四、测试ps_cursor_for_update

PL/SQL Developer工具具有很强大的plsql调试功能，我们使用两个test窗口进行模拟并发执行的情况

在编辑存储过程的界面，在打开游标的代码行加入一个断点：

图2

在存储过程ps_cursor_for_update上点击右键，打开两个test窗口：

窗口1中，输入参数填写2000

窗口2中，输入参数填写3000，如图3、4所示：

图3

图4

两个窗口分别点击start bugger按钮，开始调试，并点击run按钮，分别运行到打开游标的一行，并在窗口2中进行单步调试，运行到如图5所示位置：

图5

此时窗口1中，开始单步调试，发现状态栏处于运行中(图7)，但调试光标始终停在断点行(图6)

图6

图7

说明游标打开的数据已经被窗口2的进程锁定，所以窗口1的进程无法打开数据

下面把窗口2的断点去掉，并点击run按钮使此过程执行完毕，可以发现，此时窗口1中，代码已经执行到了原断点位置的下一行：

图8

结论：窗口2执行了commit语句，PL/SQL过程结束，并解锁操作的数据，使窗口1的过程得以打开游标。

查询emp表的数据：

图9

发现sal字段已经更新为3000

注意，打开游标的sql条件中，sal字段是1000，所以，继续运行窗口1，完成PL/SQL过程，再次查看emp数据：

图10

发现sql字段还是3000；

结论：由于窗口2锁定了emp数据，并将sal字段更新为3000，使得窗口1在窗口2执行完毕而打开游标时，sal为1000的数据已经不存在了，所以窗口1的PL/SQL过程，没有打开任何数据，也就没有更新任何数据。

由此可知，在打开游标的select语句中，使用for update子句，能在并发条件下有效地保证数据的正确。

五、创建存储过程ps_cursor_no_for_update

CREATEORREPLACEPROCEDUREps_cursor_no_for_update(p_salPLS_INTEGER)IS

CURSORcIS

SELECTt.empno, t.ename,

t.hiredate,ROWIDrd

FROMEMP t

WHEREt.sal =1000;

v_emp_record c%ROWTYPE;

v_rowsPLS_INTEGER:=0;

BEGIN

OPENc;

LOOP

FETCHc

INTOv_emp_record;

EXITWHENc%NOTFOUND;

UPDATEEMPSETsal = p_salWHEREROWID= v_emp_record.rd;

v_rows :=SQL%ROWCOUNT;

dbms_output.put_line(v_rows);

ENDLOOP;

COMMIT;

CLOSEc;

ENDps_cursor_no_for_update;

此过程游标的查询条件仍未sal=1000，而打开游标的select语句，没有for update子句，游标数据使用rowid作为唯一标识作更新操作

六、测试ps_cursor_no_for_update

先将emp表的sal字段初始化为1000

SQL> update emp t set t.sal=1000;

14 rows updated

SQL> commit;

Commit complete

Emp表的sal字段初始化为1000

查询emp表：

SELECTt.empno, t.ename,t.job, t.sal,ROWIDrdfromemp t;

结果如下：

图11

Sal字段已经全部更新为1000

编辑存储过程ps_cursor_no_for_update的界面中，设置断点如图12所示：

图12

分别打开2个test窗口，设置输入参数为2000和3000；

窗口1和窗口2分别启动断点调试，并点击run按钮运行，可发现两个窗口都运行到了断点所在位置：

图13

这时，我们在窗口1中单步调试，运行到输出语句一行：

图14

Ok，窗口1停在这里，在窗口2进行相同的动作，发现窗口2的光标仍停在update语句一行，也就是断点所在行；

结论：PL/SQL过程中，打开游标时未使用for update子句时，如果两个进程同时调用该过程，则游标可以同时打开，但在第一个update语句执行后，其它进程则进入等待状态。

好，我们继续将窗口1的过程执行完毕，然后查询emp的数据：

图15

数据已经成功更新为2000；

再看一下窗口2，调试的光标已经移动到了下一行：

图16

说明窗口1的过程执行完毕，被update语句锁住的数据已经解锁，所以窗口的过程可以继续执行了；

将窗口2的过程执行结束，再次查看emp数据：

图17

Sal字段已经更新为3000，说明窗口2的PL/SQL过程更新成功了

七、结论

结论：在PL/SQL过程中打开游标，在未使用for update子句的条件下，多个进程更新相同数据时，可能会出现数据的不一致性，所以在具体实施过程中，要根据具体需求来判断，是否需要使用for update子句。