Oracle SELECT FOR UPDATE-演示? [英] Oracle SELECT FOR UPDATE - Demonstration?

问题描述

我不太了解SELECT FOR UPDATE的锁定功能.

I am quite not understanding the lock functionality with SELECT FOR UPDATE.

这是我尝试过的.

CREATE TABLE ACCOUNT_TAB (
  ACC_ID int NOT NULL PRIMARY KEY,
  BALANCE int NOT NULL
);

INSERT INTO ACCOUNT_TAB
VALUES(1, 100);

SELECT * FROM ACCOUNT_TAB FOR UPDATE; 
SELECT * FROM ACCOUNT_TAB; 

两个SELECT都将检索该行，但是第一个查询是否不应在ACCOUNT_TAB表中锁定该行?

Both SELECT will retrieve the row, but shouldn't the first query lock the row in the ACCOUNT_TAB table?

我已经阅读了一些有关会话的内容:来自同一会话的查询并不关心锁.我可以在一个脚本文件中以某种方式演示锁定功能吗?例如，是否可以在一个脚本中运行两个不同的会话，以便第二个调用将检索到一个错误，指出该行已锁定?

I have read something about sessions: queries from the same session don't care about the lock. Can I somehow in a single script file demonstrate the lock function? For example, can I run two different sessions in one script so the second call will retrieve an error saying that the row is locked?

推荐答案

您的原始实验未能证明锁定，因为在Oracle中，写操作不会阻止读操作.FOR UPDATE子句使我们避免了两个会话尝试写入到同一记录的情况；任何数量的会话都可以读取一条记录.

Your original experiment failed to demonstrate the locking because in Oracle writes don't block reads. The FOR UPDATE clause allows us to avoid situations in which two sessions attempt to write to the same record; any number of sessions can read a record.

好的，但是，有什么方法可以在单个脚本文件中演示该锁吗?"

"Okay but still, is there some way to demonstrate the lock in a single script file?"

是的.这是带有本地过程的脚本，该脚本使用 autonomous_transaction 编译指示来模拟多用户环境:

Yes. Here is a script with a local procedure which uses the autonomous_transaction pragma to simulate a multi-user environment:

declare
    procedure p1 (p_id in number) is
        pragma autonomous_transaction;
        cursor c23  is
            select * from t23
            where id = p_id
            for update nowait;
        r23 c23%rowtype;
    begin
        dbms_output.put_line('nested transaction');
        open c23;
        fetch c23 into r23;
        update t23 
        set col2 = col2 * 2;
        close c23;
        commit;
    exception
        when others then
            dbms_output.put_line(sqlerrm);
    end;

begin
    update t23
    set col1 = 2
    where id = 1;

    p1 (1);

    commit;
end;
/

第一个UPDATE语句发出一个锁，这导致该过程调用失败，因为它无法获得锁(由于使用了NOWAIT子句):

The first UPDATE statement issues a lock, which causes the procedural call to fail because it can't get a lock (due to use of NOWAIT clause):

  ...
  30  end;
  31  /
nested transaction
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

PL/SQL procedure successfully completed.

SQL> 

这篇关于Oracle SELECT FOR UPDATE-演示?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！