Timeline for Oracle - Is it possible to maintain transaction across multiple schemas?
Current License: CC BY-SA 3.0
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 2, 2018 at 10:52 | vote | accept | SJ.Jafari | ||
| Nov 12, 2013 at 16:36 | comment | added | vegatripy |
There's no point in what are you trying to do because you can always control when the commit; is issued (the same way as you implement a parameter to control when it has to commit). And also, regardless of the schema.
|
|
| Nov 6, 2013 at 14:00 | answer | added | miracle173 | timeline score: 2 | |
| Nov 6, 2013 at 13:59 | comment | added | SJ.Jafari | Well, as I mentioned before, the SPs are logical atomic operations. Sometimes they are called by a parent proc as part of a bigger transaction, so they mustn't get committed. on the other hand there are cases that the SPs are called directly as one transaction, so they must commit themselves. The caller always knows whether the called SP should be committed or not. | |
| Nov 6, 2013 at 12:29 | comment | added | Colin 't Hart |
Why add parameter and if statement to each procedure when the caller could simply do proc(); commit; ?
|
|
| Nov 6, 2013 at 12:16 | comment | added | SJ.Jafari | 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 :) | |
| Nov 6, 2013 at 11:01 | answer | added | user953 | timeline score: 3 | |
| Nov 6, 2013 at 10:58 | history | edited | user953 | CC BY-SA 3.0 |
typo in title
|
| Nov 6, 2013 at 9:04 | comment | added | Colin 't Hart | Then, like I said, make sure there are NO commits in your stored procedures and do all the committing outside of them. | |
| Nov 6, 2013 at 8:53 | comment | added | SJ.Jafari | @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! | |
| Nov 6, 2013 at 8:41 | comment | added | Colin 't Hart | 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. | |
| Nov 6, 2013 at 8:37 | history | asked | SJ.Jafari | CC BY-SA 3.0 |