Allocate invoice numbers

At this point I don't know the terminology of what I need and would
appreciate help even getting started with researching the topic. I need to
allocate a series of (call them) invoice numbers. The "last used number" is
stored in a column of a table. I need to read the last used invoice number,
allocate a certain number of invoice numbers, and write the new "last used
invoice number" back to the table and column. My problem is that many users
will be producing invoices. How do I assure that only one user at a time
obtains an allocation of numbers and writes the last used back to the table?
Thank you.Hi
DECLARE @.par INT
BEGIN TRAN
SELECT @.par =MAX(invNumber)+1 FROM Table WITH (UPDLOCK,HOLDLOCK)
INSERT INTO Table (invNumber) VALUES (@.par)
COMMIT TRAN
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:44ED6E21-BDD1-4611-9C47-11B010E56222@.microsoft.com...
> At this point I don't know the terminology of what I need and would
> appreciate help even getting started with researching the topic. I need to
> allocate a series of (call them) invoice numbers. The "last used number"
> is
> stored in a column of a table. I need to read the last used invoice
> number,
> allocate a certain number of invoice numbers, and write the new "last used
> invoice number" back to the table and column. My problem is that many
> users
> will be producing invoices. How do I assure that only one user at a time
> obtains an allocation of numbers and writes the last used back to the
> table?
> Thank you.|||I would use the identity property of the int column for this. The problem
with this is that you can't really do a range unless you want to use set
identity_insert on before doing inserts. Work arounds would consist of
adding a column which contains the user_ID and this way you could maintain
"uniqueness".
To get the last value of the inserted row use scope_identity()
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:44ED6E21-BDD1-4611-9C47-11B010E56222@.microsoft.com...
> At this point I don't know the terminology of what I need and would
> appreciate help even getting started with researching the topic. I need to
> allocate a series of (call them) invoice numbers. The "last used number"
> is
> stored in a column of a table. I need to read the last used invoice
> number,
> allocate a certain number of invoice numbers, and write the new "last used
> invoice number" back to the table and column. My problem is that many
> users
> will be producing invoices. How do I assure that only one user at a time
> obtains an allocation of numbers and writes the last used back to the
> table?
> Thank you.|||richardb (richardb@.discussions.microsoft.com) writes:
> At this point I don't know the terminology of what I need and would
> appreciate help even getting started with researching the topic. I need
> to allocate a series of (call them) invoice numbers. The "last used
> number" is stored in a column of a table. I need to read the last used
> invoice number, allocate a certain number of invoice numbers, and write
> the new "last used invoice number" back to the table and column. My
> problem is that many users will be producing invoices. How do I assure
> that only one user at a time obtains an allocation of numbers and writes
> the last used back to the table?
There are two ways to go. One is to use the IDENTITY property, in which
case the table you mention would not be in play. In this case, you would
only insert into the target table, and pick the highest number with
scope_identity(). What is a little iffy here, is that I don't know whether
you actually can trust that if you insert 100 rows, that will be in a
contiguous range. But apart from that, the advantage with IDENTITY is that
it's good when there is plenty of concurrent access, as users will not
blocking with each other. Now, there is a price for this: if business
rules prohibits gaps in the numbers used, you cannot used IDENTITY. If
the INSERT fails, or the transaction is rolled back, those numbers will
not be reused later on, but are gone forever.
So that brings us to the other way, using your own table. The important
thing here is that you must hand the numbers in a transaction, and that
transaction must not commit until you have actually used them. The
idiom is like Uri showed:
BEGIN TRANSACTION
SELECT @.nextkey = coalesce(MAX(keycol), 0) + 1
FROM tbl WITH (HOLDLOCK, UPDLOCK)
WHERE ...
UPDATE tbl
SET keycol = @.nextkey + @.no_of_keys
WHERE ...
SELECT @.@.error = @.err
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
-- Use the keys
INSERT invoices (...)
..
SELECT @.@.error = @.err
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
...
COMMIT TRANSACTION
The important thing is the locking hint UPDLOCK, HOLDLOCK. If two
users arrive to this spot about the same time, the who comes second
will be upheld at the SELECT statement, until the other process
commits. Another important thing is the rigorous error checking, so
that if there is an error, you rollback and release the numbers you
did not use. (The error handling can be done cleaner in SQL 2005.)
This solution gives no gaps, but it has poorer concurrency, as users
must for each other.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Just curious -- while a transaction is being held in waiting what is or can
be displayed to the user interface of the application and by what mechanism?
<%= Clinton Gallagher
METROmilwaukee (sm) "A Regional Information Service"
NET csgallagher AT metromilwaukee.com
URL http://metromilwaukee.com/
URL http://clintongallagher.metromilwaukee.com/
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9737B6E65B08AYazorman@.127.0.0.1...
> richardb (richardb@.discussions.microsoft.com) writes:
> There are two ways to go. One is to use the IDENTITY property, in which
> case the table you mention would not be in play. In this case, you would
> only insert into the target table, and pick the highest number with
> scope_identity(). What is a little iffy here, is that I don't know whether
> you actually can trust that if you insert 100 rows, that will be in a
> contiguous range. But apart from that, the advantage with IDENTITY is that
> it's good when there is plenty of concurrent access, as users will not
> blocking with each other. Now, there is a price for this: if business
> rules prohibits gaps in the numbers used, you cannot used IDENTITY. If
> the INSERT fails, or the transaction is rolled back, those numbers will
> not be reused later on, but are gone forever.
> So that brings us to the other way, using your own table. The important
> thing here is that you must hand the numbers in a transaction, and that
> transaction must not commit until you have actually used them. The
> idiom is like Uri showed:
> BEGIN TRANSACTION
> SELECT @.nextkey = coalesce(MAX(keycol), 0) + 1
> FROM tbl WITH (HOLDLOCK, UPDLOCK)
> WHERE ...
> UPDATE tbl
> SET keycol = @.nextkey + @.no_of_keys
> WHERE ...
> SELECT @.@.error = @.err
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> -- Use the keys
> INSERT invoices (...)
> ...
> SELECT @.@.error = @.err
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> ...
> COMMIT TRANSACTION
> The important thing is the locking hint UPDLOCK, HOLDLOCK. If two
> users arrive to this spot about the same time, the who comes second
> will be upheld at the SELECT statement, until the other process
> commits. Another important thing is the rigorous error checking, so
> that if there is an error, you rollback and release the numbers you
> did not use. (The error handling can be done cleaner in SQL 2005.)
> This solution gives no gaps, but it has poorer concurrency, as users
> must for each other.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||clintonG (csgallagher@.REMOVETHISTEXTmetromilwauke
e.com) writes:
> Just curious -- while a transaction is being held in waiting what is or
> can be displayed to the user interface of the application and by what
> mechanism?
For the user that runs the transaction, there are no resrictions. Anything
can be displayed.
For other users, the default behaviour is that they will be blocked if
they try to access data that is being changed by the transaction. There
are several ways around this:
o Use a LOCK TIMEOUT, so that they will get a message that the data is
not accessible.
o Use the NOLOCK hint in queries, which permits them to see uncommitted
data. This method is quite dangerous if you don't understand the
implications.
o Use the READPAST hint. With this hint, locked rows are simply skipped.
This method, too, have dangers, as users may get incorrect information.
o In SQL 2005, you can use snapshot isolation (which comes in two different
flavours). In this, case users will see a before-image of the updated
data, which is less likely to have issues than NOLOCK and READPAST.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%23mDS4pXCGHA.2920@.tk2msftngp13.phx.gbl...
> Just curious -- while a transaction is being held in waiting what is or
> can be displayed to the user interface of the application and by what
> mechanism?
>
If your application only uses a transaction to generate the invoice numbers
there will be no need to display anyting on the UI. The wait will be on the
10ms scale, and the user will never notice it. The problem starts when the
invoice generation code participates in larger, longer-lived transactions.
Then the waits will get longer. The absolutely worst part, however, is that
the wait experienced by any user is a product of the number of other users
on the system. Such a design may work acceptably with 5-10 users, but fail
with hundreds. That's the main reason why IDENTITY is the prefered
solution: it does not cause serialization waits, and won't bite you when you
try to scale your application.
David|||You would use a unique prefix/suffix
and his/her own series for each user.
For example:
user_name - avode, prefix - oa,
unique invoice_num - oa-1 (oa-2, oa-3 and so on);
user_name - richardb, prefix - r,
unique invoice_num - r-1 (r-2, r-3 and so on).
--
Odegov Andrey
avodeGOV@.mail.ru
(remove GOV to respond)
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:44ED6E21-BDD1-4611-9C47-11B010E56222@.microsoft.com...
> At this point I don't know the terminology of what I need and would
> appreciate help even getting started with researching the topic. I need to
> allocate a series of (call them) invoice numbers. The "last used number"
> is
> stored in a column of a table. I need to read the last used invoice
> number,
> allocate a certain number of invoice numbers, and write the new "last used
> invoice number" back to the table and column. My problem is that many
> users
> will be producing invoices. How do I assure that only one user at a time
> obtains an allocation of numbers and writes the last used back to the
> table?
> Thank you.|||Here is the way I have set mine up, which I plan on using for invoice,
cheque, audit trail numbers etc. the 'Transtype' are predefined in the app
in my case VO2ADO, IE TR_ARINVOICENO = 'P'
Within my app
BeginTransaction()
at the proper row
IF USED = 1
tell the user to wait as someone else is updating
return to try the update again
ELSE
set USED = 1
NextInvoice = Next Number
Increment Next Number
endif
..
Do your updates etc
Now set the USED column in the Transnumber table to 0
IF all OK
Commit Transaction
Else
RollBack Transaction
end
I have not tested the speed using many operators, or table inserts and
updates.
Any comments on this methodology would be appreciaited.
DDL
CREATE TABLE [dbo].[TransNumber] (
[NextNumber] smallint DEFAULT(1) NOT NULL,
[Used] bit DEFAULT(0) NOT NULL,
[TransType] char(1) NOT NULL
)
GO
ALTER TABLE [dbo].[TransNumber] ADD CONSTRAINT [PK_TransactionNumber]
PRIMARY KEY CLUSTERED ([TransType])
GO
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 3, 0,
'A')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 9, 0,
'B')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 1, 0,
'C')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 1, 0,
'D')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 1, 0,
'F')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 1, 0,
'G')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 91,
0, 'H')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 1006,
0, 'P')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 1, 0,
'Q')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 1, 0,
'R')
When wise men disapprove, that's bad;
when fools applaud, that's worse.
A Spanish proverb
John Linville
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:44ED6E21-BDD1-4611-9C47-11B010E56222@.microsoft.com...
> At this point I don't know the terminology of what I need and would
> appreciate help even getting started with researching the topic. I need to
> allocate a series of (call them) invoice numbers. The "last used number"
> is
> stored in a column of a table. I need to read the last used invoice
> number,
> allocate a certain number of invoice numbers, and write the new "last used
> invoice number" back to the table and column. My problem is that many
> users
> will be producing invoices. How do I assure that only one user at a time
> obtains an allocation of numbers and writes the last used back to the
> table?
> Thank you.|||John Linville (orion^300@.telus.net) writes:
> Here is the way I have set mine up, which I plan on using for invoice,
> cheque, audit trail numbers etc. the 'Transtype' are predefined in the app
> in my case VO2ADO, IE TR_ARINVOICENO = 'P'
> Within my app
> BeginTransaction()
> at the proper row
> IF USED = 1
> tell the user to wait as someone else is updating
> return to try the update again
> ELSE
Really not sure how you intend to implement this, but, since the row
is locked, you will not be able to read USED, unless you use NOLOCK
to read it. Which may be fine for this particular case. Then again,
since two users could come here and read USED = 0, before any other
of them sets it to 1, there is a possible race condition here.
Rather than using an extra column, you are better of setting LOCK_TIMEOUT
to something >= 0, and if you get a lock-timeout error, then you tell
the user to wait.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx