CODEWITHSUNDEEP
c#sql-serverentity-frameworkconcurrencydatabase-concurrency
user1955255
user1955255

Reputation: 229

How to prevent race conditions in a web application

I have a web application where user register by clicking a button "Join". There can be so many users on the website and that is why in order to keep my database queries fast; I chose not to have foriegnkey constraint added in database(Though it is relational database).

Now what happens is when user with same userId opens the application in two different browsers and hit the "Join" button exactly at the same time; two rows for same user is added into database which is wrong.

The ideas I have to stop this are:

  1. Do the check/insertion logic in stored procedure and within a transaction with SQL Transaction Isolation level as SERIALIZABLE; but with this approach table will be locked even if two different users would be hitting "JOIN" button at the same time.
  2. Use lock keyword in c# and perform check/insertion logic from inside it but I believe if the same user from two browser they will acquire their own lock and would still be able to have two entries in database. Also for different users it might create a problem as other's code would be waiting for the first one to free the resources.
  3. Use Optimistic concurrency which is supported out of box by EntityFramework but I am not sure if it will solve my problem.

Could you please help me with this?

Upvotes: 1

Views: 1956

Answers (2)

JotaBe
JotaBe

Reputation: 39004

You can easyly solve your problem by creating an unique index in the user name. So, only the first one will be saved. The next one will be reported as an error, because it would break the unique index.

In fact, it should be the primary key.

According to your comments, your table is huge. So it must be much worse to look for a row in the whole table without using an index on every insert operation, than updating the an index on each insert/delete/update operation. You should consider this.

Anyway, the only way to solve the problem of not inserting the value if already exists means checking it.

Optimistic concurrency has nothing to do with that. Optimistic concurrency has to do with reading data, modifying it, and saving changes, without locking the table. What optimistic concurrency does can be explained in this steps:

  1. read the original row from the DB, without any locks or transactions
  2. the app modifies the original row
  3. when the app tries to save the changes, it checks if the row in the DB is exactly as it was when it was read on the step 1. If it is, the changes are saved. If it isn't a concurrency exception is thrown.

So optimistic concurrency will not help you.

I insist on using an unique index, which is the safest, most simple, and probably more preformant solution.

Upvotes: 2

user3316920
user3316920

Reputation:

I would use Entity and its Optimistic Concurrency.

It will wrap it in a transaction and handle these problems for you. Remember to place both identity and a primary key on the table. In case the username has to be unique then add the unique annotation on the table.

Upvotes: 1

Related Questions