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

I believe you got the answer aready. We do the same thing in our application. If we can't get a lock we throw and exception. Fortunately, if we can't get the lock "now" we can wait until the next time we run the procedure.

We start of like this:
LOCK TABLE emp_rows_at_db_link IN EXCLUSIVE NODE NOWAIT;

If we can't ge a lock, the exception logs it in a logging table, and hopefully we do it the next run.

john_galt29_at_my-deja.com wrote:

> ... 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
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

--
Regards,
Greg Lechkun
gpl :-)

DTE

Energy====================================================================
DTE     ////// ////////////       Gregory Lechkun - EM&D/PDO-PT
DTE    //   //  //   //           Power Application Software Engineer
DTE   //   //  //   ////          lechkung_at_detroitedison.com