Solved

# Generating unique sequences

Posted on 1998-12-02
Medium Priority
188 Views
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
Question by:moonrises
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 7

Accepted Solution

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

begin transaction
SELECT MAX(ACCOUNT_NBR) + 1 from ACCOUNTS (tablockx)
WHERE BANK_NBR = PassedValue
.
.
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

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

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
###### Suggested Courses
Course of the Month14 days, 22 hours left to enroll