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

A copy of this was sent to john_galt29_at_my-deja.com (if that email address didn't require changing) On Thu, 21 Oct 1999 17:01:21 GMT, you wrote:

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

no, its not a deadlock.... a deadlock would be if I locked table A, you locked table B and then I tried to lock table B and you tried to lock table A.

>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 ...
>

tkyte_at_8i> lock table emp in exclusive mode nowait; lock table emp in exclusive mode nowait

           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

>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.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Oct 21 1999 - 13:20:28 CDT