代码之家 › 专栏 › 技术社区 › Nicolas Göddel

更新某行的列并返回其id

locking oracle

Nicolas Göddel · 技术社区 · 6 年前

我需要你的建议来解决我的小问题。我得到了一个表,它由一些作业组成,这些作业带有一个ID、一个时间戳、一个状态和一些其他值,这些值定义了作业的实际内容。我需要找到时间戳最小的ID,其中STATE=1,并且在相同的原子操作中必须将STATE设置为2。

SELECT * FROM SW_ASYNC_JOBS WHERE STATE = 1 ORDER BY TIMESTAMP FETCH FIRST 1 ROW ONLY

我们将ID存储到一个变量中,比如JOB\u ID,然后将其状态设置为2:

UPDATE SW_ASYNC_JOBS SET STATE = 2 WHERE JOB_ID = :JOB_ID

现在客户机拥有了所需的所有数据,将状态设置为“in progress”并开始处理作业。

当然在现实中,在这两个操作之间会有另一个客户端执行相同的操作,并且肯定会出现竞争条件。两个客户可能在同一个工作,这是致命的。

DECLARE
    CURSOR FRESH_JOB IS
        SELECT * FROM SW_ASYNC_JOBS WHERE STATE = 1 ORDER BY TIMESTAMP FETCH FIRST 1 ROW ONLY
    FOR UPDATE;
BEGIN
    FOR JOB IN FRESH_JOB LOOP
        UPDATE SW_ASYNC_JOBS SET STATE = 2 WHERE CURRENT OF FRESH_JOB;
    END LOOP;
END;

但不知怎的我错了 ORA-02014: cannot select FOR UPDATE from view 这很奇怪,因为SW\u ASYNC\u JOBS表是一个简单的表,主键位于两列之上。

解决这个问题最好的办法是什么?我是否应该锁定整个表以获取最旧的作业并更改其状态?

为了完整起见,这是我要说的表格:

CREATE TABLE SW_ASYNC_JOBS (
    "MASTER_JOB_ID" NUMBER(22, 0) NOT NULL,
    "JOB_ID" NUMBER(22, 0) NOT NULL,
    "USER_ID" VARCHAR2(64) NOT NULL,
    "UID" VARCHAR2(64) NOT NULL,
    "VIEW" VARCHAR2(64) NOT NULL,
    "JSON" VARCHAR2(2000) NOT NULL,
    "TIMESTAMP" TIMESTAMP NOT NULL,
    "STATE" NUMBER(2, 0) NOT NULL,
    CONSTRAINT "SW_ASYNC_PRIMARY" PRIMARY KEY ("MASTER_JOB_ID", "JOB_ID")
)

还有其他客户机从一个序列中检索新的序列号,以便向该表中添加新行。首先检索主作业ID,然后为每个“从属作业”使用另一个新的序列号。所以原则上没有数字 MASTER_JOB_ID 和 JOB_ID 可能发生两次。这个 主作业ID 只有结合几个“奴隶工作”,并显示他们的地位组明智。

客户机是一个Python脚本,它使用 cx_Oracle 包的版本为12.1.0.2.0。

2 回复 | 直到 6 年前

Littlefoot 6 年前

也许我不明白这个问题,但是-为什么 SELECT UPDATE ? 这样的声明不行吗? RETURNING

declare
  retval sw_async_jobs.job_id%type;
begin
  update sw_async_jobs s set
    s.state = 2
    where s.job_id = (select s1.job_id
                      from sw_async_jobs s1
                      where s1.state = 1
                      order by s1.timestamp fetch first 1 row only
                     )
  returning job_id into retval;

  dbms_output.put_line('retval = ' || retval);
end;
/

Nicolas Göddel 6 年前

我想我找到了另一个可能非传统的解决方法。

def getNextJobId(self) :
    """ Return Id of the oldest job which is in 'ready' state
    """

    # Lock the table
    cursor = self._execute("LOCK TABLE %s IN EXCLUSIVE MODE" % self._jobTableName)

    try :
        jobId = None

        while jobId is None :

            # Find potential job Id in 'ready' state
            cursor = self._execute("SELECT JOB_ID FROM %s WHERE STATE = :READY_STATE ORDER BY TIMESTAMP, JOB_ID FETCH FIRST 1 ROW ONLY" % self._jobTableName,
                                   {'READY_STATE' : JobStates.ready.value})

            rows = list(cursor)

            # Is there is no such job, quit
            if len(rows) == 0 :
                self._rollback()
                break

            # If there is a potential job, save its Id
            elif len(rows) >= 1 :
                # 'rows' has this form: [(int,)]
                potentialJobId = rows[0][0]

            # Now we change its state. In the WHERE clause we also check if the job still is in 'ready' state.
            statement = "UPDATE %s SET STATE = :WORKING_STATE WHERE JOB_ID = :JOB_ID AND STATE = :READY_STATE" % self._jobTableName

            cursor = self._execute(statement, {'READY_STATE' : JobStates.ready.value,
                                               'WORKING_STATE' : JobStates.working.value,
                                               'JOB_ID' : potentialJobId})

            # If there was exactly one row changed, we now have the job
            if cursor.rowcount == 1 :
                jobId = potentialJobId
                self._commit()

            # If nothing was changed through the UPDATE, another client got the same potential job and changed the state already. Try again.

        return jobId

    finally :
        self._rollback()

UPDATE 是原子的,现在应该可以正常工作了。一开始我对避免循环感兴趣,但当我和一个男人聊天的时候 #oracle-database 在Freenode上,当以另一种方式尝试并且没有循环时,看起来会有竞争条件。

我不想把这个解决方案标记为这个问题的解决方案。也许其他人对Python和Oracle有更好的想法。