Solved

Generating unique sequences

Posted on 1998-12-02
3
176 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql server table datatypes 4 32
listing SQL login names of valid databases 2 20
Solar Winds can't see SQL Server Express 17 30
SQL Query 2 31
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

792 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