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

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.

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.
[email protected]

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>");
}
}

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!!!)...

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

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

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.
[email protected]

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

Hi Jeff,

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

Cheers

CPALL

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.