Generate Next Numbers with SQL Server
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists
My Blog:
http://dineshasanka.spaces.live.com/ -
I looked at your proc, and have a few concerns.
1) You state that it handles the multi-user environment by generating and updating the next number in the same stored procedure. Incorrect. Your not using transactions. Under a heavy load it is possible for two users to select from the table before either updates it
2) Use of dynamic sql. Come on, do you really need to use it here!
Sorry, but I think that there is plenty of room for improvment here.
Cheers,
Kevin -
Thankx for the reply
1. My concern is not about the accessing. but getting same no for both users.
Say User A Read and get number 15 before updating it user B also gets 15
I want to stop that.
2. What do u mean by dynamic sql.
and do u have any other way of doing this. because I'm looking for this kind of solutions
Thankx again for the reply
My Blog:
http://dineshasanka.spaces.live.com/ -
This part is Dynamic SQL:
Select @sql = "Update " + @Tablename + " Set LastNumber ="
+ Cast(@LastNumber as varchar(10)) +
" Where CODE ='" + @secondType + "'"
execute (@sql)
-
You need to have the "selecting, incrementing & updating" process happen for one person at a time. You can use a transaction and locking to do this, for example...
--------------------------------------------------------------------------------------begin transaction
--------------------------------------------------------------------------------------
--Lock the Table
select @RowCount = count(*) from SEQ_NO with (TABLOCKX)
--Get the Value
Select @LastNumber = LastNumber From SEQ_CHAN Where CODE = @secondType
--Increment the Value
Select @LastNumber = @LastNumber + 1
--Update the Value
Update SEQ_NO Set LastNumber = @LastNumber Where CODE = @secondType
--------------------------------------------------------------------------------------
commit transaction
--------------------------------------------------------------------------------------
Alternatively, without using a transaction at all, add the old value of the "Next Number" to the where clause of the update and check the rowcount. If you haven't updated any rows then you need to get the next number.
Cheers,
Kevin -
My argument is if both users are using same type. they will get same number there can be duplicate number unless u update it just after reading.
My Blog:
http://dineshasanka.spaces.live.com/ -
What? Sorry, but I'm not sure of your requirement.
Are you saying that if two users are using the same type, it is OK for them to have duplicate numbers?
Cheers,
Kevin -
NO SIR,
If I didn't update just after reading there is chance of getting same number for both records.
To avoid that I am updating the Next number Just after reading.
My Blog:
http://dineshasanka.spaces.live.com/ -
By putting the update straight after the read is enough. You need to do more. Check this script out to illustrate my point...
use tempdbcreate table Numbers (LastNumber int not null, primary key(LastNumber))
insert into Numbers values (0)
Now create 2 connections to the database and run the following script on each one at the same time. (This will simulate 20000 requests for next number in a short period of time)
set nocount ondeclare @LastNumber intdeclare @NewNumber int
declare @Count int
declare @Index int
select @Count=10000, @Index=1while (@Index <= @Count)begin
Select @LastNumber = LastNumber From Numbers
Select @NewNumber = @LastNumber + 1Update Numbers Set LastNumber = @NewNumberselect @Index= @Index + 1end
Select LastNumber From NumbersWhen I run this on my local tempdb, I get the result as 17209. This means that there were 2791 duplicate next numbers generated! Obviously you may not have the demand of 20000 instantaneous requests, but it only needs to happen once to corrupt your data integrity.
Now change the script to either use transactions, or include the LastNumber in a where clause of the update statement (as mentioned in my previous post) to correct the problem.
Cheers,
Kevin -
I got your point now
Thankx
I thought u were saying some error in my method.
your request is an improvement which is welcome.
And for second question
y are against with dynamic SQL
in my case I have to use it. What are the altenatives u have.
Thankx again for the reply
My Blog:
http://dineshasanka.spaces.live.com/ -
I can't offer you an alternative, as I don't know your business requirements.
I don't know how flexible your system needs to be, but when I saw that you were getting the tablename from the next number table, I just didn't see the point. Why the extra table in the first place?
Cheers,
Kevin -
While briefly reviewing your code I saw something funny. You are using varchar for date variable. This may work, but you mus be aware that SQL Server has to do an implicit conversion. Every time you fill getdate() to varchar it is converted according to local server settings. When you use YEAR(), it is converted back to datetime.
Much simpler solution: use datetime type for date varable, even as stored procedure parameter. You can call the procedure and send it specially formatted string that will be converted to datetime on entry. This format is called XML Date (see BOL) and it looks like this: yyyy-MM-ddThh:mm:ss (2004-04-05T00:00:00): This format is correctly converted to the right datetime no matter how local language, dateformat on the server is set.
You should also use transaction to isolate what you are doing. And very important is explicit table or at least record locking to prevent some other process from reading the last number before you updated it. Follow the examples mentioned by others.
Otherwise, how can you be sure, vou aren't getting duplicate numbers? Do you have a unique constraint on the numbering field?
Best regards, Jani
-
Also note you can do assignments like so.
update tblName set @var = col = col + 1
Which means you can read and write the data at the same time. This will help prevent the chance that more than one person can read the same value since you can do the set for all of your columns and variables at the same time on your numbering table.
Use this every so often myself.
The problem with the multiple selects as pointed out is unless you hold locks during the whole process you can potentially have more than one person get the same value in a multiuser process.
The downside to holding the lock thou is that it creates a slowdown in the number of items you can process at one time.
-
--Lock the table;
DECLARE @integer_variable int
IF @@trancount = 0
Begin Transaction;
-- Perform Update
Update table
Set Tablename.Seqnr = Tablename.Seqnr + 1
Where Blabla
--Perform Errorhandling !!! (no code inhere)
-- Perform SELECT
Select seqnr
FROM table
Where blabla.
-- Perform Errorhandling !!!
If okay
begin
COMMIT Transaction;
return;
end
IF not okay
begin
ROLLBACK TRANSACTION;
return(ERROR)
END
-
I agree with the locking -- you have to use transaction. However, sometimes dynamic SQL is necessary -- it depends on the situation.
However, the question I have is: Did the customer fully understand the cost associated with the inflexibility of changing their current number system?
I know most customers hate change, but sometimes it is necessary as the benefit does not outweight the cost. For example, I ran into the same type of problem with numbering while a project manager for an implementation. The users did not want to change, but when explained to them the cost of modification in relation to what they would get, they decided to change.
Had I not insisted to the General Manager that the gain was not worth the cost, I would have been negligent in my job. Even as a consultant, I feel that we have to point out the best possible solution to the management in a way that focuses on a cost-to-benefit ratio. Even though this may lose you some money, I believe it promotes positive attitudes between the parties and potentially more income in the long run.
Regards,
Joe Johnson
NETDIO,LLC.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply