This content has been marked as final. Show 2 replies
Normally, stored procedures do not commit. Normally, stored procedures are designed so that the caller can call many different stored procedures within the same transaction.
On the other hand, it is entirely reasonable that data warehouse developers do things a bit differently. It is entirely reasonable, for example, that a data warehouse process might be written to be restartable with interim commits in order to ensure that if there is an error after, say, an hour of processing that an unexpected error doesn't force the database to spend an hour rolling back the changes only to have to then restart the entire process. It is entirely reasonable that a data warehouse load process might issue DDL (disabling and enabling constraints, for example) that would implicitly commit. So it's entirely possible that there will be reasonable reasons for a stored procedure in a data warehouse load to commit.
A debate is raging at my workplace between the app developers who write the stored procs used on their back end db and the data warehouse etl developers who are to use said stored procs. The app developers say that stored procs should never internally issue 'commit' statements and that only the calling connection should commit afterwards and that this is a general standard in the database world. The data warehouse etl group insist that it's perfectly OK for a stored proc to issue its own commit.
What are the standards where you work; do sp's commit internally or not? Is one way or the other the exception or the rule in typical Oracle practices?
The question is not 'who should commit' it is 'when to commit'. The 'when' will generallyl dictate the 'who'.
A COMMIT should be performed when a unit of work has been completed.
Based on how your post was worded your app developers are WRONG if for no other reason than you simply can't use the word 'never' when you are talking about complex processes.
For the same reason the data warehouse group is RIGHT since they did not say it is ALWAYS ok, just that it is OK for a stored proc to issue its own commit. And
All anyone from either of your groups needs to do is come up with just ONE example where it is OK for a stored proc to issue its own commit and the dev groups position is TOAST; ETL is vindicated once again.
You can't make broad generalizations like NEVER and ALWAYS. So since we are talking COMMIT and not RECOVERY let's ignore flashback and the like.
If a stored proc is designed to perform a truncate and load of a staging table there is absolutely no reason NOT to issue a commit when the load is completed. The TRUNCATE itself will be irreversible; meaning it can't be undone using a simple ROLLBACK.
And if the subsequent load of the table is successful why shouldn't the proc issue the commit? The proc, in this case, is the transaction controller and can determine if the unit of work has been completed. If it has it should issue the COMMIT.
The process architecture needs to be designed to take restart and recovery into account. That design will dictate at what steps in the process work should be committed. The controlling process that determines that the unit of work has been completed successfully is the process that should perform the commit. If that process is part of a master stored procedure the commit should happen in the procedure. If that process is part of an ETL workflow decision the commit should be issued as part of that same workflow.