How to find locked rows in Oracle

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.

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.

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.

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

When a row in Oracle gets 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.

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

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.

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.

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

    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;

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.

Look at the dba_blockers, dba_waiters and dba_locks for locking. The names should be self explanatory.

You could create a job that runs, say, once a minute and logged the values in the dba_blockers and the current active sql_id for that session. (via v$session and 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.

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

(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_SESSION and other related data dictionary views. Please try another approach before you run this abysmally slow code.)

First, let's create a sample table and some data. Run this in session #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.

--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.

--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.

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


A
-
1

Given some table, you can find which rows are not locked with 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".