Home » SQL & PL/SQL » SQL & PL/SQL » Deadlock by deletion
Deadlock by deletion [message #237374] Mon, 14 May 2007 05:18 Go to next message
liron
Messages: 3
Registered: May 2007
Junior Member
Hy experts,

I have 2 threads that does the same thing:
delete from my_table where x=1; (delete more than one row)
commit;

can this scenario cause in any way to oracle deadlock?

thanks,
Liron.

Re: Deadlock by deletion [message #237380 is a reply to message #237374] Mon, 14 May 2007 05:40 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Search orafaq and you find
Re: Deadlock by deletion [message #237382 is a reply to message #237380] Mon, 14 May 2007 05:47 Go to previous messageGo to next message
liron
Messages: 3
Registered: May 2007
Junior Member
Thanks, but that still doesn't answer my question...
Re: Deadlock by deletion [message #237393 is a reply to message #237374] Mon, 14 May 2007 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, because the two statements can retrieve the rows in different order.

Regards
Michel
Re: Deadlock by deletion [message #237404 is a reply to message #237393] Mon, 14 May 2007 06:52 Go to previous messageGo to next message
liron
Messages: 3
Registered: May 2007
Junior Member
Is ther a way to control the deletion's order?

The only solution I can think about is cursor for loop over the ordered table, and deleting each row inside the loop.
Re: Deadlock by deletion [message #237409 is a reply to message #237404] Mon, 14 May 2007 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe:
delete (select rowid from my_table where x=1 order by <your_pk or rowid if none>); 

but I'm not sure you won't get another error due to phantom reads.
Btw, there is the same issue with PL/SQL.

Regards
Michel
Re: Deadlock by deletion [message #237419 is a reply to message #237409] Mon, 14 May 2007 07:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Did you get a deadlock? Or are you afraid of getting one?

If you got one, post the deadlock trace file.

Ross Leishman
Re: Deadlock by deletion [message #237422 is a reply to message #237419] Mon, 14 May 2007 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ross,

Do you talk to me or to OP?
I think OP question is in anticipation of a problem.

Regards
Michel
Re: Deadlock by deletion [message #237433 is a reply to message #237422] Mon, 14 May 2007 08:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Does the OP actually mean 'Deadlock' in the Oracle sense, or are they simply refering to one session getting locked and waiting for the blocking session to complete?
Re: Deadlock by deletion [message #237434 is a reply to message #237433] Mon, 14 May 2007 08:29 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Humm! ./fa/1600/0/
Good question!

Regards
Michel
Previous Topic: identity column
Next Topic: Problem in getting Date Range Data from a Procedure
Goto Forum:
  


Current Time: Fri Dec 09 15:40:16 CST 2016

Total time taken to generate the page: 0.29519 seconds