April 11, 2007 at 3:34 pm
I have a stored procedure that I would like to force to be single-threaded to prevent contention. What is the best way to do this?
April 11, 2007 at 4:02 pm
Use the MAXDOP (Max. Degree Of Parallelism) query hint option:
SELECT
col1, col2
FROM dbo.tab1 a INNER JOIN dbo.tab2 b ON a.id = b.id
OPTION (MAXDOP 1)
http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx
Eddie Wuerch
MCM: SQL
April 11, 2007 at 4:18 pm
You may also consider writing logic into your stored procedure to take advantage of SQL Server's application locks. Use BOL to read up on sp_getapplock. In a nut shell, you'd have to write the logic to check the app lock and get a lock if none exists. Don't forget to release the lock at the end of the SP. I have also seen where people will add logic into the stored procedure to add an entry into a table during SP exection and check the table prior to exectution. I believe you can find more on SSC if you look up stored procedure serialization.
April 11, 2007 at 4:26 pm
John,
Thanks for that suggestion. Actually, right now, I am using "logical locking" by writing a record to a table. The problem is that if the stored procedure fails badly, the lock record isn't cleaned up and I am looking for a way around this.
Tom
April 11, 2007 at 4:36 pm
SQL Server treats an application lock just as it would a lock on any other physical resource such as a table. I have not played around with it enough to see what happens to the lock in the event of a SP failure. You may want to play around with this, or just use the MAXDOP suggestion that Eddie had.
April 12, 2007 at 5:55 am
Just a term issue, I think everyone has caught on that you want to prevent more than one person from running the same procedure at the same time. This is concurrency, not threading.
App locks clean up automatically if a connection closes, just like a regular lock does. You can also specify a lock timeout when getting one. Here is an example of what you could put into the top of a procedure to prevent concurrent execution:
BEGIN TRAN
EXEC @Result = sp_getapplock @Resource = '<My Procedure Name>', @LockMode = 'Exclusive', @LockTimeout = 3000 --Time to wait for the lock
IF @Result <> 0
BEGIN
ROLLBACK TRAN
RAISERROR('Procedure Already Running - Concurrent execution is not supported.',16,9)
RETURN(1)
END
April 12, 2007 at 6:47 am
Michael,
That's exactly what I was looking for. Now, I will be using Session versus Transaction locks so I have a couple more questions.
What defines a session and what terminates a session? Specifically, I have a C++ application (nothing is transactional) that will be calling this stored procedure. Is the session the execution of the C++ application or just the execution of the stored procedure. Also, I assume that the session ends upon abnormal termination (including cancellation) of either the stored procedure or C++ program. Is that right?
April 12, 2007 at 7:31 am
I think you will have to make some adjustments to not use a transaction within the stored procedure. I do not think the app lock can be called with an exclusive lock without a transaction.
If I remember right, the conclusion of the transaction will release the lock in the example I have provided. In addition, the closing of the connection object will certainly release an app lock (your spid is released and the lock is bound to it).
Abnormal termination of your application will release it as long as the termination cleans up any objects still in memory (including the connection to the database). If it doesn't, you have a memroy leak you need to fix anyway.
I suggest you create a little stored procedure that has the sp_GetAppLock and then a WAITFOR DELAY '00:01:000' (waits for 1 minute with the lock remaining) and test all of your scenarios to make sure it does what you want.
April 12, 2007 at 8:10 am
I'm curious to know if MAXDOP will produce the same effect as sp_getapplock, which I know does work as advertised. Too why can't we use transaction isolation levels tot serialize concurrency?
April 12, 2007 at 8:27 am
MAX DOP prevents multi-threading within a single process, and that is not really what he is trying to do. With MAX DOP set to 1, you can have two users run the same stored procedure and they will both run and complete. What it prevents is a single select statement (or other process) using more than one thread to process it's results. This can be seen pretty commonly by finding the same SPID more than once in the result set of sp_who2.
It is easy to get to happen by using a sub query in a select statement that joins the main query. SQL will use another thread to process the sub query to speed up the process.
He is trying to prevent two people from running the same procedure at the same time. If they both update the same records, they will be prevented from running at the same time automatically by the SQL locking mechanisms, but he is looking for a complete prevention of the situation. The situation may not actually call for this (rarely should it), but I simply provided a way to do it (certainly not the only way, I must add).
Transaction isolation levels allow you to adjust what SQL does when it encounters a lock on an object. For instance, allowing READ UNCOMMITTED will tell SQL to read data from uncommitted transactions rather than waiting for the transaction to complete. This will allow you to see records before they are committed making it possible to get a result from a record that will be rolled back (typically not good to do).
April 12, 2007 at 8:31 am
Before I get beat up, I want to add that the above post was not a full description of how parallelism, or transaction isolation levels work. I was just trying to iiiustrate that that are not specifically designed for this situation.
April 12, 2007 at 8:42 am
Micheal, what would happen if he were to set the TRANSACTION ISOLATION LEVEL TO SERIALIZABLE within the stored procedure? Would that not give him the effect he desires?
April 12, 2007 at 8:49 am
Or even better still
SELECT a.au_id, title_id
FROM dbo.titleauthor a WITH (HOLDLOCK, UPDLOCK)
INNER JOIN dbo.authors b ON a.au_id = b.au_id
Which is baiscally the same thing as setting the TRANSACTION ISOLATION LEVEL TO SERIALIZABLE but just more fine tuned.
April 12, 2007 at 8:53 am
CREATE PROC MyProcedure
AS
IF Object_ID('tempdb..##MyProcedureTable') IS NULL
CREATE TABLE ##MyProcedureTable (
...
)
ELSE
RETURN
{SP body}
DROP TABLE ##MyProcedureTable
GO
As soon as last connection using ##MyProcedureTable will be closed this table will be dropped automatically.
_____________
Code for TallyGenerator
April 12, 2007 at 9:55 am
A select with a HOLDLOCK or using transaction isolation levels will work if the select command is held open. The procedure is running more than one command so the lock on the table will release when the select is completed.
You will also have locked a real resource which may not be what he wants. Should this procedure lock everything any select against the table?
I like this global temp table idea - it is very simple - good thinking.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply