Hi..
I have a problem about How to generate Incremental Transaction Number on Multiuser environment ?
for example : 13070001, 13070002, 13070003, etc..
This number is generated when users open a new transaction so when a User Interface displayed, that number is already on database and user can't look that number on their screen.
What I'm afraid here is about more than one user open new transaction and then when my business object generate that number ,then writed on DB Primary Key Collision Happened ?
is there any suggestions ? or any idea is ok,
thanks a lot
stefanus
Does it have to be a sequential number?
If not, in my opinion Guids are superior primary keys.
If you wish to use a number then the server will have to generate it.
Each time a user begins a new transaction you'll send a request to the server to get the next transaction number (you could create a master transaction number table). Then you're assured of a unique number regardless of how many users request a new transaction.
Create a table called dbo.AutomaticTransactionNumber (or give it whatever schema you want) with the following columns
TransactionType, NextNumber
Then have a stored procedure called getNextAutomaticTransactionNumber that has a Type nvarchar(255) parameter called @Type. The type parameter is a string representation of the transaction type you want to generate the number for. In the stored proc do the following.
SELECT NextNumber FROM dbo.AutomaticTransactionNumber WHERE Type = @Type
UPDATE dbo.AutomaticTransactionNumber SET NextNumber = [NextNumber]+1 WHERE Type = @Type
This will return the nextnumber for the transaction and then increment it for next time.
I´m not fond of this table structure and have found it to be not safe when used in a hight traffic site within transactions (and even nested transactions). In general - you do not want to have the sequence counter generator to be part of transactions and rollbacks in the database. On a high-traffic site this will cause your transactions to be slowed down to one singe transaction at the time when the counter generator is locked for the entire duration of a transaction.
My preference for sql server is to create a separate ID table with only an autoincrement counter for each counter. Simple and safe to use within SQL Server wheher within a transaction or not.
Hi jonny..
what do you mean transaction is on DataAccess region .. or on store proc ?
I still don't understand with the reason : counter generator is locked for entire during transaction ?
could you give me an example bad and good example ? :)
thanks a lot jonny
stefanus
Transactions is typically set for the entire data access (ex DataPortal_Update) and databases use either table, page or rowlock in transactions.
My key issue here is that the single rown in counter table is shared so even if the database use rowlock mechanism on the other tables it has a single exclusive lock on the counter.- thus limiting the database to process a single transaction at the time.
Yhis is of course assuming tht you generate the counter ID as part oi the DataPortal_Update (which is my preference).
Hi all...
thanks a lot for your respond..
@Matthew : YES , I want sequential number, because there is a government regulation that something like invoice must be in order number.
@cfarren : yes the logic is like your suggestion, But I approach that generating in order trans number is a business rules, and I want to try to look a way to do it on Business Layer ( MAINTAINABLE Reason )
@GregDobbs : Could you explain more detail ? Create Master Transaction Number Table, Assure each user get unique number..
OR My problem is can't solve at Business Layer ? Like Cfarren's suggestion it must be solved at DataBase Level ?
thanks a lot all..
If you want to maintain it in a business rule then where do the numbers and increment amounts etc come from? If they come from the database (which they should, especially for multi user) then you have to touch the database anyway so you might as well handle it there too.
That being said, you could always just do the select and update in the business rule instead of using a stored procedure but you will need the automatic numbering table.
My next thought is you could still use a guid for your primary key and use a separate identity column to auto increment an InvoiceNumber. This avoids the risk of primary key conflict.
Edit: Given Fitanv's reply below, this works only if you do not need the Invoice Number until after the initial insert. Based on your original post, that the user never sees this number, I am assuming that this is the case.
Not a database guru, so I am unsure about issues (if any) of using an Identity column in this way.
The architecture for this is straight forward, something will have to have responsibility for generating your sequence number. Whether is is maintained on the database side or handled by a stand alone WCF service, you can access it via a command object.
The more interesting question is 'when' do you get the number. This leads to all sorts of additional questions. If you get it at the beginning of a process, what happens if the process fails and needs to roll back? What if other processes have grabbed transaction numbers in the meanwhile? Does this mean you now have gaps in your sequence? Is this okay or a problem? What happens if you grab the number at the last possible moment instead? Should you lock the process that generates the number? How would this affect throughput?
Copyright (c) Marimer LLC