如何在 Oracle SQL 中锁定单行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34775960/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I lock a single row in Oracle SQL
提问by Cr1ms0nStraY
It seems simple but I struggle with it. The question is how can I lock for example a single row from the table JOBSwith JOB_ID = IT_PROG. I want to do it, because I want to try an exception from a procedure, where it displays you a message when you try to update a locked row. Thanks in advance for your time.
这看起来很简单，但我很挣扎。问题是我如何锁定例如表JOBS 中的一行，其中JOB_ID = IT_PROG。我想这样做，因为我想尝试一个过程的异常，当您尝试更新锁定的行时，它会向您显示一条消息。在此先感谢您的时间。
回答by Marmite Bomber
You may lock the record as described in other answers, but you will not see any exceptionwhile UPDATEing this row.
您可以按照其他答案中的描述锁定记录，但在更新此行时您不会看到任何异常。
The UPDATEstatement will wait until the lock will be released, i.e. the session with SELECT ... FOR UPDATEcommits. After that the UPDATE will be performed.
该UPDATE语句将等待直到锁定被释放，即具有SELECT ... FOR UPDATE提交的会话。之后将执行更新。
The only exeption you can manage is DEADLOCK, i.e.
您可以管理的唯一例外是死锁，即
Session1 SELECT FOR UPDATE record A
Session2 SELECT FOR UPDATE record B
Session1 UPDATE record B --- wait as record locked
Session2 UPDATE record A --- deadlock as 1 is waiting on 2 and 2 waiting on 1
回答by zschallz
AskTom has an example of what you're trying to do:
AskTom 有一个你想要做的例子：
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4515126525609
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4515126525609
From AskTom:
来自 AskTom：
declare
resource_busy exception;
pragma exception_init( resource_busy, -54 );
success boolean := False;
begin
for i in 1 .. 3
loop
exit when (success);
begin
select xxx from yyy where .... for update NOWAIT;
success := true;
exception
when resource_busy then
dbms_lock.sleep(1);
end;
end loop;
if ( not success ) then
raise_application_error( -20001, 'row is locked by another session' );
end if;
end;
This attempts to get a lock, and if it can't get one (i.e. ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired is raised) it will raise an error.
这会尝试获取锁，如果无法获取锁（即 ORA-00054：资源繁忙并获取指定的 NOWAIT 或超时已引发），它将引发错误。
回答by Elza Christy Karunakaran
It's not possible to manually lock a row in Oracle. You can manually lock an object,though. Exclusive lock is placed on the row automatically when performing DML operations to ensure that no other sessions could update the same row or any other DDL operations could drop that row- other sessions can read it any time.
在 Oracle 中手动锁定一行是不可能的。不过，您可以手动锁定对象。执行 DML 操作时会自动在行上放置排他锁，以确保没有其他会话可以更新同一行，或者任何其他 DDL 操作都可能删除该行 - 其他会话可以随时读取它。
The first session to request the lock on the rows gets it and any other sessions requesting write access must wait.
If you don't want to be get locked ,that means , if you don't want to wait , you can use
请求锁定行的第一个会话获得它，任何其他请求写访问的会话都必须等待。
如果你不想被锁定，这意味着，如果你不想等待，你可以使用
Select .... For update ( nowait / wait(n) ) ( skiplocked)statement
Select .... For update ( nowait / wait(n) ) ( skiplocked)陈述
