Home » SQL & PL/SQL » SQL & PL/SQL » Commit in Procedure (Oracle 9i)
Commit in Procedure [message #365315] Thu, 11 December 2008 07:07 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I heard that using COMMIT in procedure is bad way of programming.

Is it right assumption?

If yes then please explain me regarding the same.

Thanks,
Re: Commit in Procedure [message #365316 is a reply to message #365315] Thu, 11 December 2008 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must only commit when a business rule needs or tells it.

Regards
Michel

[Updated on: Thu, 11 December 2008 07:12]

Report message to a moderator

Re: Commit in Procedure [message #366182 is a reply to message #365315] Fri, 12 December 2008 00:29 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes very correct. Consider as Michel says that the business need dictates when to commit. The typical example is ofcourse the bank transaction where you move money from one account to another. In sql terms it looks something like this:

update account set total = total - 100 where account = 1;
update account set total = total + 100 where account = 2;
commit;

This process has two steps, but both are done in the same transaction because there is only one commit.

Now some not too bright plsql developer does this for you because it makes the code "reusable" and everybody like "reusable" right?

create or replace procedure update_account (amount_p in number, account_id_p in number) is
begin
   update account set total = total + amount_p where account_id = account_id_p;
   commit;
end;
/


If you were to use this procedure then the tranaction above becomes this

exec update_account (-100,1)
exec update_account (+100,2)


This makes your code do this:

update account set total = total - 100 where account = 1;
commit;
update account set total = total + 100 where account = 2;
commit;

Your not too bright plsql developer has forced you into a bad situation because when you run your code guess what happens? This does:

exec update_account (-100,1)

procedure completed successfully

exec update_account(100,2)

ORA-01652: unable to extend temp segment

100 dollars got deducted from account 1, but it was never given to account 2, because your system ran out of space somewhere (or any of 1000 other unexpected issues). And because the first event was commited, you can't undo the change to account 1. Mmm... bad deal.

The business made it clear that the transaction had two parts which must go together.

But your developer put a commit into a procedure that ultimately split the transaction up into two transactions.

This is why in general it is a bad idea to put commits in plsql procedures/functions. There are always exceptions but it is a bad practice most of the time. For most systems, it is the client code's responsibility to say when it is done. This means, the program that starts the transaction, is the one that should end it with a commit.

Good luck, Kevin

[Updated on: Fri, 12 December 2008 00:30]

Report message to a moderator

Re: Commit in Procedure [message #366224 is a reply to message #366182] Fri, 12 December 2008 03:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good explanation!
Re: Commit in Procedure [message #366308 is a reply to message #366224] Fri, 12 December 2008 13:14 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right; that's why programmers should reverse these calls: not
exec update_account (-100,1)
exec update_account (+100,2)
but
exec update_account (+100,2)
exec update_account (-100,1)
so that - if something breaks - you eventually get your $100 (or whichever currency we are talking about) even though it cannot be rolled back, which is, of course, better than losing $100 (which also cannot be rolled back).

I was just kidding (just in case that someone takes it seriously!)
Re: Commit in Procedure [message #375881 is a reply to message #366308] Mon, 15 December 2008 04:13 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, it's better from the customer's point of view, but the chances are it's the Bank who are paying the developers wages.
Previous Topic: Display datatype double until 2 precision number
Next Topic: Strange Error
Goto Forum:
  


Current Time: Tue Dec 06 12:19:00 CST 2016

Total time taken to generate the page: 0.09293 seconds