SQL For Update跳过锁定查询和Java多线程 - 如何解决此问题 [英] 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为空
通过PRTY desc，CRET_DT命令结果
 WHERE tkn1.id = result.id 
 AND result.rnum <= 10 FOR UPDATE OF tkn1.id SKIP LOCKED 
  

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

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

线程2 - 更新procs_dt或提交发生在线程1之前，此线程执行此查询。我的要求是，接下来的10个未锁定的行必须切换到线程2.但是真正发生的是同一组锁定行来自内部查询，因为procs_dt仍然为空，并且还要由线程1更新，锁定在外部查询中，所有这10行被跳过，并且没有返回线程2的处理

这最终会打败我的多线程要求。 >

如何解决此问题？我试图添加skip锁定到内部查询。但是oracle 11g不允许它。

专家请帮忙。我使用oracle 11g

解决方案

我会这样做：
光标选择行

 创建表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; 
  

实际上，我首先看看是否将所有未完成的行作为一个集合，线程。

然后，如果我决定需要某种形式的多线程，我将看看流水线函数并行启用（假设我在企业版） 。

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

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

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..

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.

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

Experts please help. I am using oracle 11g

解决方案

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跳过锁定查询和Java多线程 - 如何解决此问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！