Home » SQL & PL/SQL » SQL & PL/SQL » table row lock question (merged) (Oracle 6i reports)
table row lock question (merged) [message #434315] Tue, 08 December 2009 10:13 Go to next message
asmani
Messages: 47
Registered: February 2007
Member
Hi there

How do I check if an oracle row in a certain table is currently locked?


Many thanks!
Usman
Re: table row lock question (merged) [message #434317 is a reply to message #434315] Tue, 08 December 2009 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot unless you either
- dump the table blocks
- try to lock the table rows

Regards
Michel
Re: table row lock question (merged) [message #434327 is a reply to message #434317] Tue, 08 December 2009 12:56 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
you can get some ideas from this example...
set serveroutput on

DECLARE
   resource_busy_nowait   EXCEPTION;
   PRAGMA EXCEPTION_INIT (resource_busy_nowait, -00054);
BEGIN
   -- filter the list range of rows you are interested in examining here
   -- not the whole table, else other sessions wont be able to get
   -- locks they want...
   FOR i IN (SELECT ROWID, partno, part_desc FROM my_table where cust_id=123)
   LOOP
      BEGIN
         -- then check each individual record here
         FOR j IN (SELECT     1
                         FROM my_table
                        WHERE ROWID = i.ROWID
                   FOR UPDATE NOWAIT)
         LOOP
            NULL;
         END LOOP;
      EXCEPTION
         WHEN resource_busy_nowait
         THEN
           DBMS_OUTPUT.put_line('Locked rec: '|| RPAD(i.partno, 20, ' ')||i.part_desc);
      END;
   END LOOP;
   -- then release the locks
   ROLLBACK;
   DBMS_OUTPUT.put_line ('done');
END;
/

[Updated on: Wed, 09 December 2009 12:09]

Report message to a moderator

Re: table row lock question (merged) [message #434328 is a reply to message #434327] Tue, 08 December 2009 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course, if DML acitivity and table are big enough the result is wrong when the block completes. Laughing

Regards
Michel

[Updated on: Tue, 08 December 2009 13:00]

Report message to a moderator

Re: table row lock question (merged) [message #434329 is a reply to message #434328] Tue, 08 December 2009 13:08 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
yes, like with any code - an understanding of how the example works, and what the side effects may be need to be understood before just implementing it.
Re: table row lock question (merged) [message #434546 is a reply to message #434329] Wed, 09 December 2009 11:53 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Although I know it is just an example and you mean to show how this can be done and you even implied people should not use it straight away, I would like to add a warning for those not 100% familiar with Oracle:
Do not use this blindly in your production database, since it will put a lock on every row of your table. It can last quite a long time for large tables (which Michel hinted at already).
Besides, knowing which specific rows are locked is not very interesting either most of the time. I actually never ever needed to know that information in all my time I worked with Oracle.
Previous Topic: UPDATE question => updating with information from other table
Next Topic: extract pairs
Goto Forum:
  


Current Time: Wed Sep 28 21:11:25 CDT 2016

Total time taken to generate the page: 0.13864 seconds