Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I got this database in oracle which includes 4 schemas. Each schema represents a logical layer. Each transaction (Stored Procedure) in the upper layer breaks into a couple of transactions in the lower layer. So there is this scenario:

I execute a SP in the upper layer which results in some inserts in both layers but for some reason one of the transactions in the lower layer fails. Is it possible to rollback the whole thing?

share|improve this question
    
Yes, this works fine. Just make sure there are no commits in any of your stored procedures, or at worst, only in the top-level stored procedures. –  Colin 't Hart Nov 6 '13 at 8:41
    
@Colin'tHart: Actually this is not a good solution, because I might wanna call a procedure directly from the lowest layer. That procedure, which is an atomic logical operation, must be either committed or rolled back. One (imaginary) solution would be some chaining-like mechanism that you could bound the execution of some transactions to the execution of another. I'm not hopeful if such a feature exists! –  SJ.Jafari Nov 6 '13 at 8:53
3  
Then, like I said, make sure there are NO commits in your stored procedures and do all the committing outside of them. –  Colin 't Hart Nov 6 '13 at 9:04
    
I added a "commit_op" parameter to all my SPs which let's the caller decide whether to commit it or not. @Colin'tHart, if you answered as an "Answer" I could accept yours :) –  SJ.Jafari Nov 6 '13 at 12:16
    
Why add parameter and if statement to each procedure when the caller could simply do proc(); commit; ? –  Colin 't Hart Nov 6 '13 at 12:29
show 2 more comments

2 Answers

yes you can, just be sure that the transactions are defined by the uppper layer - the client. A transaction has to be Atomic, so has to succeed (and commit) or has to fail (and rollback).

share|improve this answer
add comment

In Oracle schema has nothing to do with transactions. Schema is a namespace and it is assocated with a user:

  • it is the default namespace for this user if she issues an sql statement, e.g select * from customers; selects data from the table named customer of the current schema.
  • she is the owner of the objects of this namespace which means she has some default privileges , e.g. select,insert,update,delete on her's tables.
  • there are system privileges that applies for the default namespace, e.g. create table allows creation of the table in the schema (default namespace of the user)

There is no relation between schema and transactions.

share|improve this answer
1  
Why the down vote? –  miracle173 Nov 6 '13 at 18:10
    
That's right. Transactions are schema namespace independient. I upvote. –  vegatripy Nov 12 '13 at 16:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.