Home » SQL & PL/SQL » SQL & PL/SQL » Preventing concurrent running of a stored procedure (Oracle 10g)
Preventing concurrent running of a stored procedure [message #568098] Tue, 09 October 2012 08:38 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi all,

I have a stored procedure that is run from a command within our Clarity application.
The procedure involves some SQL Reads and SQL Inserts.

We have experienced users running the SP at the same time (slim chance to do this) and it creating duplicate entries.

Can anyone tell me if there is a clever way of preventing the same SP to be run concurrently?

Initially I was thinking of having the first step of the SP to interrogate a flag into a custom table - which the SP then sets to 1 if it is running, and 0 at the end.

Are there better more efficient/effective ways of doing this?

Regards,
Matt
Re: Preventing concurrent running of a stored procedure [message #568099 is a reply to message #568098] Tue, 09 October 2012 08:40 Go to previous messageGo to next message
BlackSwan
Messages: 22484
Registered: January 2009
Senior Member
>Initially I was thinking of having the first step of the SP to interrogate a flag into a custom table
A session can NOT see unCOMMITTED DML from other sessions so this "solution" crashes & burns upon initial takeoff!
Re: Preventing concurrent running of a stored procedure [message #568100 is a reply to message #568099] Tue, 09 October 2012 08:42 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

couldn't I use a 'commit' statement within the PL/SQL after setting the flag?
Re: Preventing concurrent running of a stored procedure [message #568101 is a reply to message #568098] Tue, 09 October 2012 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 58499
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Are there better more efficient/effective ways of doing this?


Use a UNIQUE/PK constraint to avoir the duplicates.

Regards
Michel
Re: Preventing concurrent running of a stored procedure [message #568106 is a reply to message #568101] Tue, 09 October 2012 08:51 Go to previous messageGo to next message
BlackSwan
Messages: 22484
Registered: January 2009
Senior Member
>couldn't I use a 'commit' statement within the PL/SQL after setting the flag?
doing so "contaminates" transaction integrity & prevents use of ROLLBACK.
Re: Preventing concurrent running of a stored procedure [message #568126 is a reply to message #568106] Tue, 09 October 2012 10:59 Go to previous messageGo to next message
ThomasG
Messages: 3087
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What I have done for such things is setting the client info in the procedure to something meaningful.

That way another "start attempt" can check in v$session if something that conflicts logically is already running.
Re: Preventing concurrent running of a stored procedure [message #568208 is a reply to message #568126] Wed, 10 October 2012 04:32 Go to previous messageGo to next message
rleishman
Messages: 3684
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I'm sure serialising call is not the best answer, but that's your problem, not mine. If you are determined to serialize calls to the proc you can use the DBMS_LOCK package to lock a virtual "resource". Just call DBMS_LOCK.REQUEST('MY_PROCEDURE_NAME') to lock a pretend resource called MY_PROCEDURE_NAME, then any other request for the same lock will wait (by default) until the first call commits.

Be aware that DBMS_LOCK is considered a privileged package, so you may need to get an exemption from your DBA.

Also note that the lock will be released AS SOON at the job commits. If the stored proc contains interim commits, you may release the lock earlier than you intended.

Ross Leishman
Re: Preventing concurrent running of a stored procedure [message #568280 is a reply to message #568126] Wed, 10 October 2012 08:36 Go to previous message
Solomon Yakobson
Messages: 1949
Registered: January 2010
Senior Member
ThomasG wrote on Tue, 09 October 2012 11:59
That way another "start attempt" can check in v$session if something that conflicts logically is already running.


But still leaves a tiny chance of multiple sessions executing same SP.

SY.
Previous Topic: How to execute stored procedure in sql developer?
Next Topic: Pivot
Goto Forum:
  


Current Time: Wed Jul 23 09:15:56 CDT 2014

Total time taken to generate the page: 0.73635 seconds