Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 4th, 2006, 09:49 AM
Friend of Wrox
Points: 1,243, Level: 13
Points: 1,243, Level: 13 Points: 1,243, Level: 13 Points: 1,243, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2003
Location: , , United Kingdom.
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default CONCURRENCY

Hi,

My knowledge in this area is quite limited and I would like some help to decide what to do next.

This is the problem:

I need an order number counter with company wide scope, that is incremented by one, each time a user in the company requests the next available number.

However, after some thought I think I could have a concurrency issues if it is a company wide field. Suppose that 2 people tried to get a counter (update) at the same time? Don’t you think that this would be a problem?

My conclusion was that if the entire operation of retrieving and incrementing the counter is done in a single stored procedure then that operation is protected. In other words that will complete before another one can be attempted, so further requests will be effectively queued. Is that right ??

IS THIS A SOLUTION OR NOT? IF NOT CAN SOMEONE HELP WITH SOME IDEAS AND SOURCE CODE IF POSSIBLE?

Cheers,

CPall


Reply With Quote
  #2 (permalink)  
Old December 4th, 2006, 01:18 PM
Registered User
 
Join Date: Dec 2006
Location: london, , United Kingdom.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am not the best guy to help here but why don't you use dynamic SQL statements? This means as many people as you like can get a new order number anytime they want as dynamic SQL statements will just open a new work area within the DB and get a new number.

Secondly i doubt there will be a problem with the time issue if you use dynamic SQL statements.

I am new to SQL and thus have avoided sPRoc's so far and have created some pretty complicated programs, which are mostly secure too... You don't NEED a Sproc to do this i'm sure. I could be wrong though, maybe one of the experts on here will say i'm wrong...

When **** becomes valuable the poor will be born without *******s.
Reply With Quote
  #3 (permalink)  
Old December 4th, 2006, 01:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

There is indeed a potential concurrency problem as you correctly point out.

However, using a stored procedure, or dynamic SQL as another suggested, will NOT solve this problem.

Presumably, you'll store the last order number assigned in a table, then whenever you need another order number, you add 1 to that value, store that updated value in the table and return it to the caller. To do that, you'll need two SQL statements, one to UPDATE the counter, and a SELECT to retrieve the updated value. There are any number of ways to do this, but for this discussion let's assume we have a table that contains a single row and a single column which contains the last order number assigned. Thus, the statements to assign a new order number might be:

    UPDATE OrderSequence SET LastOrderNumber = LastOrderNumber + 1;
    SELECT LastOrderNumber FROM OrderSequence;

and thus the concurrency issue shows its ugly head because another user could get in between those two statements.

The only way to prevent this is to execute these two statements within the context of a transaction, as:

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    BEGIN TRANSACTION
    UPDATE OrderSequence SET LastOrderNumber = LastOrderNumber + 1;
    SELECT LastOrderNumber FROM OrderSequence;
    COMMIT TRANSACTION

this guarantees that no other user can be updating and then reading the order number value while another user is attempting the same thing.

The REPEATABLE READ isolation level insures that a lock is obtained on the row being updated, and no other user can obtain that lock until it is released via the COMMIT statement.

I think it's better to control the transaction and it's COMMIT (or ROLLBACK) from within the Client code, rather than within TSQL (error handling is WAY better) but YMMV.

Jeff Mason
Custom Apps, Inc.
je.mason@comcast.net
Reply With Quote
  #4 (permalink)  
Old December 4th, 2006, 04:16 PM
Friend of Wrox
Points: 1,243, Level: 13
Points: 1,243, Level: 13 Points: 1,243, Level: 13 Points: 1,243, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2003
Location: , , United Kingdom.
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default

Hi Jeff, N4th,

Many thanks to your reply and help.

It is much clearer now what I have to do.

The reason why I have to use a Stored Procedure to do this is that since the beginning we are using this design. We have a C# class responsible to do all inserts and updates, deletes etc. and we need to manitain consistency.

Therefore, I will have to use a stored procedure for that.

I have a table called tblCompany and it looks something like this:

id companyName Address insertionCounter
1 company1 1 r street 3
2 company2 2 r road 1
3 company2 22 s road 100

Below this message, You will find an extract of my C# code that uses a command object and add parameters to be sent to the stored procedure in the database.

Jeff you said:

"The REPEATABLE READ isolation level insures that a lock is obtained on the row being updated, and no other user can obtain that lock until it is released via the COMMIT statement."

That is exactly what I need because the row being updated is determined by the company ID so it means if another user from another company want to update its counter then I will not have any problems, right ??

Please correct me if I am wrong!!!!

Also, you said that it is better to control the transaction and it's COMMIT (or ROLLBACK) from within the Client code, rather than within TSQL (error handling is WAY better).

Please could you have a look at my C# code and point me out what I have to do in other to control the the transaction in my code.

I presume that the sql statement in the stored procedure will then look something like this:

UPDATE OrderSequence SET LastOrderNumber = LastOrderNumber + 1;
    SELECT LastOrderNumber FROM OrderSequence;

and then this:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION

COMMIT TRANSACTION

But what about the ROLLBACK ??

I would appreciate very much if could help me.

Cheers

CPall

C# Code

[CommandGenerator.SqlParameter("UserId")] SqlInt32 ID
)
{
try
{
   //Create SQL command object
   SqlCommand command = CommandGenerator.SqlCommandGenerator.GenerateComma nd
   (sqlConn, (MethodInfo)MethodBase.GetCurrentMethod(),
   new object[] { userID});

   XmlDocument xmlDoc1 = new XmlDocument();

   //return results of SQL command
   return xmlDoc = SQLExecuter.ExecuteQuery(command, false);
}
catch (Exception ex)
{

 //Return name of class
 return XML.CreateXMLDocFromString("<error>" + this.GetType().ToString() + "</error>");
}
}
Reply With Quote
  #5 (permalink)  
Old December 4th, 2006, 11:39 PM
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
Thanks: 0
Thanked 9 Times in 9 Posts
Default

First of all, I don't understand why you aren't just using an IDENTITY column... it has the least likely chance of a concurrency issue among ALL the proposed methods show so far.

Second, do not use Update/Select in a transaction to get an ID from a table... you will create more deadlocks on this hotspot than you can possibly imagine.  We had a nearly identical issue at work with 3rd party code that caused 4,000 (NOT a misprint) deadlocks per day.  We fixed the problem with code similar to the following (NOTE THE ABSENCE OF A DECLARED TRANSACTION!!!)...

Code:
UPDATE dbo.OrderSequence
   SET @NextID = LastOrderNumber = LastOrderNumber + 1
Of course, @NextID is an output variable for a stored proc and we didn't actually use "+1"... we used a variable to pass an increment so we could also do things in a set based manner.



--Jeff Moden
Reply With Quote
  #6 (permalink)  
Old December 5th, 2006, 05:22 AM
Friend of Wrox
Points: 1,243, Level: 13
Points: 1,243, Level: 13 Points: 1,243, Level: 13 Points: 1,243, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2003
Location: , , United Kingdom.
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default

Hi Jeff,

We already have an IDENTITY field in the table. It is the ID field so that every company has a unique id. I did not quite understand your comments regarding the not adding a 1 and passing a variable to increment the counter.

1 - Where do you keep your old counter to be updated?

2 - So this would be the stored procedure sql - UPDATE dbo.OrderSequence
   SET @NextID = LastOrderNumber = LastOrderNumber + 1

I assume LastOrderNumber is the name of the counter field in the table. Could you please explain the last bit of this expression to me

SET @NextID = LastOrderNumber = LastOrderNumber + 1

Are you assigning the current value of the LastOrderNumber + 1 to the output variable?

I would appreciate if you could elaborate a bit more on you explanation since this is new for me.

Cheers,

Claudio
Reply With Quote
  #7 (permalink)  
Old December 5th, 2006, 06:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by Jeff Moden
 Second, do not use Update/Select in a transaction to get an ID from a table... you will create more deadlocks on this hotspot than you can possibly imagine.  We had a nearly identical issue at work with 3rd party code that caused 4,000 (NOT a misprint) deadlocks per day.

With all due respect, the Update/Select done within the context of a well defined transaction will not cause a deadlock as long as all accesses to acquire an order number are done in the same way - that is, the Update then the Select (or even the other way around, as long as its consistent), and there are no other update/select's for other purposes done elsewhere to the table which would acquire the same lock.

You can't get a deadlock no matter how many processes are involved if the only resource being locked is a single row in a single table...

Your third party code must have been poorly written (i.e. improperly organized or using sloppily defined transactions) for deadlocks to occur if indeed your situation was 'nearly identical'.

As to using an IDENTITY, many times those values aren't fit for external consumption, and a more human-friendly 'sequential' value is more desirable for external things like an order number, case number, transaction number, ticket number, etc. IDENTITY values are good internally for surrogate keys, and should not be exposed to the outside world.

I recognize such talk may ignite a 'whither IDENTITY' religious war, so that's IMO...;)

Jeff Mason
Custom Apps, Inc.
je.mason@comcast.net
Reply With Quote
  #8 (permalink)  
Old December 5th, 2006, 08:15 AM
Friend of Wrox
Points: 1,243, Level: 13
Points: 1,243, Level: 13 Points: 1,243, Level: 13 Points: 1,243, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2003
Location: , , United Kingdom.
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default

Hi Jeff

Thanks again.

So I have decide to use this stored procedure and the C# code below which is working fine.

Do you think this will not cause any concurrency issues?

CREATE PROCEDURE dbo.asp_GetNextOrderNumber
(
    @CompanyId int,
    @NextON int output

)
 AS

UPDATE tblCompany SET @NextON = NextOrderNumber = NextOrderNumber + 1
WHERE CompanyID = @CompanyId

GO

With this C# Code:

cmd.Connection = sqlConn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "asp_GetNextOrderNumber";
cmd.Parameters.AddWithValue("@CompanyId", companyId);
cmd.Parameters.Add("@NextON", SqlDbType.Int);
cmd.Parameters["@NextON"].Direction = ParameterDirection.Output;
sqlConn.Open();

cmd.ExecuteNonQuery();
int returnID = int.Parse(cmd.Parameters["@NextON"].Value.ToString());
sqlConn.Close();

Cheers,

CPall
Reply With Quote
  #9 (permalink)  
Old December 5th, 2006, 12:58 PM
Friend of Wrox
Points: 1,243, Level: 13
Points: 1,243, Level: 13 Points: 1,243, Level: 13 Points: 1,243, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2003
Location: , , United Kingdom.
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default

Hi Jeff,

Could you please answer my previous message. Do you think I am safe with this code?

Cheers

CPALL
Reply With Quote
  #10 (permalink)  
Old December 5th, 2006, 02:59 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If I am understanding the request I think the requestor is trying to replicate the functionality of the Sequence which is available in other SQL RDBMS's like Oracle or Postgres. But not available in MS-SQL. In the rare situations where I want to replicat this functionality. I use a table with an identity value that incriments. Then say I have a customer table. When I want to do an insert to the customer table I will first do an insert into this special global counter table, then take that value an store it into the customer table along with another identity value in the customer table.

For example insert into global counter table, return identity value.

Take that global counter identity value and insert into customer table. When I select the row from the customer table I get back both a customer identity value and a global counter identity value.

Hope this helps, not sure I understand the question though.

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
concurrency problems Dorean Visual Studio 2005 1 September 4th, 2007 10:38 AM
Concurrency issues vitthala ASP.NET 2.0 Basics 3 October 18th, 2006 11:42 AM
Concurrency problem sanjaymannnet ASP.NET 1.0 and 1.1 Professional 0 July 10th, 2006 05:42 AM
Concurrency Issue billy_bob_the_3rd Classic ASP Components 1 October 23rd, 2004 09:33 AM
Concurrency dextertoh Classic ASP Databases 2 May 17th, 2004 12:53 AM



All times are GMT -4. The time now is 11:50 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.