May 31, 2014 at 3:31 pm
Hi,
I have a db to manage the creation of invoice number designed for a web application.
My problem is how to manage the concurrency when the users need to create an invoice number.
thank in advance for any suggestion.
VP
May 31, 2014 at 8:14 pm
change your thought process: the end user never tells YOU what the purchase order number is.
a user requests a new purchase order number, and you assign it.
that purchase order number is the identity() value of a table, as i remember it, an old article said that for sql2005 , it can easily handle more that 400 identity concurrent requests per second, and that was with 32 bit, slower hardware than you have today.
you can capture their desired po number if you want, but don't let it be a primary key, make the login user and the requested number an attribute instead
Lowell
June 1, 2014 at 2:31 am
Hi Lowell,
thanks for your reply.
Indeed it is the application that has to assign the purchase order number, the solution you suggest is to create a table with Identity and after show to user like select SELECT SCOPE_IDENTITY(), right?
The problem is that PO number it must be custumized ie. number/year and each new year it is to restart from 1.
It means that each new year I have to reset a sequence ID...
Or maybe I have to evaluate to work with lock on the? but I am afraid about deadlock... 🙁
June 1, 2014 at 8:31 am
vinpes (6/1/2014)
Hi Lowell,thanks for your reply.
Indeed it is the application that has to assign the purchase order number, the solution you suggest is to create a table with Identity and after show to user like select SELECT SCOPE_IDENTITY(), right?
The problem is that PO number it must be custumized ie. number/year and each new year it is to restart from 1.
It means that each new year I have to reset a sequence ID...
Or maybe I have to evaluate to work with lock on the? but I am afraid about deadlock... 🙁
Can you then use sequence numbers in sql server 2012?
Your PO number will be the seqNo/curentYear ...
You can reset the seq number whenever you want.
Igor Micev,My blog: www.igormicev.com
June 1, 2014 at 10:17 am
Sequences sound like a good choice here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2014 at 11:04 am
Hello guy,
Yes, i have evaluated to work with sequence, but there are some limitation , ie. Sequence numbers inserted into a table can have gaps when a transaction is rolled back, when a sequence object is shared by multiple tables, or when sequence numbers are allocated without using them in tables.
What do you think about this strategy?
(The problem is i have to' update al tables inserting identity column!
CREATE TABLE [dbo].[tbRelMov](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RelDitta] [nvarchar](3) NOT NULL,
[RelAnno] [nchar](4) NOT NULL,
[RelNumReg] [int] NOT NULL,
CONSTRAINT [PK_tbRelMov] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[RelDitta] ASC,
[RelAnno] ASC,
[RelNumReg] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
create Procedure sp_CreateNextNumReg
AS
BEGIN
SET XACT_ABORT ON
DECLARE @nextNumReg int
DECLARE @YEAR VARCHAR(4)
SET @YEAR = (SELECT CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4)))
BEGIN TRAN
INSERT INTO [test].[dbo].[tbRelMov]
([RelDitta]
,[RelAnno]
,[RelNumReg])
VALUES
('001'
,@YEAR
,0)
SET @nextNumReg = (SELECT [RelNumReg] + 1
FROM [tbRelMov]
WHERE [RelAnno] = DATEPART(YEAR,GETDATE()) AND ID = (SELECT SCOPE_IDENTITY() AS id ) - 1)
IF(@nextNumReg =0 OR @nextNumReg ='' OR @nextNumReg IS NULL)
BEGIN
SET @nextNumReg =1
END
UPDATE [tbRelMov]
SET RelNumReg = @nextNumReg
where ID =(SELECT SCOPE_IDENTITY() AS id )
COMMIT TRAN
END
GO
June 2, 2014 at 7:42 am
vinpes (6/1/2014)
Hello guy,Yes, i have evaluated to work with sequence, but there are some limitation , ie. Sequence numbers inserted into a table can have gaps when a transaction is rolled back, when a sequence object is shared by multiple tables, or when sequence numbers are allocated without using them in tables.
That is normal behavior. And of course when a given sequence is used by more than 1 table of course there will be "gaps" in the values.
What do you think about this strategy?
Why are you storing pointers to the "next" row? No matter how you implement this it is always going to be a challenge because this is denormalized. Let the data tell you what the next one is by sorting by a value (most likely the sequence). What are you going to do if you delete a row? Now your pointer is pointing to nothing or you have to do some really complicated silliness to update the row that used to point to the row you just deleted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 2, 2014 at 7:50 am
Hi SSChampion,
you are right, indeed my problem with my strategy is in case of same rollback.
I am storing pointer to nextrows
SET @nextNumReg = (SELECT [RelNumReg] + 1.....
cause I need to create Next Register Number so I want to use the previous Identity to know what is my next RelNumReg.
I try to tell my need, maybe you can suggest me some new solution.
I have a invoice system (web appl), the invoice number will be InvoiceNr/Year and InvoiceNr = max(InvoiceNr) + 1, now I am afraid of concurrency,
Suggestions?
Tks
June 2, 2014 at 8:03 am
vinpes (6/2/2014)
Hi SSChampion,you are right, indeed my problem with my strategy is in case of same rollback.
I am storing pointer to nextrows
SET @nextNumReg = (SELECT [RelNumReg] + 1.....
cause I need to create Next Register Number so I want to use the previous Identity to know what is my next RelNumReg.
I try to tell my need, maybe you can suggest me some new solution.
I have a invoice system (web appl), the invoice number will be InvoiceNr/Year and InvoiceNr = max(InvoiceNr) + 1, now I am afraid of concurrency,
Suggestions?
Tks
I would use a sequence here. You can always reset it at the beginning of the year. Why are you worried about having a gap in the values? This is normal behavior of nearly every invoicing system out there. Especially since these are invoices, who cares?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 2, 2014 at 8:25 am
This is an example in pseudo code about what I am worried.
USER 1
--my invoice Number = mysequence = 1
SELECT
mysequence = NEXT VALUE FOR dbo.MySequence;
GO
begin trans
insert into table1 (mysequence , 'A', 'B','C')
insert into table2 (mysequence , 'F', 'G','100')
-- AT THIS POINT ROLLBACK
insert into table3 (mysequence , '123', '1B',44.00)
ROLLBACK
------------------
USER 2
--my invoice Number = mysequence = 2 (THIS GAP FOR INVOICE SYSTEM CAUSE SEQ 1 ISNT USED)
SELECT
mysequence = NEXT VALUE FOR dbo.MySequence;
GO
begin trans
insert into table1 (mysequence , 'A', 'B','C')
insert into table2 (mysequence , 'F', 'G','100')
-- AT THIS POINT ROLLBACK
insert into table3 (mysequence , '123', '1B',44.00)
COMMIT trans
Maybe in case of Rollback, I could to restart mysequence but for scenario below, if the USER2 create a new sequence first that I restart from last - 1, there will be a gap cause sequence number = 2 will be already used!
🙁
June 2, 2014 at 11:35 am
If you have a legal obligation that prohibits gaps in your invoice numbers, then what I would do is on a rollback is after the rollback is completed on the invoice table to insert a voided invoice with the number that would have been used had the invoice been properly completed.
June 2, 2014 at 11:41 am
Hi Lynn,
I considered this option, but it could be other problem of concurrency.
Imagine I have two record of with invoice Nr not completed, ie
InvoiceID flagcompleted
1 yes
2 no
3 no
4 yes
Next user should be use InvoiceID 2 but if two users ask at sametime, I will have a concurency problem again!
🙁
June 2, 2014 at 11:57 am
vinpes (6/2/2014)
Hi Lynn,I considered this option, but it could be other problem of concurrency.
Imagine I have two record of with invoice Nr not completed, ie
InvoiceID flagcompleted
1 yes
2 no
3 no
4 yes
Next user should be use InvoiceID 2 but if two users ask at sametime, I will have a concurency problem again!
🙁
Again, if you cannot have gaps in your invoice numbers once an invoice number is assigned to a specific invoice and that process fails resulting in a rollback situation you need to void that number. Once assigned (issued is a better term) it should not be available for use by another process even if there was a rollback.
June 2, 2014 at 12:11 pm
But I'd recover that invoice number not to void
:ermm:
June 2, 2014 at 12:37 pm
vinpes (6/2/2014)
But I'd recover that invoice number not to void:ermm:
And you complexity to the process that really isn't necessary.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply