Transaction control in plsql [message #306622] |
Sat, 15 March 2008 02:31 |
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 #306629 is a reply to message #306628] |
Sat, 15 March 2008 04:09 |
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 #306671 is a reply to message #306622] |
Sat, 15 March 2008 12:11 |
|
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 |
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 |
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 |
|
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 |
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.
|
|
|
|