Database concurrency

I'm wondering whether the following code would work if users are RAPIDLY registering (assumption) WITH the same username.

public bool UsernamExists(string username){string sql ="SELECT true FROM [users] WEHRE username = @.username;";return Convert.ToBoolean(comm.ExecuteScalar());}public bool Signup(User user){bool usernameExists = UsernameExists(user.Username);if( usernameExists )return false;//update or insert sql for user etc blah blah}

If two users try to signup AT THE VERY SAME TIME (DOWN TO THE NANOSECOND), would this technique work? Do I have to wrap it in a transaction, stored procedure??

Thanks.

no that does not guarantee unique usernames.

You should add a unique constraint to your username column in the database to guarantee that duplicates cannot exist.

Then, you can still do your usernameexists check it you want, but you would also want to add an error handler (try/catch) to catch the exception that would occur in the unlikely event that two users simultaneously submitted the same usernames.

|||I know about unique column BUT would the technique I mentioned above work IN TERMS OF CODING? Like 2 users submitting AT THE VERY SAME TIME.|||

Yes the code would run, but...

if you dont have a unique constraint in your db, then you could end up with 2 records with the same username.

|||

Your code will not work because you have error in it

"SELECT true FROM [users]WHERE username = @.username;";

you have no comm object defined ad no parameter added to command. But if you update it you will have no guaranty that you will not have two user with the same name in your database. But you can modify you code to do something like this with transaction (safer) or not:

BEGIN TRAN

IF not exist(SELECT * FROM [users]WHERE username = @.username)
BEGIN
INSERT into [users](username)
Values (@.username)
SELECT 'True' [Result]
END
ELSE
SELECT 'False' [RESULT]

COMMIT TRAN

In this way you will save one round trip to SQL server an everything will be done in one shot and very fast.

Thanks

|||

I know you have to use the comm object, excluded it out for the simplicity.

I thought about wrapping my sql syntax in a transaction with row locking, but I'm not to keen on writing that much sql syntax. I like to keep it minimal.

Is there a way the comm object could "perform" the transaction automatically? Can you show me a simple example?

|||

Hi vze1r2ht

There is no way to make the comm object start a transaction automatically. You will need to start a transaction by explicitly calling SqlConnection.BeginTransaction() and assign the returned SqlTransaction object to a SqlCommand.

You can also perform this in a stored procedure by using BEGIN TRAN.

|||

Kevin Yu - MSFT:

Hi vze1r2ht

There is no way to make the comm object start a transaction automatically. You will need to start a transaction by explicitly calling SqlConnection.BeginTransaction() and assign the returned SqlTransaction object to a SqlCommand.

You can also perform this in a stored procedure by using BEGIN TRAN.

Thank you. Is there any major performance hit when using the SqlConnection.BeginTransaction() technique vs SQL's BEGIN Tran? Because of the massive amounts of transactions I will need to write for my classes, it will alll be inline if it was SQL and NOT stored procedures (hard to maintain).

|||

Hi,

As far as I can see, they are the same. Not much differences.