Sequence number issue

Jonny Bergdahl (jonny.bergdahl@newsgroup.nospam) writes:
> I have a table containing some sequence numbers that are being used by a
> stored procedure. The procedure reads the current value, increases it,
> chacks that it is below athe max allowed value and returns the number.
> If the max value is passed, it checks the "allow wrap" flag, and if set
> resets the value to the minimum value and returns that value (if wrap is
> not allowed, an error is returned).
>
> This procedure is called by .NET code that inserts data to several
> tables, and for some of them calls the stored procedure to get the value
> to be inserted. This code runs in a transaction (TransactionScope). This
> code runs as a result of a web service call.

What isolation level do you get with TransactionScope? Check that you
don't have serializable, because this level is very prone to deadlocks.
Since you appear to have a fixed number of rows in the table, REPEATABLE
READ should be enough. But you may also need to add an UPDLOCK hint
when you read the value to avoid conversion deadlocks.

> Now I only need a solution to the problem. The first thing I would
> change is to run the procedure outside of the transcation, that way the
> update of the sequence table would not cause it to be locked as part of
> the (long running) transaction. I would instead need to lock the
> sequence table inside the procedure and release that lock when the
> procedure is done.

You could have a session-level application lock, but I don't think
that is the right answer.



--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Sequence number issue 

Hi Jonny,


Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that when running the procudure, dead
lock occured.If I have misunderstood, please let me know. That will help us
resolve the issue quickly.

in order to address your concern, I would like to explain the following.
1 based on the description, the issue might be related to the T-SQL to
create the procedure. So please tell me the code of the produce.

2 After reading the public response, the suggestion of Erland makes sense.
Thank you, Erland. besides, there is 2 articles to share with
you:http://msdn.microsoft.com/en-us/library/ms173763.aspx
http://msdn.microsoft.com/en-us/library/ms177523.aspx, please read the
Table_Hint_Limited part.

3 To better understand the issue, we need to capture some information. my
email is v-fathan DeleteThis @online.microsoft.com(remove online). if it is convenient
to you, please email me, I will create a workspace for you to upload some
required files.

Besides, about deadlock issue, there are some realted articles to share
with you:
Analyzing and Avoiding Deadlocks in SQL
Server:http://support.microsoft.com/kb/169960

How to Track Down Deadlocks Using SQL Server 2005
Profiler:http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-d
eadlocks-using-sql-server-2005-profiler/

SET DEADLOCK_PRIORITY (Transact-SQL):
http://msdn.microsoft.com/en-us/library/ms186736.aspx

Hope the above helpful. Have a nice day.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg DeleteThis @microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 >> Stay informed about: Sequence number issue 

Sorry for the late reply, but better late that never.

The procedure is defined as below. If I have understood it correctly, the
deadlock occurs when more than one thread has executed the SELECT clause and
then tries to escalate the lock for the UPDATE clause. I am not certain
about what lock type I'll get from FOR UPDATE in the SELECT clause.

Anyway, I want to have an exclusive lock on the table (or better, the table
row) for the duration of the procedure execution. As the result is a
sequence number that needs to be unique, it really needs to be serialized.
Can I simply change the SELECT clause in order to get a row lock, perhaps?

Also note that this table is never accessed by anything other that this
procedure. The number of rows in the table is less than 20, and the
different rows in the table is used by different threads, so row locking
would perhaps increase throughput.

Regards;
/jb

------------
CREATE PROC GetNextSequenceNumber
@sequenceType int
AS
DECLARE @currentValue decimal(20,0)
DECLARE @minValue decimal(20,0)
DECLARE @maxValue decimal(20,0)
DECLARE @enableWrap bit
DECLARE @width int
DECLARE @result varchar(20)

DECLARE SeqCursor CURSOR
LOCAL
SCROLL_LOCKS
FOR
SELECT CASE
WHEN CurrentValue >= MinValue
THEN COALESCE(CurrentValue,MinValue-1)
ELSE MinValue-1
END
AS CurrentValue,MinValue,MaxValue,EnableWrap,Width
FROM SequenceNumbers
WHERE SequenceType=@sequencetype
FOR UPDATE

OPEN SeqCursor

FETCH NEXT FROM SeqCursor
INTO @currentValue,@minValue,@maxValue,@enableWrap,@width

if @@ROWCOUNT = 0
begin
set @currentValue = NULL
raiserror('Sequence does not exist ',12,1)
GOTO EXIT_PROC
end

if @currentValue<@maxValue
set @currentValue = @currentValue+1
else if @enableWrap=1
set @currentValue=@minValue
else
begin
set @currentValue = NULL
raiserror('Reached max value and wrap is not allowed',12,1)
GOTO EXIT_PROC
end

UPDATE SequenceNumbers
SET CurrentValue = @currentValue
WHERE CURRENT OF SeqCursor

EXIT_PROC:
Close SeqCursor
deallocate SeqCursor

set @result = CONVERT(varchar(20),@currentValue)
select (REPLICATE('0', @width - LEN(@result)) + @result)

GO
------------

"Erland Sommarskog" <esquel DeleteThis @sommarskog.se> skrev i meddelandet
news:Xns9B6B33FBA2A8Yazorman@127.0.0.1...
> Jonny Bergdahl (jonny.bergdahl@newsgroup.nospam) writes:
>> I have a table containing some sequence numbers that are being used by a
>> stored procedure. The procedure reads the current value, increases it,
>> chacks that it is below athe max allowed value and returns the number.
>> If the max value is passed, it checks the "allow wrap" flag, and if set
>> resets the value to the minimum value and returns that value (if wrap is
>> not allowed, an error is returned).
>>
>> This procedure is called by .NET code that inserts data to several
>> tables, and for some of them calls the stored procedure to get the value
>> to be inserted. This code runs in a transaction (TransactionScope). This
>> code runs as a result of a web service call.
>
> What isolation level do you get with TransactionScope? Check that you
> don't have serializable, because this level is very prone to deadlocks.
> Since you appear to have a fixed number of rows in the table, REPEATABLE
> READ should be enough. But you may also need to add an UPDLOCK hint
> when you read the value to avoid conversion deadlocks.
>
>> Now I only need a solution to the problem. The first thing I would
>> change is to run the procedure outside of the transcation, that way the
>> update of the sequence table would not cause it to be locked as part of
>> the (long running) transaction. I would instead need to lock the
>> sequence table inside the procedure and release that lock when the
>> procedure is done.
>
> You could have a session-level application lock, but I don't think
> that is the right answer.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
 >> Stay informed about: Sequence number issue 

Jonny Bergdahl (jonny.bergdahl@newsgroup.nospam) writes:
> The procedure is defined as below. If I have understood it correctly,
> the deadlock occurs when more than one thread has executed the SELECT
> clause and then tries to escalate the lock for the UPDATE clause. I am
> not certain about what lock type I'll get from FOR UPDATE in the SELECT
> clause.
>
> Anyway, I want to have an exclusive lock on the table (or better, the
> table row) for the duration of the procedure execution. As the result is
> a sequence number that needs to be unique, it really needs to be
> serialized. Can I simply change the SELECT clause in order to get a row
> lock, perhaps?
>
> Also note that this table is never accessed by anything other that this
> procedure. The number of rows in the table is less than 20, and the
> different rows in the table is used by different threads, so row locking
> would perhaps increase throughput.


> DECLARE SeqCursor CURSOR
> LOCAL
> SCROLL_LOCKS

SCROLL_LOCKS? I definitely do not know what means or implies. And I
don't think I want to know either. In any case, I don't see the point
with the cursor.

I think should be better:

BEGIN TRANSACTION

SELECT @currentValue = CASE WHEN CurrentValue >= MinValue
THEN COALESCE(CurrentValue,MinValue-1)
ELSE MinValue-1
END,
@minValue = MinValue, @maxValue = MaxValue,
@EnableWrap, = EnableWrap = @width = Width
FROM SequenceNumbers WITH (UPDLOCK)
WHERE SequenceType=@sequencetype

if @@ROWCOUNT = 0
begin
set @currentValue = NULL
raiserror('Sequence does not exist ',12,1)
GOTO EXIT_PROC
end

if @currentValue<@maxValue
set @currentValue = @currentValue+1
else if @enableWrap=1
set @currentValue=@minValue
else
begin
set @currentValue = NULL
raiserror('Reached max value and wrap is not allowed',12,1)
GOTO EXIT_PROC
end

UPDATE SequenceNumbers
SET CurrentValue = @currentValue
WHERE SequenceType=@sequencetype

COMMIT TRANSACTION

EXIT_PROC:
IF @@trancount > 0 ROLLBACK TRANSACTION

set @result = CONVERT(varchar(20),@currentValue)
select (REPLICATE('0', @width - LEN(@result)) + @result)

You should make sure there is an index on SequenceType, or else the
entire table will be locked.

The UPDLOCK serves as a serialisation point, only one process at a
time can have this lock.

--
Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Sequence number issue 

> SCROLL_LOCKS? I definitely do not know what means or implies. And I

Yes, you see my problem. Not my code, and not my programming language.

> You should make sure there is an index on SequenceType, or else the
> entire table will be locked.

SequenceType is the sole primary key, so I guess I won't need an extra idex
for this?

Thank you very much for your input. I will try out the proposed solution.

Regards;
/jb
 >> Stay informed about: Sequence number issue 

Jonny Bergdahl (jonny.bergdahl@newsgroup.nospam) writes:
>> SCROLL_LOCKS? I definitely do not know what means or implies. And I
>
> Yes, you see my problem. Not my code, and not my programming language.
>
>> You should make sure there is an index on SequenceType, or else the
>> entire table will be locked.
>
> SequenceType is the sole primary key, so I guess I won't need an extra
> idex for this?

That should do. I wanted to point this out. Since this is such a small
table, someone might have believed that there is no need for indexes at all.




--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Sequence number issue 

Hi Jonny,

Thank you for the reply.

After reviewing the responce to you, I found Erland had made a good sample
for you. Thank you, Erland .

Besides, in order to address your concern, I would like to answer the
following question.
Q: SequenceType is the sole primary key, so I guess I won't need an extra
idex for this?
A: When you specify a PRIMARY KEY constraint for a table, the Database
Engine enforces data uniqueness by creating a unique index for the primary
key columns. This index also permits fast access to data when the primary
key is used in queries

There is an article to share with
you:http://msdn.microsoft.com/en-us/library/ms191236.aspx

If you have any questions on the above, please let me know. Thanks.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg.TakeThisOut@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 >> Stay informed about: Sequence number issue 

On Dec 4, 9:31 am, "Jonny Bergdahl" <jonny.bergd... RemoveThis @newsgroup.nospam>
wrote:
> I have a table containing some sequence numbers that are being used by a
> stored procedure. The procedure reads the current value, increases it,
> chacks that it is below athe max allowed value and returns the number. If
> the max value is passed, it checks the "allow wrap" flag, and if set resets
> the value to the minimum value and returns that value (if wrap is not
> allowed, an error is returned).
>
> This procedure is called by .NET code that inserts data to several tables,
> and for some of them calls the stored procedure to get the value to be
> inserted. This code runs in a transaction (TransactionScope). This code runs
> as a result of a web service call.
>
> Now to the problem:
> When we run load testing on the server, some of the transactions gets
> aborted by SQL Server as deadlocks. I can understand how that can happen as
> I have several different threads (>20) running at the same time, and calling
> the procedure at different times.
>
> Now I only need a solution to the problem. The first thing I would change is
> to run the procedure outside of the transcation, that way the update of the
> sequence table would not cause it to be locked as part of the (long running)
> transaction. I would instead need to lock the sequence table inside the
> procedure and release that lock when the procedure is done.
>
> As I have never programmed in TSQL I have no idea how to do this, or if
> there is a better way to solve the problem. Any advice on this matter would
> be appreciated.
>
> Regards;
> /jb

One thing is to make sure you run it under READ COMMITTED:

http://www.simple-talk.com/sql/t-sql-programming/close-those-loopholes...ress-te

Another thing: if you tolerate gaps, you can allocate numbers in
batches rather than one by one.
 >> Stay informed about: Sequence number issue 

Hi Jonny,

This is Mark. I'm writing to follow the issue.

If I can assist you anything related to the issue, please let me know.

I look forward to hearing from you.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg.TakeThisOut@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and c
 >> Stay informed about: Sequence number issue