Is it possible to grant exclusive execution on a SP?
-
Is it possible to grant exclusive execution on a SP?
That means if there are multiple connections(or processes) trying to execute the SP, only one connection will be granted at a time.
Any idea?
-
No.
Permissions are granted to users, not connections.
Connections do not have a security context.
You could code the proc to check if it's running, and if so exit itself. (probably with some kind of 'I'm alive and running flag' of some sorts)
/Kenneth
-
Top of the head idea, not tried or tested.
Create a new table to contain two columns, procname and datetime. Put all sql in proc within a transaction. Add an update to the above table after trans started and before your sql. This should cause blocking (probably why this is not a good idea) on any other calls to this procedure. Make sure you commit/rollback the tran when finished to release any locks/blocking.
Far away is close at hand in the images of elsewhere.
Anon. -
This sounds shaky at best.
I'd suggest that you instead create a semaphore table - maybe a datecolumn and an isRunning column - then have the proc check this table before it does anything else.
ie if isRunning = 0 then update it to 1, then continue.
if isRunning = 1 then exit (perhaps with some informing message)
After the proc is done, reset isRunning to 0.
...something along those lines could be used to make the proc serialized.
/Kenneth
-
While trying to manufacture a semophoric method for locking a stored procedure by implementing something like a procedure execution state table that is constantly updated, SQL 2000 has the perfect construct for this and it can also be used to enforce that critical series of transactions can only be entered by one process at a time. These are called application resource locks and are created and released using the sp_getapplock and sp_releaseapplock system procedures.
In the simple case of wanting to ensure only a single process can execute a procedure at a time, you could add a bit of code like the following to the procedure.
Declare @getlockRC int
Set @getlockRC = 999999999
While @getlockRC < 0
begin
EXEC @getlockRC = sp_getapplock @Resource = 'MyProcNameLock',
@LockMode = 'Exclusive'
end
... the code for the procedure
EXEC sp_releaseapplock @Resource = 'MyProcNameLock'
RETURN
Now obviously this is not ideal as an app could sit and spin on this forever so you could add an attempt counter variable and raise an error if the number of attempts are exceeded.
There are several other features such as being able to specify a timeout and whether the lock is associated with the current transaction or the session. A lock owned by the session is held through transactions and could be used to protect a series of transactions, or at a more abstract level could be used to enforce a rule that only one person can modify an account at a time. The resource could then be the account number or id, the lock mode exclusive and the lock owner set to session.
This just seems to be a simple way that provides a lot of functionality that can be used instead of trying to roll your own concurrency controls, and the you also don't have to worry about resetting any table stored values in the case of dropped connections or unexpected server issues. The application resource locks are all cleared automatically when these things occur. You should take a look in the SQL Books Online at these two system procedures.
Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp. -
Kenneth, I would disagree with 'shaky', not ideal maybe.
The problem with using a semaphore is that if an error occurs that stops the procedure then the semaphore is not changed and you have a permanent block with no traceabilty unless the semaphore table contains more info such as spid, user etc. At least with the lock you can use sql to find the blocking spid.
Any solution, good or bad, will depend on the type of application and transaction volume.
Don't get me wrong, I like the idea of using semaphores but have seen so many systems die because of them or constant support calls to release the semaphore (and this can happen in the best, error free, systems).
Far away is close at hand in the images of elsewhere.
Anon. -
'shaky' is just a synonym for 'not ideal'
You're right - dropped connections and such would risk leaving the semaphores stuck.
The sp_getapplock variant seems like a better path - although it's not 100% waterproof in that sense either - it gives you a readymade base to work from. And I also stand corrected about my initial post - sp_getapplock is a method that indirectly lets you grant exclusive exec on a proc at the session level, by leveraging the locking mechanism.
I would assume that it's still possible for any connection to actually exec the proc, but in this case it will just wait until resources are freed, so the end result would be the same. Only problem then might be how to present to the user that his clientside hourglass is 'normal'
I would think that it requires from the programmer to have a thorough understanding of how transactions work in SQL Server in order to use it to full advantage, though.
/Kenneth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply