2 users attempt to create accounts with the same ID: how to prevent 1

[cadoltt]

Hello everybody,


This situation has to have a standard solution but I couldn't find it so far.

Imagine multimple users create their accounts and there is a table to store the personal data one of the fields of which is to keep the user nickname. Nicknames have to be unique.

Here is a simplified procedure to create a new user account:

PROCEDURE crt_account(vc_in_nick  IN  VARCHAR2,
                      vc_in_pw    IN  VARCHAR2,
                      i_out_error OUT INTEGER)
  AS
    bl_nick_exists BOOLEAN;
  BEGIN

    check_nick(vc_in_nick,bl_nick_exists);

    IF bl_nick_exists = FALSE THEN
      INSERT INTO tbl_Acccounts
        (                                    
         vc_user_nickname,
         vc_user_pw
        )
      VALUES 
        (
         vc_in_nick,
         vc_in_pw
        );

      COMMIT;

      i_out_error := 0;

    ELSE
      RAISE exc_nickname_taken;
    END IF;

  EXCEPTION
    WHEN exc_nickname_taken THEN 
     i_out_error := -1;

END crt_account;
/

Before inserting a record with a new user the procedure checks whether such a nickname exists in the table (calls check_nick).

Now, if two new users create accounts at the same time and choose the same nickname there will be a problem.

I've read about transactions, locking tables etc. but so far I don't have a clear understanding which tool to use. For example, they say locking a table doesn't prevent the other users from querying it. So suppose, the first user's procedure calls check_nick and gets FALSE (no such nickname in the table) and before it commits the insertion of a new row the second user's procedure calls in turn the check_nick and also gets FALSE (am I right here?) and, therefore will attempt to insert another record with the same nickname (of course, there is UNIQUE constraint on the vc_user_nickname field).

How should I change the procedure to prevent this kind of trouble?


Thank you,
Alex

 

[Ascalonian]

What is the table structure you have for tbl_Accounts? The nicknames should have a unique index on the column. That should help

 

[SantaMufasa]

Whoa, Alex...Let's resolve some misconceptions you have about Oracle's handling of data:

If two new users create accounts at the same time and choose the same nickname there will be a problem.

Not at all a problem...There are no ties in Oracle -- One of the two account creators will win, and the other will receive an error message, provided you have either a unique constraint or a unique index in place.

How should I change the procedure to prevent this kind of trouble?

Following is a slight modification (mods appear in bold-italic, below) of your code that handles the case where two users create duplicate accounts at the same time:

PROCEDURE crt_account(vc_in_nick  IN  VARCHAR2,
                      vc_in_pw    IN  VARCHAR2,
                      i_out_error OUT INTEGER)
  AS
    bl_nick_exists BOOLEAN;
  BEGIN

    check_nick(vc_in_nick,bl_nick_exists);

    IF bl_nick_exists = FALSE THEN
      [B][I]begin[/I][/B]
         INSERT INTO tbl_Acccounts
           (                                    
            vc_user_nickname,
            vc_user_pw
            )
         VALUES 
           (
            vc_in_nick,
            vc_in_pw
           );
      [B][I]exception[/I][/B]
         when others then
            i_out_error := -1;
      [B][I]end;
      if i_out_error is null or i_out_error <> 1 then[/I][/B]
         COMMIT;
         i_out_error := 0;
      [B][I]end if;[/I][/B]
    ELSE
      RAISE exc_nickname_taken;
    END IF;

  EXCEPTION
    WHEN exc_nickname_taken THEN 
     i_out_error := -1;

END crt_account;
/

With the code, above, then one or the other simultaneous creator of the same account will receive the error.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

cadollt,

You are quite right, Oracle does indeed provide a 'read consistent view' of data, and readers never block writers.

However, the duplicated nickname scenario you describe is unavoidable. If users can enter free format text, then Murphy's law guarantees that sooner or later, two will attempt to write the same value simultaneously.

You can only deal with this as best you can, by suitable error trapping. Have the application add a nickname, and if it raises an exception, gracefully convert this to a meaningful text message for the user, advising that a clash has occurred, and requesting a different nick name.

An alternative is to use 'SELECT .... FOR UPDATE' which will put a lock on the row affected, and prevent anyone else from writing. This isn't a solution, just a possible work around.

The long term solution is to examine the application and find the flaw in its design/architecture which allows this in the first place. Correct that flaw, and the problem will cease.

For example, I believe that the combination of user name and password should be unique, so enforce this with a constraint. If two uniquely identified people both want the same nick name, who cares? The system is in no doubt as to who is who, so having several users with a handle of "mickey mouse" is no problem.

As an aside, I notice that you appear to be storing passwords in a database table as plain text. This is a major security flaw, and should be eliminated ASAP.

If you want a column to be unique, then enforce this via a constraint, NOT by means of a unique index. As usual, the illustrious Mr Kyte has chapter and verse on this, see

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3051352977204

for details.

Regards

T



Grinding away at things Oracular

 

[cadoltt]

Thank you for answering, guys.

Everything is a simplified in my post, including the table structure, let's suppose it has just two fields (vc_user_nickname and vc_user_pw) and as I mentioned there is UNIQUE constraint on the vc_user_nickname field. Of course this constraint will not allow the duplicate but I want to handle the situation graciously, not just recieving 'ORA-00001: unique constraint violated' message. The way suggested by MikeyJudd will work too, and I will probably use it, but I thought whether it's possible to keep the second user from reading (querying) the table till the first user commits his change. This way the check_nick procedure will inform the calling procedure that the nickname is already taken. Say, will it help if I add PRAGMA AUTONOMOUS_TRANSACTION line in the crt_account procedure?

Thanks,
Alex

P.S. Thargtheslayer, you are right about password storage, in real db it's stored in hashed form, but in this example I am trying to avoid unnecessary details.

 

[SantaMufasa]

Cadoltt,

If you want to avoid a user from entering large amounts of data, just to find out that another user has simultaneously (but won the "race") to commit, then alter your logic slightly to:

1) Check first for an existing record that would duplicate your unique bits
2) INSERT and COMMIT just that amount of data to "get your foot in the door" so to speak, thus reserving the unique bits
3) UPDATE the record just INSERTed with the rest of the row's data.

How does that sound?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cadoltt]

Guys,

First of all let me apologize. In my previous post where I said 'The way suggested by MikeyJudd...' I meant SantaMufasa - I just copied the name from a wrong spot. So my apologies to both SantaMufasa and MikeyJudd


SantaMufasa,

Your first suggestion was already good enough and this one gives me another great idea (by the way, is't not the first time you are responding to my posts - I owe you!).


Thanks a lot!

Alex

 

[KenCunningham]

Then, Alex, if you 'owe' Santa, why not repay him by awarding a star, the local currency!

I want to be good, is that not enough?

 

[cadoltt]

KenCunningham,

You are quite right.


SantaMufasa,

The star is awarded. I wish it was the Silver Star or Medal of Honor


Thanks again!
Alex

 

[SantaMufasa]

Cadoltt and Ken,

You are both very generous. Thank you for the thoughtfulness. I enjoy helping.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]