Solved

Generating unique sequences

Posted on 1998-12-02
3
161 Views
Last Modified: 2010-03-19
I am looking for a solution to the following problem (similar anyway):

There are banks, each banks having a BANK_NBR, and each bank opens bank accounts. When an new account is created, an account number is assigned to it. For a new account, a record is created in a table that has the following unique key: BANK_NBR, ACCOUNT_NBR.

My question relates to the case where several accounts are created at the same time in the same bank. How do I ensure that each one is assign a different number.  

I wish I could use an @@IDENTITY field but each bank needs an acount 1, 2, 3, 4...

The solution probably involves using a

SELECT MAX(ACCOUNT_NBR) + 1 from ACCOUNTS
WHERE BANK_NBR = PassedValue

I realize the chances are small that two accounts be created at the same time at the same bank (probably talking about milliseconds) but I need a robust solution.
0
Comment
Question by:moonrises
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
tchalkov earned 200 total points
ID: 1092047
you can use the following construction:

begin transaction
SELECT MAX(ACCOUNT_NBR) + 1 from ACCOUNTS (tablockx)
WHERE BANK_NBR = PassedValue
.
//insert your new record here
.
commit tran
By using tablockx you require exclusive lock over the table so no one else could read from it until the end of the transaction.
0
 

Author Comment

by:moonrises
ID: 1092048
Thank you for your answer. Just to confirm, if somebody tries to read the table while there is a lock, will it try for a while or will it return an error ?
0
 
LVL 7

Expert Comment

by:tchalkov
ID: 1092049
it will wait until the lock is released
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now