May 6, 2003 at 1:46 am
I have a stored procedure that transforms data from one table to another.
It uses normal tables to store intermediate results.
The procedure should only be run exclusively by one user at a time.
Is there a mechanism to prevent any concurrent use of this procedure? Or does SQL server automatically lock all the referred objects?
Regards,
SeekQuel
May 6, 2003 at 1:55 am
One alternative is to use transactions and issue a SELECT with TABLOCKX within the transaction on the tables to be locked.
TABLOCKX holds exclusive lock on the table till the transaction is completed.
May 7, 2003 at 3:15 am
Thanks!
Did some tests here and this is the solution i've implemented.
I have a dedicated table I lock exclusively. The other tables in the procedure are only locked when necessary, so I don't block our production system while the procedure is running (it's an ETL procedure).
When executing this proc twice, you'll notice the second process being blocked.
CREATE PROC USP_Concurrent_Test
AS
BEGIN
/* The proc may only be executed exclusively. I define a transaction and I use a logging table that I lock exclusively at the start of the procedure */
DECLARE @liDelay int /* Local Delay variable */
BEGIN TRAN
INSERT INTO dbo.tblLog (RunDate)
VALUES getdate
tablockx holdlock /* locks the tblLog table */
SET @lidelay=50
WAITFOR DELAY @liDelay
COMMIT TRAN
END
May 7, 2003 at 3:59 am
This is a typical concurrency issue that you often find in 'normal' programming. Just copycatting the semaphore or mutex idea from there, I would construct a table containing one record for each concurrency limited resource.
For each procedure you want only a limited number of instances to run, you add one to the value when you start the procedure and subtract one when it completes.
Before running the procedure, you have to check if the value is not over the maximum threshold.
Of course, you still have to provide for the proper locking control over this record, but you can get away with row level locking for this one. And obviously, if you have more procedures, you just add more records, one for each such procedures.
Seems like a nice little project for the 'Scripts' section...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply