显示在 Oracle 中锁定的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5172911/
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
Showing rows that are locked in Oracle
提问by Cwoo
Using Oracle, is it possible to indicate which rows are currently locked (and which are not) when performing a select statement (I don't want to lock any rows, just be able to display which are locked)?
使用 Oracle，是否可以在执行 select 语句时指示当前锁定哪些行（哪些未锁定）（我不想锁定任何行，只是能够显示哪些已锁定）？
For example, a pseudo column that would return the lock/transaction against the row:
SELECT lockname FROM emp;
例如，一个伪列将返回针对该行的锁/事务：
SELECT lockname FROM emp;
回答by Michael Broughton
One thing you could do is this - although it is not terribly efficient and so I wouldn't want to do use it for large data sets. Create a row-level function to try and lock the row. If it fails, then the row is already locked
您可以做的一件事是 - 尽管它的效率不是很高，所以我不想将它用于大型数据集。创建一个行级函数来尝试锁定该行。如果失败，则该行已被锁定
CREATE OR REPLACE FUNCTION is_row_locked (v_rowid ROWID, table_name VARCHAR2)
RETURN varchar2
IS
x NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'Begin
Select 1 into :x from '
|| table_name
|| ' where rowid =:v_rowid for update nowait;
Exception
When Others Then
:x:=null;
End;'
USING OUT x, v_rowid;
-- now release the lock if we got it.
ROLLBACK;
IF x = 1
THEN
RETURN 'N';
ELSIF x IS NULL
THEN
RETURN 'Y';
END IF;
END;
/
And then you could
然后你可以
Select field1, field2, is_row_locked(rowid, 'MYTABLE') from mytable;
It will work, but it isn't pretty nor efficient.
它会起作用，但它既不漂亮也不高效。
Indeed, it has exactly one redeeming quality - it will work even if you don't have select privs on the various v$ tables required in the linked document. If you have the privs, though, definitely go the other route.
事实上，它只有一种赎回质量——即使您在链接文档中所需的各种 v$ 表上没有选择权限，它也能工作。但是，如果您有特权，请务必走另一条路。
回答by a_horse_with_no_name
is it possible to indicate which rows are currently locked (and which are not) when performing a select statement
是否可以在执行 select 语句时指示当前锁定哪些行（哪些未锁定）
A SELECT statement will never lock any rows - unless you ask it to by using FOR UPDATE.
SELECT 语句永远不会锁定任何行 - 除非您使用 FOR UPDATE 要求它。
If you want to see locks that are held due to a SELECT ... FOR UPDATE (or a real update), you can query the v$lock system view.
如果您想查看由于 SELECT ... FOR UPDATE（或真正的更新）而持有的锁，您可以查询 v$lock 系统视图。
See the link that OMG Pony posted for an example on how to use that view.
有关如何使用该视图的示例，请参阅 OMG Pony 发布的链接。
回答by Jon Heller
I think @Michael Broughton's answer is the only way that will always work. This is because V$LOCK is not accurate 100% of the time.
我认为@Michael Broughton 的回答是唯一始终有效的方法。这是因为 V$LOCK 不是 100% 准确的。
Sessions don't wait for a row, they wait for the end of the transaction that modified that row. Most of the time those two concepts are the same thing, but not when you start using savepoints.
会话不等待一行，它们等待修改该行的事务结束。大多数情况下，这两个概念是相同的，但在您开始使用保存点时则不同。
For example:
例如：
- Session 1 creates a savepoint and modifies a row.
- Session 2 tries to modify that same row, but sees session 1 already has that row, and waits for session 1 to finish.
- Session 1 rolls back to the savepoint. This removes its entry from the ITL but does not end the transaction. Session 2 is still waiting on session 1. According to V$LOCK session 2 is still waiting on that row, but that's not really true because now session 3 can modify that row. (And if session 1 executes a commit or rollback, session 2 will wait on session 3.)
- 会话 1 创建一个保存点并修改一行。
- 会话 2 尝试修改同一行，但看到会话 1 已经拥有该行，并等待会话 1 完成。
- 会话 1 回滚到保存点。这会从 ITL 中删除其条目，但不会结束事务。会话 2 仍在等待会话 1。根据 V$LOCK 会话 2 仍在等待该行，但这不是真的，因为现在会话 3 可以修改该行。（如果会话 1 执行提交或回滚，会话 2 将等待会话 3。）
Sorry if that's confusing. You may want to step through the link provided by OMG Ponies, and then try it again with savepoints.
对不起，如果这令人困惑。您可能希望单步执行 OMG Ponies 提供的链接，然后使用保存点再试一次。
