Stored Procedure Synchronization

[Prithwish Ghosh]

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]

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]

But, You can do this by obtaining a lock. The simplest is to obtain a lock on a row of some table:

procedure p2 is
begin
  select col from some_table where pk=...
  for update;
  -- Now continue
end;

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]

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]

Hi,

You can simply google on "autonomous transaction oracle"

or

Follow this link http://www.oracle-base.com/articles/misc/AutonomousTransactions.php