Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Deadlock issue in while inserting in oracle table.

623104Nov 6 2010 — edited Nov 8 2010
I have written a procedure which copy data in to oracle tables.Other user can also start inserting in that table so to prevent these intervention.
i have applied locks on these two tables and assume that my procedure will wait for other users to finish and it will apply lock on the tables and other users will now wait till this procedure finishes.
My Procedure looks like:--


lock table table1 in exclusive mode;
lock table table2 in exclusive mode;

update table1 set where rownum < x;

forall .....insert in table 2.
...
...

..
commit;
EXCEPTION
WHEN OTHERS THEN
dbms_printline(SQLERRM)
commit;
END;


But my problem is i am still receiving oracle error as below and the above procedure fails in between:-
ORA-00060: deadlock detected while waiting for resource

Any hint ??


Can we get this error if i execute a SELECT statement on some table which is not locked by me and some other other is updating/deletig/inserting in that table ?
*Select statement is without update clause.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 6 2010
Added on Nov 6 2010
9 comments
1,555 views