November 4, 2012 at 9:25 am
dear friends,
I need to generate invoice number(s). To keep everything easy lets say I need to generate incremental number for each customer- starting with 1 and increment by 1. I cannot use ID column, don't ask why, but for example after delete operations I cannot take ID counter back.
I see some new functions of sql2012 and sp's when googling but my problem is:
this is a credit card transaction application. invoice number CANNOT be used 2 times, even once, everyone gets into trouble. not using one number twice is very CRITICAL. I cannot emphasize enough how critical it is.
I need reliable, known solution that I can trust. it seems to me that as this is a asp.net application and will be under heavy usage possibility of any algorithm that generates number without table locking would cause problems. -at least I think that way, maybe wrong.
how can I achieve that ? how do you deal with invoice numbers ? db will be sql2012, all new functions are welcome without compatibility concern. I can use stored procedures or use aspnet code, not important.
best regards.
November 4, 2012 at 11:39 am
I guess it all depends on what is meant by, "I cannot use ID column, don't ask why, but for example after delete operations I cannot take ID counter back."
You can use a one row control file like so...
First create the table:
CREATE TABLE [dbo].[controlFile](
[number] [bigint] NULL
) ON [PRIMARY]
Then, create a stored proc NewNumber
create procedure NewNumber
as
begin
update controlfile set number = number + 1
select number from controlfile
end
As long as you always use it inside a transaction, numbers will always be sequential and there will be no gaps. That being said, you now have to make sure everything that creates one of those numbers is in a transaction or there will be problems. If you were doing it in a website that ran on a single server, you could do it in code using the lock statement, but that wouldn't persist the key between starts and would cause problems if you went to a web farm.
November 4, 2012 at 12:10 pm
here's the question: (as I don't know sp's very well, or lest's say I have trust issues against sql server-yet)
is there any chance of 2 insert statements getting same number if they occur exactly same time ? I mean:
visiort1 presses button, application executes sp and gets generated number.
visitor2 makes same thing exactly at the same millisecond.
I'm asking this because in real life of this application its very possible the button_click event to be fired 100 times in any given second. and more than that, visitors can start "batch" operations that can runs thousands of insert statements. lets say I wrote an insert trigger and I added these generated numbers to records.
I know, if this kind of problem would be possible then sql server engine wouldn't be a real database engine but I need to confirm this:
if I wrote an insert trigger that adds invoice number to the inserted data, even if millions of transactions would be run at the exact same millisecond, sql server would handlesthem and works perfectly.
can you confirm that please ? more importantly, did you every experienced this ?
November 4, 2012 at 12:59 pm
lnardozi 61862 (11/4/2012)
I guess it all depends on what is meant by, "I cannot use ID column, don't ask why, but for example after delete operations I cannot take ID counter back."You can use a one row control file like so...
First create the table:
CREATE TABLE [dbo].[controlFile](
[number] [bigint] NULL
) ON [PRIMARY]
Then, create a stored proc NewNumber
create procedure NewNumber
as
begin
update controlfile set number = number + 1
select number from controlfile
end
As long as you always use it inside a transaction, numbers will always be sequential and there will be no gaps. That being said, you now have to make sure everything that creates one of those numbers is in a transaction or there will be problems. If you were doing it in a website that ran on a single server, you could do it in code using the lock statement, but that wouldn't persist the key between starts and would cause problems if you went to a web farm.
If you do this in a transaction, I guarantee (I've been through this before) hundreds and maybe even thousands of deadlocks per day and each deadlock will cause... a Gap! If there's a rollback, you'll still get a Gap in the data.
One way to do this is to do extreme validation of the data in the transaction to ensure that that transaction will succeed and use an IDENTITY column. Of course, even that can fail if you have a limit on the account and more than one person has a card against the account. Although not likely, it IS possible for one transaction to get in between the time you validate and the time you insert.
The best way to do this is take any and all "attempts" (using an IDENTITY column) and ensure no chance of a rollback. Then validate the transaction once it's been recorded. If the transaction is no good, then mark the transaction as "invalid" and leave it where it stands.
--Jeff Moden
November 4, 2012 at 1:15 pm
then if I have 10 customers, I have to have 10 invoice number tables for each of them to keep different invoice numbers for different customers right ?
as I understood after all reading the most reliable method is identification column.
I just tested the above solution with SQLQueryStress -which is very simple tool. 1,000,000 transaction with 200 threads.
with 1,000,000 inserts, almost 131,000 numbers shows up more than once. some of them 2 or tree, some of them even 20. (I use 16gb imac computer- which is fast)
November 4, 2012 at 1:23 pm
By "customers" I'm guessing you mean "unique sets of invoice numbers". If that is what you mean, you just need an additional column in the same table for each set of invoice numbers you want to keep. If by "customer" you mean the end buyer then I'm completely lost as to what you're trying to accomplish.
November 4, 2012 at 1:25 pm
putting the proc inside a transaction eliminates that possibility.
November 4, 2012 at 1:38 pm
first answer is yes, if I have 10 customers, I need 10 set of invoice numbers.
the transaction I use is:
declare @no int
exec CreateInvoiceNumber @no output
select @no
insert into test (no) VALUES( @no)
and this is the sp:
CREATE procedure [dbo].[CreateInvoiceNumber] (@no INT OUTPUT)
as
begin
declare @x int
update InvoiceNumbers set number = number + 1
select @x = (select number from InvoiceNumbers)
set @no = @x
end
the I look into test table to see if there are any duplicate records.
if you suggest any changes I would appreciate.
November 4, 2012 at 1:45 pm
Odd. I just tried it in the exact same tool and it performed flawlessly. You did use the proc inside another proc - and in that proc it was between a begin transaction and a commit? Unless it was part of an implicit transaction, that's what the whole ACID thing is about - either it happens or it doesn't.
November 4, 2012 at 1:49 pm
lnardozi 61862 (11/4/2012)
putting the proc inside a transaction eliminates that possibility.
Yes it does... it also guarantees deadlocks.
--Jeff Moden
November 4, 2012 at 1:51 pm
aykut canturk (11/4/2012)
as I understood after all reading the most reliable method is identification column.
Same reliability as the new SEQUENCE numbers (like Oracle has) available in SQL Server 2012 but easier to use.
--Jeff Moden
November 4, 2012 at 1:57 pm
wrong post.
November 4, 2012 at 1:59 pm
hmmm, begin/commit transaction looks like did the trick.
begin transaction
declare @no int
exec CreateInvoiceNumber @no output
select @no
insert into test (no) VALUES( @no)
commit transaction
1.000.000 transactions, 200 threds, no duplicates.
thanks... that was educational conversation.
November 4, 2012 at 3:04 pm
It occurred to me later it could be simplified to
insert into table_that_needs_number (number) select a.number from (update controlfile set number= number+1 output inserted.number) a
seems to me that would be kind of deadlock proof - at least in my test with 200 threads 1000 reps it never once deadlocked, even using implicit transactions.
November 4, 2012 at 3:11 pm
Having thought about the possibility of deadlock, I think it might be better to go like this - puts it all in one sql statement.
CREATE TABLE [dbo].[controlFile](
[Client1Number] [bigint] NULL default 0, --you'd probably use the name of the client
[Client2Number] [bigint] NULL default 0, --this is the only time I can think of where
[Client3Number] [bigint] NULL default 0, --a primary key is counterprductive
[Client4Number] [bigint] NULL default 0,
[Client5Number] [bigint] NULL default 0,
[Client6Number] [bigint] NULL default 0,
[Client7Number] [bigint] NULL default 0,
[Client8Number] [bigint] NULL default 0,
[Client9Number] [bigint] NULL default 0,
) ON [PRIMARY]
Then your key statement would be like this:
insert into table_that_needs_number (number) select a.Client1Number from (update controlfile set Client1Number = Client1Number +1 output inserted.Client1Number ) a
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply