SQL Server 2008 stored proc running concurrently causing delays

Question

I have a problem where it seems that when running the same stored procedure at the same time from two different instances of the same application it is timing out, and wondered if there was anything I could do to resolve it?

I believe the problem is in the way SQL Server 2008 handles itself, locking rows and executing the SPs...things I don't really know a whole lot about. The app uses ADODB.Command to execute the SP.

I have one VB6 exe (App.exe), running on the one server multiple times. This app calls a stored proc on the database which returns the next sequence number for that app. This sequence number field is unique for the instance of the application - there is 1 row in a table (tbl_SequenceNos) for each instance of the running application.

So for example, say we have running: App[a].exe and App[b].exe

tblSequenceNos looks like:

iAppNo| iNextSequenceNo
  a   |     1234 
  b   |     4567

The stored procedure to get the next sequence number is relatively simple:

CREATE PROEDURE GetNextSequenceNo (@AppNo varchar(1), @NextSequenceNo int output)
AS
BEGIN
    DECLARE @TempSequenceNo int

    SELECT @NextSequenceNo = iNextSequenceNo 
    FROM tblSequenceNos 
    WHERE iAppNo = @AppNo

    @NextSequenceNo = @NextSequenceNo + 1

    UPDATE tblSequenceNos 
    SET iNextSequenceNo = @NextSequenceNo
    WHERE iAppNo = @AppNo

END

When both App[a].exe and App[b].exe try to run this procedure to get their NextSequenceNo value, they are hanging for about 30Secs (ADO timeout?).

Because Each app never looks at the each others row, I thought that this would work concurrently without specifing an special Locking. Is there something I am missing? I thought perhaps I need to specify to lock the row only, not the whole table or Page? - I do not know what sql2008 does by default.

Any help is greatly appreciated! Thank you in advance Andrew

score 2 · Accepted Answer · answered Dec 01 '11 at 10:58

2

Your procedure is not thread safe and will produce incorrect results because between the select and the update multiple threads might get the same sequence nr.

CREATE PROCEDURE GetNextSequenceNo (@AppNo varchar(1), @NextSequenceNo int output)
AS

 DECLARE @var table(seq int);

 UPDATE tblSequenceNos 
    SET iNextSequenceNo = @NextSequenceNo + 1
 OUTPUT inserted.iNextSequenceNo INTO @var;
  WHERE iAppNo = @AppNo

  select @NextSequenceNo = seq from @var
GO

Also make sure your iAppNo column is indexed. (This means an index on this column only or an index where this field is the first field in your index)

answered Dec 01 '11 at 10:58

Filip De Vos

11,568
1
48
60

+1 darn - beat me by a few seconds ! :-) That's really the best way to do it, for sure! – marc_s Dec 01 '11 at 11:02
Thanks guys :). iAppNo is the Primary key on the table. I can guarentee that the SP is not called on Multiple threads with the same @AppNo passed to it. With this in mind, would you expect locking issues? – Andrew Humphries Dec 01 '11 at 11:38
Sql Server will take page locks which can block a threads on an unrelated iappno. – Filip De Vos Dec 01 '11 at 13:00
hmm, Thats what I was thinking. Is there any way to force a row lock only? – Andrew Humphries Dec 01 '11 at 13:30
add `WITH ROWLOCK` to your query. (Note that it is only a locking hint, and sql server will still escalate the locks) – Filip De Vos Dec 01 '11 at 15:02
I was trying this kind of scenario using sp_getAppLock. But its sometimes its returning same sequence number. Is this method is better than using sp_getapplock and thread safe? – Joy George Kunjikkuru Feb 20 '14 at 14:14

SQL Server 2008 stored proc running concurrently causing delays

1 Answers1