aspose file tools*
The moose likes Struts and the fly likes Stored Procedure Synchronization Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Frameworks » Struts
Bookmark "Stored Procedure Synchronization" Watch "Stored Procedure Synchronization" New topic
Author

Stored Procedure Synchronization

Prithwish Ghosh
Greenhorn

Joined: Mar 04, 2009
Posts: 9
Hi,

I've one question about oracle stored procedure. I know it's a java forum but it's little bit related to java also. My question is - Is oracle stored procedure run synchronously ?? Let me clear my question -

I'm calling a stored proc from my servlet code and that procedure calculate some monetary values. Now I've cover my proc calling section within a synchronized block, so that at a single point of time procedure will execute for a single user only. Now my problem is another application is also accessing the same procedure from different server, and that application is out of my reach. So if both the applications (my apps and other apps locating on diff server) access the proc and calculation will run at the same time then some undesired result will come. In this case if I able to make the procedure synchronized then my program will be full proof.


So can anyone give any idea regarding this ?

Thanks in advance..
ujjwal soni
Ranch Hand

Joined: Mar 28, 2007
Posts: 403
Hi,

Since you are calling a single stored procedure from two different applications and as you said, you dont have access to other application, it would be called asynchronously.

Cheers!!!


Cheers!!!
Ujjwal B Soni <baroda, gujarat, india> <+919909981973>
"Helping hands are better than praying lips......"
ujjwal soni
Ranch Hand

Joined: Mar 28, 2007
Posts: 403
But, You can do this by obtaining a lock. The simplest is to obtain a lock on a row of some table:



The second call to P2 will have to wait for the other caller's transaction* to finish before it can continue.

Alternatively, you can use the DBMS_LOCK package to define your own locking mechanism.

* Note: my p2 code above locks a record in some_table for the duration of the whole transaction, not just the procedure call. This may be too much. You could use an autonomous transaction to prevent this.
Prithwish Ghosh
Greenhorn

Joined: Mar 04, 2009
Posts: 9
Thank you very much for sharing your valuable knowledge. I'm not familiar with autonomous transaction. I'll look upon this matter or if you give any links it'll be greatfull.

Thanks again.
ujjwal soni
Ranch Hand

Joined: Mar 28, 2007
Posts: 403
Hi,

You can simply google on "autonomous transaction oracle"

or

Follow this link http://www.oracle-base.com/articles/misc/AutonomousTransactions.php
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Stored Procedure Synchronization