Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Stored Procedure Synchronization

 
Prithwish Ghosh
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 403
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!!!
 
ujjwal soni
Ranch Hand
Posts: 403
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 403
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

You can simply google on "autonomous transaction oracle"

or

Follow this link http://www.oracle-base.com/articles/misc/AutonomousTransactions.php
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic