• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Why commit should not be written in procedures being called from Java?

 
Ranch Hand
Posts: 264
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why commit should not be written in procedures being called from Java?

Many times I have faced problems when my code was executing procedures written in Oracle and there was a commit; in the procedure.

My application was deployed on weblogic, and the exception i used to get was

ORA-02089: COMMIT is not allowed in a subordinate session.

I want to know what is the meaning and why the commit is not allowed.
I know that is not a good practice to write commit in procedures, but I don't know the reason, why?

Can some one please tell me the precise reason, so as why one should not write commit?
What if there is a requirement to write a commit?
Or there cannot be any scenario in the world where commit has to be written in procedure? or do we have a workaround in every scenario.

Some knowledgeable people, please help me understand the mystery.
 
Bartender
Posts: 4179
22
IntelliJ IDE Python Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There is no rule about not using commit in Java. This is a Weblogic issue: Weblogic holds the pools of connections for you and auto commits them. Since Weblogic handles committing you can't. I have only briefly used Weblogic, but I believe there is an option somewhere (global transaction or global commits or something like that) where this can be turned off if you want control over committing.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The database connection you're calling the stored procedure from is part of a distributed transaction. Individual participants in a distributed transaction cannot commit on their own, only the transaction coordinator can do that (otherwise the entire distributed transaction wouldn't be atomic). As Steve says, there is probably a Weblogic setting somewhere which turned distributed transactions on for you.

However, some people do say that stored procedures indeed shouldn't commit (Oracle even has a setting which causes all commits in the stored procedures to be silently ignored). I can think of these problems that can arise when calling stored procedures with commits:

1) You cannot use distributed transactions.

2) The caller (eg. a Java application) cannot build a larger transaction by calling several of your procedures in sequence, since the unwanted commit cannot be avoided by the caller.

3) You won't be able to use frameworks that control transactions (eg. through annotations) - again, because there are commits outside of the control of the framework.

And while the first case causes an error to be thrown (the ORA-02089 you got), the other two cases don't cause any errors, but result into a broken logic, which, furthermore, will surface only when there is a rollback - the committed part won't get rolled back and you'll probably end up having slightly corrupted data. I wouldn't want to be the one to look for the cause.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic