|
|
Re: table row lock question (merged) [message #434327 is a reply to message #434317] |
Tue, 08 December 2009 12:56   |
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 #434546 is a reply to message #434329] |
Wed, 09 December 2009 11:53  |
Frank
Messages: 7901 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.
|
|
|