SQL For Update Skip Locked Query and Java Multi Threading - 如何解决这个问题 [英] SQL For Update Skip Locked Query and Java Multi Threading - How to fix this

问题描述

SELECT 
        id
        FROM table_name tkn1,
        (SELECT 
            id, 
            ROWNUM rnum
         FROM table_name 
         WHERE 
            PROCS_DT is null
         order by PRTY desc, CRET_DT) result 
        WHERE  tkn1.id= result.id
        AND result.rnum <= 10 FOR UPDATE OF tkn1.id SKIP LOCKED

这是我的问题.2 个线程同时访问此查询

Here is my problem. 2 threads are accessing this query at the same time

线程 1 - 执行选择并锁定按优先级降序和创建日期排序的 10 行.接下来，我将从单独的查询中将 procs_dt 更新为今天的日期..

Thread 1 - Executes select and locks 10 rows ordered by descending priority and created date. Next I would update the procs_dt as todays date from a separate query..

线程 2 - 在线程 1 发生 procs_dt 更新或提交之前，该线程执行此查询.我的要求是必须将接下来的 10 个未锁定行移交给线程 2.但真正发生的是同一组锁定行来自内部查询，因为 procs_dt 仍然为空并且尚未被线程 1 更新，并且因为跳过在外部查询中给出了锁定，所有这 10 行都被跳过，并且线程 2 处理没有返回任何记录

Thread 2 - Before update of procs_dt or commit happens from thread 1 , this thread executes this query. My requirement is that the next 10 unlocked rows must be handed over to thread 2. But what really happens is the same set of locked rows comes out of the inner query since procs_dt is still null and yet to be updated by thread 1 and since skip locked is given in the outer query, all those 10 rows are skipped and no records are returned for thread 2 to process

这最终打败了我的多线程要求.

This ultimately defeats my multi threading requirement.

如何解决此查询?我尝试将跳过锁定添加到内部查询.但是 oracle 11g 不允许.

How to fix this query? I tried adding the skip locked to the inner query. But oracle 11g doesn allow it.

请高手帮忙.我正在使用 oracle 11g

Experts please help. I am using oracle 11g

推荐答案

我会选择这样的:用于选择行以进行更新的游标，并使用 LIMIT 子句获取前十个可用的行.

I'd go with something like this : A cursor to select the rows in order for update, and use the LIMIT clause to get the first ten available.

create table gm_temp
as select rownum id, table_name obj_name, date '2011-01-01' + rownum create_date 
from all_tables where rownum < 500;

CREATE TYPE tab_number IS TABLE OF NUMBER;

DECLARE
  cursor c_table IS 
    SELECT id FROM gm_temp ORDER BY create_date DESC FOR UPDATE OF id SKIP LOCKED;
  t_table_src tab_number := tab_number();
BEGIN
  OPEN c_table;
  FETCH c_table BULK COLLECT INTO t_table_src LIMIT 10;
  CLOSE c_table;
  dbms_output.put_line(':'||t_table_src.count||':'||t_table_src(1));
END;

实际上，我首先会看看将所有未完成的行作为一个集合处理是否比多线程更好.

Actually, I'd firstly see whether processing ALL outstanding rows as a set would be better than multi-threading.

如果我确实决定需要某种形式的多线程，我会考虑启用并行的流水线函数(假设我使用的是企业版).

Then if I did decide that I needed some form of multi-threading, I'd look at pipelined functions with parallel enabled (assuming I was on Enterprise Edition).

这篇关于SQL For Update Skip Locked Query and Java Multi Threading - 如何解决这个问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！