How to find locked rows in Oracle

Question

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

User · Answer

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

User · Answer

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

User · Answer

you can find the locked tables in oralce 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

User · Answer

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

User · Answer

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

User · Answer

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

[oracle] How to find locked rows in Oracle

Examples related to oracle

Examples related to locking