OraFAQ Forum: SQL & PL/SQL » FOR UPDATE clause

Home » SQL & PL/SQL » SQL & PL/SQL » FOR UPDATE clause (ERP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

FOR UPDATE clause [message #624733]

Thu, 25 September 2014 01:40

chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India

Senior Member

Dear,

We have used the FOR UPDATE clause widely in our product in situations like, the document number generation and updation of the current stock status when each and every transaction is begin made.

In a multi user environment we encounter dead locks frequently and DBA had analysed and suggested to remove this FOR UPDATE clause.

Is this going to help in resolve the performance/deadlocks or create additional issues since the exclusive row locking via FOR UPDATE is begin removed ?

Kindly suggest.

Report message to a moderator

Re: FOR UPDATE clause [message #624735 is a reply to message #624733]

Thu, 25 September 2014 01:49

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

I see a lot of unnecessary FOR UPDATE usage when developers are not aware of how Oracle handles read consistency. The problem is particularly bad if the developers were trained in a SQL Server environment. Deadlocks are an application design issue (as is made clear in the ORA-00060 message) caused by your developers writing bad code.

Report message to a moderator

Re: FOR UPDATE clause [message #624738 is a reply to message #624735]

Thu, 25 September 2014 01:58

chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India

Senior Member

Thanks.

Can i consider to remove these FOR UPDATE clauses and be granted that i will not end up in data discrepancy. (Atleast 250 user sessions will be acessing the product simulataneously)

Report message to a moderator

Re: FOR UPDATE clause [message #624740 is a reply to message #624738]

Thu, 25 September 2014 02:05

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Can i consider to remove these FOR UPDATE clauses

No, you have to review your code and see if it is necessary or not, for each case.

Report message to a moderator

Re: FOR UPDATE clause [message #624789 is a reply to message #624740]

Thu, 25 September 2014 10:41

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

May be you should consider using some mechanism to avoid user conflicts ...like comparing orahash values before doing update.... using "for update" is certainly comes with cost.

Report message to a moderator

Previous Topic:	Need Sql query
Next Topic:	Unique constraint error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 11:51:48 CDT 2024