Solved

Generating unique sequences

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Episode III - Revenge of The Dude 24 53
sql 2014,  lock limit 5 32
sql query to calculate avaerage 21 41
Update a text value in another table 10 42
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

832 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