I have a typical problem. I am to create a user management pages using ASP. The username field for users should be unique. In the ASP, I am checking it like this.

1)If username does not exist in database
2) Insert the user into database
3)else
4) Indicate that the username already exists. Show the details form again.
5)end if

For line1, I am using a typical select statement and a recordset checking whether it is at EOF.

But my friend says that this code is not suited for multi-program environment. He says that a processor switch task can happen after line1. Then it may happen that in the following task submitted by another user, it can insert a user with the same username as the one I am trying to insert. Then when processor comes back to my task (starting from line2), it will try to insert my user; but this will fail and cause an error.

I consider my friend's argument to be logically correct. It may not have happened to me never. But this does not make me think that my code is not prone to this error. He suggests to use MTS for this. But I doubt for doing a simple db operation like this should I use complex things like MTS ? Is there any simple way this can be done in a fool-proof manner ?

Could anybody shed some some light into this as I am terribly worried I need to redo a lot of code I have done. I have been doing this method for a long for checking uniqueness.

Thanks i advance.

That could be with a very minute time difference between both the transactions. It mainly depends up on the kind of hit count your site is gonna have. Still I would say that there is a rare such chance of happening.

Did both of you try doing that at the same time? How many times did you succeed hitting that ERROR point? You may succeed once in 10000 times, may be when you are smarter than computer;).

It all depends up on the processor speed, and number of other applications running on the system when trying to do so, even the network traffic, all that makes the difference.

As you said, in anyways, when the USERNAME already exists it takes you to SHOW DETAILS form again. So why do you have to worry about that?

May I suggest one correction to this? As it has been followed everywhere as a standard, you can get the USERNAME as his/her EMAILID, as you negate the possiblity of having to choose the same username/emailid again. No one would register/login with anyone else's email id. This way you reduce the possibility of having the user to choose/provide the same username(in this case emailid) again.

As you are dealing with User/password authentication, I am sure you got to provide a Password retrieval feature to it, in which case, mailing to the EmailId, would be the right solution, instead of getting username and showing the password, still one can play with that to see other's password and play around.

I am not trying to divert you from your question. If that really helps and makes sense, do consider that, as this worked perfect in my case.

Hope that helps.
Cheers!

Hope that Helps.
Cheers!

-Vijay G

I agree that the probability of this error is very very small. But you cannot say that it will not happen. This may not happen in a site. Also, I was taking an example to illustrate a wide range of problems. My context is actually not that of maintaining a user master.

If we are using ASP based web applications for intra-office programs, this can have drastic problems. For e.g. In an insurance company, insurance processing may be happening at 100 terminals simultaneosly. They may have a rule that out of their capacity(num processed per day), 60% is used for urban application processing and 40% for rural application processing. (Any remaining application in either category will be postponed to the next day). At each process entry we need to check whether the limit is reached. In those situations this problem can happen.

My idea was to formulate a general method of tackling the issue. I think I got it. I can change the stored procedures slightly as follows. I am putting modified stored procedure for user maintainance.

CREATE PROCEDURE proc_insert_user(@param_email varchar(200), @param_password varchar(15), @param_firstname varchar(50), @param_lastname varchar(50), @param_user_status char(1))
AS
if not exists (select * from tbl_users where email = @param_email)
begin
    INSERT INTO tbl_users(email, password, firstname, lastname, user_status) VALUES(@param_email, @param_password, @param_firstname, @param_lastname, @param_user_status)
    return (0)
end
else
    return (1)

In this way I can avoid the SP for checking the existence also. This will make it faster.

Any comments on this ? Is there any other easier way ? Or is this method also prone to the same type of errors ?

Some more better SP for this (suggested by one friend through another forum).

CREATE PROCEDURE proc_insert_user(@param_email varchar(200), @param_password varchar(15), @param_firstname varchar(50), @param_lastname varchar(50), @param_user_status char(1))
AS
    SET NOCOUNT ON

    SELECT email from tbl_users where email = @param_email
    IF @@rowcount = 0
    BEGIN
        BEGIN TRANSACTION
        INSERT INTO tbl_users(email, password, firstname, lastname, user_status) VALUES(@param_email, @param_password, @param_firstname, @param_lastname, @param_user_status)
        IF @@Error <> 0
        BEGIN
            COMMIT TRANSACTION
            RETURN(1)
        END
        ELSE
        BEGIN
            ROLLBACK TRANSACTION
            RETURN(2)
        END
    END
    ELSE
        RETURN (0)

This is much better and fills the last holes also. There may be context switches while executing an SP also. But this solves that problem also.

This returns 0 if email already exists in db; 1 if insertion was success; 2 if some error happened in insertion.

Yes, this looks perfect.

Anytime you do Data Manipulation, better use BEGIN TRAN and COMMIT TRAN/ROLLBACK TRAN(on success/Failure) checking for @@ERROR's value. That is the best practice to avoid errors.

Cheers!

-Vijay G