Home » SQL & PL/SQL » SQL & PL/SQL » Transaction control in plsql
Transaction control in plsql [message #306622] Sat, 15 March 2008 02:31 Go to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Hi,

I am writing a Procedure through which i am inserting data in 5 tables.
I want to ensure that if some error occurs (e.g Power Failure)
when the procedure is running & data is not inserted in all the tables then, The entire data should be Rolled back.

If data is inserted in all the tables then only data should be commited.
Does a Stored Procedure takes care of this internally.
Please help me out in this regard.
Thank You.
Re: Transaction control in plsql [message #306623 is a reply to message #306622] Sat, 15 March 2008 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL is transactional, it does not commit or rollback unless you ask it to do so.
So your procedure acts in the same way if you execute the statements by yourself.
For instance, if the third insert fails then the first 2 ones were executed and you (the caller) have to know if you want to roll back or commit; this is not to the procedure to know what you want to do.

Regards
Michel
Re: Transaction control in plsql [message #306625 is a reply to message #306623] Sat, 15 March 2008 02:57 Go to previous messageGo to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Thanks Michel,

How do i know if any insert fails(e.g If the second insert fails
how will i handle it from the procedure)
Re: Transaction control in plsql [message #306628 is a reply to message #306625] Sat, 15 March 2008 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How do i know if any insert fails

You get an exception.

Quote:
how will i handle it from the procedure

Trapping the exception in the procedure: use an "EXCEPTION" block.

Regards
Michel
Re: Transaction control in plsql [message #306629 is a reply to message #306628] Sat, 15 March 2008 04:09 Go to previous messageGo to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Thanks Again,

If i create a savepoint after the begin clause

BEGIN
CREATE SAVEPOINT A;


& In the exception block
I use

WHEN OTHERS THEN
ROOLBACK TO SAVEPOINT A


Is this the right approach to solve this issue or is there any specfic ORA-CODE that is fired when a insert fails.

Re: Transaction control in plsql [message #306669 is a reply to message #306629] Sat, 15 March 2008 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add a RAISE in the WHEN OTHERS part.
Your caller must know there was a failure and which one.
Apart from that it is good.

Regards
Michel
Re: Transaction control in plsql [message #306671 is a reply to message #306622] Sat, 15 March 2008 12:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
in my opinion, savepoints are a bad thing. They are I believe a product of theory that does not translate well into practice. I have used them a few times in the past and in every case only had trouble with them. In the end, I never actually needed them. Savepoints suffer from two problems:

1) the argument for their use is the concept of "RETRY". The theory goes something like this: In concurrent systems, there are certain "resource in use" errors that are recoverable if one is willing to wait a short time and then try to get the resource again. A savepoint makes it possible to do this retry by allowing the developer to reset his/her environment back to a known state and try again.

In practice this retry logic does not really work.

A) few people know what the list of "recoverable errors" actually is

B) rollback to savepoint does not actually reset the environment, it does only what its name suggests, undoes database work back to the savepoint. It does not reset package state to its condition at the time of the savepoint (how could it) and thus pl/sql global variables (among other things) get out of synch with the retry operation as a whole. Also, rollback to savepoint does not release locks that have been acquired by the process becaues it expects them to be reaquired in a moment anyway. The net effects of these two facts is that retry using savepoints almost never actually succeeds in practice, and can in fact lead to incorrect results if it does succeed and the developer has relied in part on pl/sql state to compute an answer.

C) retry logic complicates a program massively. Retry logic is technical code, not business code. By this I mean it does not actually have anything to do with the business process being written. It is one technical response to one set of specific technical issues found in a relational environment. To undestand what I mean, consider that if you remove all retry logic and savepoints from a piece of code, that code will still do what it was written to do, from a business angle. Retry logic easily can double the size of a program. It makes understanding what the program is doing difficult because one must wade through the retry logic in order to find the "real code". And for what? It is code that will almost never be executed, and in the rare situation where it is executed, will fail most of the time anyway and thus make no difference. Very much not worth it.

2) savepoints are an unfriendly database feature for Oracle. For example, they do not like playing with INSTEAD-OF-TRIGGERS. I forget the specific error message, but systems that use instead-of-triggers more than one level deep, which if you are using these in any true design philosophy, is always, then you will get errors with savepoint code. I for one think instead-of-triggers is one of the top 10 best features ever delivered by Oracle. I won't use any feature of the database that does not play with them.

So my advice is not to even think about savepoints ever.

Transaction management is a basic relational database thing. Keep it simple for yourself. All work done between two commits is a transaction. Transactions are commited or undone as a whole, not pieces of them. Code your work as a transaction and you are all set.

good luck, Kevin
Re: Transaction control in plsql [message #306821 is a reply to message #306671] Mon, 17 March 2008 00:27 Go to previous messageGo to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Thanks Kevin,
I really appreciate your efforts in giving me explanation about SAVEPOINTS..

In my procedure i have to insert data in 5 tables so will it help, If i code my procedure using begin transaction
E.g

BEGIN TRANSACTION
Insert into t1..
Insert into t2..
Insert into t3..
Insert into t4..
Insert into t5..
COMMIT TRANSACTION


In above case if a insert fails at table t3, will insert at table t1 be rolled back..
Re: Transaction control in plsql [message #306835 is a reply to message #306671] Mon, 17 March 2008 01:20 Go to previous messageGo to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
I believe i have a made a mistake by using

BEGIN TRANSACTION
COMMIT TRANSACTION


as it is not applicable in Oracle DB.

Instead, can i use Pragma Autonomous_Transaction
Please explain it to me with an example..

I apologies for th mistake.
Re: Transaction control in plsql [message #306842 is a reply to message #306821] Mon, 17 March 2008 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on what you want to do.
Does the 5 inserts part of your current transaction or not?
Are they totaly or partially independent?
Do you have a way to handle failure of these 5 inserts in your current transaction?
...

If it is a totally independent part, you can use an autonmous transaction. This is the case for instance for a logging part.
If it is a partially dependent and you know how to handle it in case of failure then you can use savepoint. This is the case for instance for replication, you wish to replicate but it does not need to be synchroneous, in this case if there is an error (for instance remote site is not accessible) you can submit a job to do the replication later.
If it is totally part of your transaction, that is the failure of one input implies the failure of the whole transaction (these inserts and the rest) then is nothing to do/add.

Regards
Michel

[Updated on: Mon, 17 March 2008 02:24]

Report message to a moderator

Re: Transaction control in plsql [message #306850 is a reply to message #306842] Mon, 17 March 2008 01:58 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Rule of thumb:
autonomous transactions are not used in production code.

Only deviate from this rule if
- you understand why this rule should be followed
- you understand why and when you can violate it.
Re: Transaction control in plsql [message #306975 is a reply to message #306622] Mon, 17 March 2008 07:13 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
read this please.

http://www.orafaq.com/node/1915

Kevin
Previous Topic: Names separated by "|" in a single line to individual names
Next Topic: Oracle number(*,0) [merged]
Goto Forum:
  


Current Time: Tue Nov 05 12:21:10 CST 2024