January 27, 2009 at 4:29 pm
Dear,
I am developping a web site and when I am creating the accounts, I also need to generate a unique key which will be used later on.
I have a table, which contains among other, the following columns:
tbl_account
account_id int identity
code varchar(20) => on which I created a unique index.
When I am creating the account, the code sequence is:
1. Open a transaction
2. loop until code is unique
call stored procedure with a new generated code
if return code of the stored procedure is ok, leave the loop, otherwise loop
(the stored procedure either returns the new account_id > 1 or returns -2 if the code already exists)
3. update the newly created record with additional information on the account
4. create additional links (between other tables)
5. commit the transaction
My question is:
Since I expect the multi-threading to work and other accounts to be created while the transaction (linked to other accounts also being created) will still be open, does the unicity of the codes be effective?
In other words, let's take an assumption that a first user account creation attempts to records the code "ABC" and its transaction is opened while another transaction attempts to create another record with code "ABC". Will the second transaction "know" that the code already exists in the other opened transaction?
Code of the stored procedure is equivalent to:
CREATE PROCEDURE dbo.sp_Account_Add
(
@in_login Varchar(50),
@in_passwordVarchar(15),
@in_code Varchar(20)
)
AS
set nocount on
IF EXISTS( SELECT account_id FROM tbl_account WHERE code = @in_code)
RETURN -2
ELSE
INSERT tbl_account ( login,password, code)
VALUES(@in_login, @in_password, @in_code)
RETURN @@IDENTITY
GO
I hope my question is understandable...
Thanks in advance for your feedback.
January 28, 2009 at 4:38 am
The unique constraint will keep duplicates from getting in. I'd suggest, in order to reduce contention, that you perform the INSERT statement without the pre-check and then check that the a successful insert occurred. Depending on the size of the system and how many duplicate records you're really going to get, you can pretty radically cut down on processing time not having to search for the the record prior to inserting it. On the other hand, a large number of rejects will have an affect on the identity column, so you might want to reconsider having it as the primary key.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply