for update skip locked分析

        最近在工作中，用Spring的Quartz处理数据，由于数据量过大，就基于一个时间戳处理，但在部署时部署两个instance时，就出现了漏处理数据的问题。

        分析发现，取数过程是一个事务，由于数据量比较大，数据取完后就交给另个Service处理。进一步分析，取数前获得时间戳，数据查询后，再将记录的最后一条记录提时间戳更新回时间戳（当然，实现应用比这复杂），因为获取数据的查询语句加了for update skip locked，所以之前一直相信，即使是多个instance也不会出现漏处理数据。

        后来进一步分析，发现确实就是对for update skip locked理解有误导致的。

        测试过程如下：

create table bj_test(id number not null);
insert into bj_test values(1);
insert into bj_test values(2);
insert into bj_test values(3);
commit;

        实验一：连续两次执行同一个如下SQL语句：

select * from bj_test where rownum <= 1 for update skip locked;

        结果如下：

        实验二：再开启另一个SESSION窗口执行，却发现没有查询出任何数据。

        分析如下：

        因为同一个SESSION的锁定可以重复获取，所以实验一的第二条语句仍然能查询出同样的数据。而对于实验二的结果，我再做一个测试，即在另一个SESSION窗口执行：select * from bj_test for update skip locked;如下：

        可以发现后面两条记录并没有被第一个SESSION锁定。那为何 select * from t where rownum <= 1 for update skip locked; 这个就 no rows 呢？其实是因为 for update 执行情况我们误解了。对于存在  for update 的SQL语句，Oracle首先在没有for Update的情况下选取出满足条件的记录。然后对选取出的记录进行lock。如果后skip locked，那么在锁定记录的时候对于满足条件的记录中已经被锁定的将会再次被筛选掉。
        如下是for update skip locked 的执行过程：第一个SESSION 中锁定了第一条记录(Rownum <=1 ), 第二个SESSION 中依然按照 (Rownum<=1) 取到第一条ID=1的记录，在后继执行for update skip locked 的时候发现记录被锁定，则被筛选掉，那么最终结果就是no rows了。

        总结：skip locked 是在SQL查询结果集的基础上进行二次筛选。

        另外，增加一个知识点：使用了 For Update 查询的表上会放置一个MODE=3 的TM锁定，而不管该查询是否查询到数据。

 

解决问题及本文资料来源：http://www.itpub.net/thread-1060874-1-1.html