July 13, 2004 at 12:43 pm
I might have posted this more than once today because of some problem with my connection. Apologies.
Due to lack of Sequence (as in Oracle) in SQL, we use a 3-col table XSEQUENCE (sequence_id, sequence_name, next_value) to do the trick.
The application uses a stored procedure GetNextValue to retrieve the next available sequence number. In the same sp, the retrieved sequence is incremented by 1.
We have about 30 sites running this application. At some sites (both SQL7 and SQL2k on server with 4GB RAM and 4 CPUs), the application is reporting Lock Timeout (1000 milliseconds) error on executing GetNextValue. So we think that it's a DB-performance issue rather than an application issue. The application has been around for long time.
My questions are:
1. why would executing an sp as simple as this (see below) cause a lock timeout? At any given time, there could be 20 or more requests to execute this sp. Does this mean the Db is having problem serving these concurrent requests? I occassionally see locks on the XSEQUENCE table.
2. The XSEQUENCE table contains about 83 sequences indexed on sequenceid. The application at most times uses only 3-4 sequences from this table. The sp reads and updates the table. Would it help if we pin the table into memory? I know I should find out what is causing the lock first. But from the trace I'm using, is only says a specific SPID, executing GetNextValue, is causing a block or being blocked.
All suggestions appreciated. Thanks.
Here is the SP:
create procedure GetNextValue
@SequenceID int out,
@NextValue int out,
@SequenceName varchar(30) out
as
begin transaction
select @NextValue = NextValue, @SequenceName = SequenceName
from XSequence WITH (UPDLOCK)
where SequenceID = @SequenceID
update XSequence set NextValue = NextValue + 1
where SequenceID = @SequenceID
commit transaction
GO
July 13, 2004 at 1:24 pm
RE: 1. why would executing an sp as simple as this (see below) cause a lock timeout? At any given time, there could be 20 or more requests to execute this sp. Does this mean the Db is having problem serving these concurrent requests? I occassionally see locks on the XSEQUENCE table.
2. The XSEQUENCE table contains about 83 sequences indexed on sequenceid. The application at most times uses only 3-4 sequences from this table. The sp reads and updates the table. Would it help if we pin the table into memory? I know I should find out what is causing the lock first. But from the trace I'm using, is only says a specific SPID, executing GetNextValue, is causing a block or being blocked.
3. All suggestions appreciated. Thanks
----
Just a few thoughts...
1. One would probably want to monitor the locking to try to determine what is occuring.
2. Pinning the table likely would not address the issue described.
3. One might consider testing performance with RowLock vs. UpdLock?
July 14, 2004 at 11:36 am
We have a very similar scenario. Our procedure that updates the 'xsequence' table does both the get value and the update in one statement.
update serialno
set @tmpSerno = serialno, serialno=serialno+1
where sfname = @vcFldname
and dbid = @db_dbid
Try it this way and see if it helps you any.
July 14, 2004 at 5:01 pm
Thank you both, DrChips and Eagoss. I'm going to pass this onto the developer. Will let you know how it goes although it may take a few days.
July 14, 2004 at 6:15 pm
Eagoss, it's a very elegant solution. Our developer would like to know whether you experienced similar locking problem in your situation before you derived this solution. The reason is we can't replicate the locking problem in the lab and will have to try it on a production system. So we'd like to know how effective it was for you. Thanks.
July 15, 2004 at 8:53 am
Our procedure has been working for us for several years. I've worked here 4 years and haven't had any reports of locking issues with the SQL Server version of this procedure (we did have one with the Oracle version, but that one is written slightly differently).
I didn't actually write the procedure, so I don't know if they had issues prior to it or not. However, we have about 20 clients all using either the Oracle or SQL Server version. No one's complained..
Something else you may want to consider is that ALL updates into the table should be done through the stored procedure. It will help in avoiding duplicate values to be pulled. We allow new SFNAMES (Serialno Field Names) to be added by calling the same procedure. If the record doesn't exist, it inserts it and returns a 1.
Glad I could help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply