Oracle谨慎使用嵌套查询作为锁机制条件

2019独角兽企业重金招聘Python工程师标准>>>

案例：Oracle 11g里写了一个存储过程，内容为执行update语句，更改表记录状态，目的是对表记录进行锁定。应用服务器集群调用同一个数据库的这个存储过程。最后发现，多个WebLogic实例都更新了同一条记录。锁机制失效。

进行代码测试：

数据初始化准备

create table tb_test_tab(
id number(10),
name varchar2(10),
statu varchar2(10)
);

truncate table tb_test_tab;
insert into tb_test_tab values (1001,'1','1');
insert into tb_test_tab values (1002,'2','1');
insert into tb_test_tab values (1003,'3','1');
insert into tb_test_tab values (1004,'4','1');
insert into tb_test_tab values (1005,'5','1');
insert into tb_test_tab values (1006,'6','1');
commit;

在plsql客户端开2个sql窗口，第一个窗口执行以下语句，不进行提交事务。

update  tb_test_tab t set t.name='343333',t.statu='2' where t.statu='1' and t.id=1001;

第二个窗口同时执行以下语句，提交事务。

update  tb_test_tab t set t.name='343333',t.statu='3' where t.statu='1' and t.id=1001;
commit;

此时第二个语句的commit处于等待状态，现在提交第一个窗口的事务，第二个事务也会立即提交。查看结果发现 t.id=1001记录的 t.statu值为2。说明表的锁机制成功！

现在换成嵌套语句作为更新条件进行执行更新处理，看下锁机制如何，操作步骤如下：

先初始化数据。

truncate table tb_test_tab;
insert into tb_test_tab values (1001,'1','1');
insert into tb_test_tab values (1002,'2','1');
insert into tb_test_tab values (1003,'3','1');
insert into tb_test_tab values (1004,'4','1');
insert into tb_test_tab values (1005,'5','1');
insert into tb_test_tab values (1006,'6','1');
commit;

同样在plsql客户端开2个sql窗口，第一个窗口执行以下语句，不进行提交事务。语句含义就是将statu='1'的记录升序后的前三条设置statu='2'，name='22222'。

update tb_test_tab t
   set t.name = '22222', t.statu = '2'
 where t.id in (select id
                  from (select r.id from tb_test_tab r where r.statu='1' order by r.id)
                 where rownum <= 3);

第二个窗口同时执行以下语句，提交事务。

update tb_test_tab t
   set t.name = '33333', t.statu = '3'
 where t.id in (select id
                  from (select r.id from tb_test_tab r where r.statu='1' order by r.id)
                 where rownum <= 3);
commit;

此时第二个语句的commit处于等待状态，现在提交第一个窗口的事务，第二个事务也会立即提交。查看结果发现id升序后的前三条记录的 statu='3'，name='33333'。 说明表的锁机制存在异常，至少不是之前我们想象的那样的结果（之前想象的结果应该是 statu='2'，name='22222'。 第二个窗口执行的语句更新记录为0条）！  这个也许就是Oracle内部锁机制灵敏度不针对嵌套语句里面的结果集。故需要谨慎使用嵌套语句作为锁机制的限制条件。

如果想上面嵌套语句不变，同时想让锁机制起到效果，可以将嵌套语句里面的限制条件在最外层再写一遍即可。具体如下。第一个窗口改成：

update tb_test_tab t
   set t.name = '22222', t.statu = '2'
 where t.id in (select id
                  from (select r.id from tb_test_tab r where r.statu='1' order by r.id)
                 where rownum <= 3)
    and t.statu='1';

第二个窗口改成：​

update tb_test_tab t
   set t.name = '33333', t.statu = '3'
 where t.id in (select id
                  from (select r.id from tb_test_tab r where r.statu='1' order by r.id)
                 where rownum <= 3)
    and t.statu='1';
commit;

​

转载于:https://my.oschina.net/u/163737/blog/710153