如何在 Oracle 中查找锁定的行

How to find locked rows in Oracle

提问by ehymel

We have an Oracle database, and the customer account table has about a million rows. Over the years, we've built four different UIs (two in Oracle Forms, two in .Net), all of which remain in use. We have a number of background tasks (both persistent and scheduled) as well.

我们有一个 Oracle 数据库，客户帐户表有大约一百万行。多年来，我们构建了四种不同的 UI（两个在 Oracle Forms 中，两个在 .Net 中），所有这些都仍在使用中。我们还有许多后台任务（持久的和计划的）。

Something is occasionally holding a long lock (say, more than 30 seconds) on a row in the account table, which causes one of the persistent background tasks to fail. The background task in question restarts itself once the update times out. We find out about it a few minutes after it happens, but by then the lock has been released.

有些东西偶尔会在 account 表中的一行上持有一个长锁（比如，超过 30 秒），这会导致持久性后台任务之一失败。一旦更新超时，有问题的后台任务会自行重新启动。我们在它发生几分钟后才发现它，但到那时锁已经被释放了。

We have reason to believe that it might be a misbehaving UI, but haven't been able to find a "smoking gun".

我们有理由相信这可能是一个行为不端的用户界面，但一直没能找到“冒烟的枪”。

I've found some queries that list blocks, but that's for when you've got two jobs contending for a row. I want to know which rows have locks when there's not necessarily a second job trying to get a lock.

我发现了一些列出块的查询，但这是当你有两个工作竞争连续的时候。当不一定有第二份工作试图获得锁时，我想知道哪些行有锁。

We're on 11g, but have been experiencing the problem since 8i.

我们在 11g 上，但从 8i 开始就遇到了这个问题。

采纳答案by Quassnoi

Oracle's locking concept is quite different from that of the other systems.

Oracle的锁定概念与其他系统的锁定概念大不相同。

When a row in Oraclegets locked, the record itself is updated with the new value (if any) and, in addition, a lock (which is essentially a pointer to transaction lock that resides in the rollback segment) is placed right into the record.

当一行Oracle被锁定时，记录本身会用新值（如果有）更新，此外，一个锁（它本质上是一个指向位于回滚段中的事务锁的指针）被放置在记录中。

This means that locking a record in Oraclemeans updating the record's metadata and issuing a logical page write. For instance, you cannot do SELECT FOR UPDATEon a read only tablespace.

这意味着锁定记录Oracle意味着更新记录的元数据并发出逻辑页面写入。例如，您不能SELECT FOR UPDATE在只读表空间上执行操作。

More than that, the records themselves are not updated after commit: instead, the rollback segment is updated.

不仅如此，提交后记录本身不会更新：而是更新回滚段。

This means that each record holds some information about the transaction that last updated it, even if the transaction itself has long since died. To find out if the transaction is alive or not (and, hence, if the record is alive or not), it is required to visit the rollback segment.

这意味着每条记录都包含有关上次更新它的事务的一些信息，即使事务本身早已死亡。要确定事务是否处于活动状态（因此，记录是否处于活动状态），需要访问回滚段。

Oracle does not have a traditional lock manager, and this means that obtaining a list of all locks requires scanning all records in all objects. This would take too long.

Oracle 没有传统的锁管理器，这意味着获取所有锁的列表需要扫描所有对象中的所有记录。这将花费太长时间。

You can obtain some special locks, like locked metadata objects (using v$locked_object), lock waits (using v$session) etc, but not the list of all locks on all objects in the database.

您可以获得一些特殊的锁，例如锁定的元数据对象（使用v$locked_object）、锁等待（使用v$session）等，但不能获得数据库中所有对象的所有锁的列表。

回答by Gary Myers

Rather than locks, I suggest you look at long-running transactions, using v$transaction. From there you can join to v$session, which should give you an idea about the UI (try the program and machine columns) as well as the user.

我建议您查看长时间运行的事务，而不是锁，使用v$transaction. 从那里您可以加入v$session，这应该让您了解 UI（尝试程序和机器列）以及用户。

回答by PenFold

Look at the dba_blockers, dba_waitersand dba_locksfor locking. The names should be self explanatory.

查看dba_blockers,dba_waiters和dba_locks用于锁定。这些名称应该是不言自明的。

You could create a job that runs, say, once a minute and logged the values in the dba_blockersand the current active sql_idfor that session. (via v$sessionand v$sqlstats).

您可以创建一个作业，例如，每分钟运行一次，并记录该会话的dba_blockers和当前活动中的值sql_id。（通过v$session和v$sqlstats）。

You may also want to look in v$sql_monitor. This will be default log all SQL that takes longer than 5 seconds. It is also visible on the "SQL Monitoring" page in Enterprise Manager.

您可能还想查看v$sql_monitor. 这将默认记录所有耗时超过 5 秒的 SQL。它也可以在企业管理器的“SQL 监控”页面上看到。

回答by Baji Shaik

you can find the locked tables in oralce by querying with following query

您可以通过以下查询在oralce中找到锁定的表

    select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

回答by Jon Heller

The below PL/SQL block finds all locked rows in a table. The other answers only find the blocking session, finding the actual locked rowsrequires reading and testing each row.

下面的 PL/SQL 块查找表中所有锁定的行。其他答案只找到阻塞会话，找到实际锁定的行需要读取和测试每一行。

(However, you probably do not need to run this code. If you're having a locking problem, it's usually easier to find the culprit using GV$SESSION.BLOCKING_SESSIONand other related data dictionary views. Please try another approach before you run this abysmally slow code.)

（但是，您可能不需要运行此代码。如果您遇到锁定问题，使用GV$SESSION.BLOCKING_SESSION和其他相关数据字典视图通常更容易找到罪魁祸首。请在运行此极其缓慢的代码之前尝试另一种方法。）

First, let's create a sample table and some data. Run this in session #1.

首先，让我们创建一个示例表和一些数据。在会话 #1 中运行它。

--Sample schema.
create table test_locking(a number);
insert into test_locking values(1);
insert into test_locking values(2);
commit;
update test_locking set a = a+1 where a = 1;

In session #2, create a table to hold the locked ROWIDs.

在会话 #2 中，创建一个表来保存锁定的 ROWID。

--Create table to hold locked ROWIDs.
create table locked_rowids(the_rowid rowid);
--Remove old rows if table is already created:
--delete from locked_rowids;
--commit;

In session #2, run this PL/SQL block to read the entire table, probe each row, and store the locked ROWIDs. Be warned, this may be ridiculously slow. In your real version of this query, change both references to TEST_LOCKING to your own table.

在会话 #2 中，运行这个 PL/SQL 块来读取整个表，探测每一行，并存储锁定的 ROWID。请注意，这可能会慢得离谱。在此查询的真实版本中，将对 TEST_LOCKING 的两个引用更改为您自己的表。

--Save all locked ROWIDs from a table.
--WARNING: This PL/SQL block will be slow and will temporarily lock rows.
--You probably don't need this information - it's usually good enough to know
--what other sessions are locking a statement, which you can find in
--GV$SESSION.BLOCKING_SESSION.
declare
    v_resource_busy exception;
    pragma exception_init(v_resource_busy, -00054);
    v_throwaway number;
    type rowid_nt is table of rowid;
    v_rowids rowid_nt := rowid_nt();
begin
    --Loop through all the rows in the table.
    for all_rows in
    (
        select rowid
        from test_locking
    ) loop
        --Try to look each row.
        begin
            select 1
            into v_throwaway
            from test_locking
            where rowid = all_rows.rowid
            for update nowait;
        --If it doesn't lock, then record the ROWID.
        exception when v_resource_busy then
            v_rowids.extend;
            v_rowids(v_rowids.count) := all_rows.rowid;
        end;

        rollback;
    end loop;

    --Display count:
    dbms_output.put_line('Rows locked: '||v_rowids.count);

    --Save all the ROWIDs.
    --(Row-by-row because ROWID type is weird and doesn't work in types.)
    for i in 1 .. v_rowids.count loop
        insert into locked_rowids values(v_rowids(i));
    end loop;
    commit;
end;
/

Finally, we can view the locked rows by joining to the LOCKED_ROWIDS table.

最后，我们可以通过连接到 LOCKED_ROWIDS 表来查看锁定的行。

--Display locked rows.
select *
from test_locking
where rowid in (select the_rowid from locked_rowids);


A
-
1

回答by Danilo Piazzalunga

Given some table, you can find which rows are notlocked with SELECT FOR UPDATESKIP LOCKED.

给定一些表，您可以找到哪些行没有被锁定SELECT FOR UPDATESKIP LOCKED。

For example, this query will lock (and return) every unlocked row:

例如，此查询将锁定（并返回）每个未锁定的行：

SELECT * FROM mytable FOR UPDATE SKIP LOCKED

References

参考

• Ask TOM "How to get ROWID for locked rows in oracle".

• 问 TOM“如何在 oracle 中获取锁定行的 ROWID”。