November 4, 2012 at 8:18 pm
lnardozi 61862 (11/4/2012)
It occurred to me later it could be simplified toinsert 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.
I agree... that's much more deadlock proof.
--Jeff Moden
November 4, 2012 at 9:32 pm
More on the whole can't use IDENTITY "requirement".
Just be aware that IDENTITY is for doing exactly what you are trying to do here is for, and will perform much better with less trouble.
You indicate that this is meant to be a high volume application, so be aware that you are imposing a limit on performance that you would not have if you used IDENTITY.
November 5, 2012 at 3:56 am
I have customer database. and transactions database. each transaction gets an Id number. okay. 2 different customers made 2 transactions. here's teh trasactions table:
ID CustomerNumber
1 1
2 2
what happened here ? second customer made first transaction but its transaction number is 2. it should 1. that's why I cannot use ID. the only solution I can think if is to create different transaction tables for each customer which is unacceptable for me.
to clearify: there are multiple customers of mine. I dont generate bill for them. the invoice number I'm talking about is not MY INVOICE NUMBER. Each of my customers uses my portal to generate bill for THEIR OWN customers. that's why I need separate, actually multiple invoice numbers.
November 5, 2012 at 5:29 am
aykut canturk (11/5/2012)
I have customer database. and transactions database. each transaction gets an Id number. okay. 2 different customers made 2 transactions. here's teh trasactions table:ID CustomerNumber
1 1
2 2
what happened here ? second customer made first transaction but its transaction number is 2. it should 1. that's why I cannot use ID. the only solution I can think if is to create different transaction tables for each customer which is unacceptable for me.
to clearify: there are multiple customers of mine. I dont generate bill for them. the invoice number I'm talking about is not MY INVOICE NUMBER. Each of my customers uses my portal to generate bill for THEIR OWN customers. that's why I need separate, actually multiple invoice numbers.
Gosh... I just don't see that. IDENTITY is going to number separate transactions in the order that the table was inserted. Add a DATETIME2 column with a default of CURRENT_TIMESTAMP and see. At best, there will be a tie.
--Jeff Moden
November 5, 2012 at 8:00 pm
Well, now I'm completely confused. I know how to do what you want to do, I just have no idea why you want to do it. If you want to display sequential number(s) for each invoice, why not just do like so
select * from
(select id,CustomerNumber,ROW_NUMBER() over (order by id) invoice_number from orders) a where CustomerNumber=@customerNumber and
invoice_number between @beginRow and @endRow
on your customer's orders page and use invoice_number as their reference number for you. Guaranteed sequential, unique and immune to change if you use logical deletes instead of physical ones. Also, if this is a web app you're going to have to do paging anyway and this gets you paging for free. Moden knows what he's talking about, you could do a lot worse in asking for advice. I make no value judgements on SHOULD you do it, I just tell you HOW you can do it. Buyer Beware.
November 6, 2012 at 1:51 am
dear lnardozi 61862,
problem is, I'm not generating invoices. my customers are generating their own invoices for their own customers using my web application. Say, customer A starts operation and generated 3 invoices. invoice numbers will be 1,2 and 3. I can do that even with ID field. customer B will also generate, lets say 2 invoices. numbers should be 1 and 2. if I use Id, these last numbers would be 3 and 4. then customer A generates 2 more invoices. numbers should be 4 and 5. again if I use IF filed they would be 5 and 6.
thats the problem. but the solution above seems to be working. but any ideas are welcome. I'm still searching about the reliability of the solution above.
November 6, 2012 at 6:12 am
aykut canturk (11/6/2012)
dear lnardozi 61862,problem is, I'm not generating invoices. my customers are generating their own invoices for their own customers using my web application. Say, customer A starts operation and generated 3 invoices. invoice numbers will be 1,2 and 3. I can do that even with ID field. customer B will also generate, lets say 2 invoices. numbers should be 1 and 2. if I use Id, these last numbers would be 3 and 4. then customer A generates 2 more invoices. numbers should be 4 and 5. again if I use IF filed they would be 5 and 6.
thats the problem. but the solution above seems to be working. but any ideas are welcome. I'm still searching about the reliability of the solution above.
From what I can see you want to be able to save Invoice Number somewhere (so re-generating on fly is not an option, as deleted invoices will screw-up numbers) and you want to know the last Invoice number generated per CustomerId so you can increase it when next invoice is about top be created.
So, cannot think anything else than dedicated table which will maintain last invoice number per CustomerId.
November 6, 2012 at 6:48 am
aykut canturk (11/6/2012)
dear lnardozi 61862,problem is, I'm not generating invoices. my customers are generating their own invoices for their own customers using my web application. Say, customer A starts operation and generated 3 invoices. invoice numbers will be 1,2 and 3. I can do that even with ID field. customer B will also generate, lets say 2 invoices. numbers should be 1 and 2. if I use Id, these last numbers would be 3 and 4. then customer A generates 2 more invoices. numbers should be 4 and 5. again if I use IF filed they would be 5 and 6.
thats the problem. but the solution above seems to be working. but any ideas are welcome. I'm still searching about the reliability of the solution above.
That finally brings a little clarity to the problem. Each customer needs their own sequence numbers and to do that (as Eugene said above), you're going to need a dedicated sequence number table or some form of dynamic SQL along with the new SEQUENCE objects available in 2012. I hate these damned things because they're always a PITA but ya gotta do what ya gotta do.
Now my question would be (didn't look back through this thread) is what version of SQL Server do you actually have so we can give you the best help? Is it really 2012 as you stated in your first post? I ask only because it's important for stuff like this.
--Jeff Moden
November 6, 2012 at 7:20 am
ok I've got this implementation in my snippets that does what you are asking.
see if this works for you.
you need a table and a procedure, shown below:
CREATE TABLE [dbo].[InvoiceKeys] (
[CustomerID] INT NOT NULL,
[InvoiceNumber] INT NOT NULL,
CONSTRAINT [PK__InvoiceKeys__6CC3E588] PRIMARY KEY CLUSTERED (CustomerID) WITH FILLFACTOR = 100)
GO
CREATE PROCEDURE GetNewInvoice @CustomerID CHAR(30), @NewInvoiceNumber INT OUTPUT
AS
BEGIN
BEGIN TRANSACTION
IF NOT EXISTS (SELECT InvoiceNumber FROM InvoiceKeys WHERE CustomerID = @CustomerID)
INSERT INTO InvoiceKeys VALUES (@CustomerID, 0)
SELECT @NewInvoiceNumber = InvoiceNumber + 1
FROM InvoiceKeys WITH(HOLDLOCK)
WHERE CustomerID = @CustomerID
UPDATE InvoiceKeys
SET InvoiceNumber = @NewInvoiceNumber
WHERE CustomerID = @CustomerID
COMMIT TRANSACTION
END
now the actual implementation, from TSQL so you can see it here, would be like this...the value of your OUT parameter is what you would save inside a customers table someplace:
--Usage:
DECLARE @MyCustomer int,
@MYOUTKEY INT
SET @MyCustomer= 42
SET @MYOUTKEY=0
PRINT @MYOUTKEY
EXEC GetNewInvoice @MyCustomer,@MYOUTKEY OUT
PRINT @MYOUTKEY --this is the New invoice number to be saved in another table.
Lowell
November 6, 2012 at 8:01 am
Lowell (11/6/2012)
ok I've got this implementation in my snippets that does what you are asking.see if this works for you.
you need a table and a procedure, shown below:
CREATE TABLE [dbo].[InvoiceKeys] (
[CustomerID] INT NOT NULL,
[InvoiceNumber] INT NOT NULL,
CONSTRAINT [PK__InvoiceKeys__6CC3E588] PRIMARY KEY CLUSTERED (CustomerID) WITH FILLFACTOR = 100)
GO
CREATE PROCEDURE GetNewInvoice @CustomerID CHAR(30), @NewInvoiceNumber INT OUTPUT
AS
BEGIN
BEGIN TRANSACTION
IF NOT EXISTS (SELECT InvoiceNumber FROM InvoiceKeys WHERE CustomerID = @CustomerID)
INSERT INTO InvoiceKeys VALUES (@CustomerID, 0)
SELECT @NewInvoiceNumber = InvoiceNumber + 1
FROM InvoiceKeys WITH(HOLDLOCK)
WHERE CustomerID = @CustomerID
UPDATE InvoiceKeys
SET InvoiceNumber = @NewInvoiceNumber
WHERE CustomerID = @CustomerID
COMMIT TRANSACTION
END
now the actual implementation, from TSQL so you can see it here, would be like this...the value of your OUT parameter is what you would save inside a customers table someplace:
--Usage:
DECLARE @MyCustomer int,
@MYOUTKEY INT
SET @MyCustomer= 42
SET @MYOUTKEY=0
PRINT @MYOUTKEY
EXEC GetNewInvoice @MyCustomer,@MYOUTKEY OUT
PRINT @MYOUTKEY --this is the New invoice number to be saved in another table.
Looks like the problem I resolved at a couple of previous companies and I believe that's going to be deadlock city because of the SELECT/UPDATE within a transaction.
--Jeff Moden
November 6, 2012 at 9:38 am
I'll give a try to these solutions tonight. by the way, sql version is 2012 and that's not gonna change. only upgrades are posibble.
November 6, 2012 at 9:39 am
I would pre-populate InvoiceTable with 0 on the Customer Record creation, then getting and updating invoice number would be as simple as:
DECLARE @invoiceNo INT
UPDATE [InvoiceKeys] SET @invoiceNo = [InvoiceNumber] = [InvoiceNumber] + 1
WHERE [CustomerID] = 1
SELECT @invoiceNo -- RETURN @invoiceNo
The good thing about the above solution, is that calls to such proc wouldn't block or dead-lock each other, and rollback will be per failed transaction - customer...
November 6, 2012 at 10:22 am
I was digging sql2012 sequence feature, looks like don't need to worry about deadlocks nor increments any more but I couldn't find one thing:
where does sql2012 stores sequences. it seems you have to use
select * from sys.sequences
not
select * from sequences
so does it mean sequences are stored in master database ? if so, do they disappear after restore or re-installing operating system and sql2012 ? any ideas ? I could not find any clue while googling.
November 6, 2012 at 10:43 am
aykut canturk (11/6/2012)
I was digging sql2012 sequence feature, looks like don't need to worry about deadlocks nor increments any more but I couldn't find one thing:where does sql2012 stores sequences. it seems you have to use
select * from sys.sequences
not
select * from sequences
so does it mean sequences are stored in master database ? if so, do they disappear after restore or re-installing operating system and sql2012 ? any ideas ? I could not find any clue while googling.
I don't think SEQUENCE feature will help you, as it's going to be a nightmare if you will create a sequence per customer... Also, you will be bound to use Dynamic SQL to get next ID's. No, SEQUENCEs are not designed for what you want.
November 7, 2012 at 4:22 am
I would change the HOLDLOCK to an UPDLOCK. An U lock is aquired, and other calls to the procedure will have to wait for the lock to be released before a new invoice number can be aquired.
If you just use HOLDLOCK then another connection will get the same invoice number if the first connections hasn't executed the UPDATE statement. With UPLOCK the second connection will have to wait for the U (or X when UPDATE statement is executed) before the SELECT statement can be completed.
SELECT @NewInvoiceNumber = InvoiceNumber + 1
FROM InvoiceKeys WITH(UPDLOCK)
WHERE CustomerID = @CustomerID
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply