1

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?

user1955255
  • 219
  • 4
  • 18
  • you could create an insert trigger, which first checks for the id. If it exists, throw an error from SQL which will should be propagated up the stack for you to handle at the front end. – bilpor Dec 16 '15 at 11:09
  • "the same user from two browser they will acquire their own lock" - No, unless they shared an `AppDomain` and could share a the same `lock` variable - and that doesn't happen. – Enigmativity Jan 01 '19 at 21:59

2 Answers2

2

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.

BornToCode
  • 9,495
  • 9
  • 66
  • 83
JotaBe
  • 38,030
  • 8
  • 98
  • 117
  • I cannot have it as a Primary key as there is some other column which is there as Primary key. Also its a very big table adding Index will degrade the update and delete performance and there are frequent DML operations happen on this table. – user1955255 Dec 16 '15 at 12:27
  • Thanks JotaBe for the explanation – user1955255 Dec 17 '15 at 08:04
1

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.