Re: What Happens When You Try To Lock A Table That's Already Locked?

Actually, that's not a deadlock, but let's not get into that.

Depending on the version of Oracle, there are either views or tables you can query against that contain locking information. BUT, I think you don't need to worry about it if you do this:

LOCK TABLE oldtable IN EXCLUSIVE MODE;
CREATE TABLE newtable as SELECT * FROM oldtable; DELETE FROM oldtable;
COMMIT; There are options, such as NOWAIT that you may want to investigate.

>>

... I suppose the obvious answer here is DEADLOCK. My REAL question is -

How can I get Oracle to throw an exception if it tries to lock a table that is already locked? What I am doing is, when I have a certain number of records, locking the table, creating a new table, copying the records there and then nuking the records in the old table. Thus, during this process I want to have an EXCLUSIVE lock on the table so:

· No other process can lock the table
· No other process can add records to this table, until it has been emptied.

But after doing a simple test in 2 SQLPlus windows, I realized that if I tried to lock the same table twice it just hangs until I do a COMMIT for the first lock ...

That just doesn't help me ... I need to throw an exception so my app knows it's locked and sends an appropriate message.

Any takers?

Thanx.
John
<< Received on Thu Oct 21 1999 - 13:33:23 CDT