?
Solved

Generating unique sequences

Posted on 1998-12-02
3
Medium Priority
?
188 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
[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
  • Learn & ask questions
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
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
.
//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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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

771 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