CODEWITHSUNDEEP
sql-serverasp.net-mvcsql-server-2008entity-framework
bdwain
bdwain

Reputation: 1735

How to deal with sql concurrency issues in a website

I am making a website using asp.net mvc. I have a table that looks like this

Create Table Items(
   [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
   [Colname] [nvarchar](20) NOT NULL
   //OTHER UNIMPORTANT COLS HERE
)

ALTER TABLE Items ADD CONSTRAINT UN_Colanme UNIQUE(Colname)

From my website, I frequently try to create new Items, or select items from the database (using entity framework). Here's how it looks in c#

var item = db.Items.FirstOrDefault(i => i.Colname == "foo");
if(item == null)
{
   item = new Item("foo");
   db.Items.Add(item);
}

//some stuff with item
db.SaveChanges();

Sometimes, 2 requests come in at the same time, both trying to create an item with the same colname. While the unique constraint prevents the database from storing any bad values, trying to save changes throws an exception because the constraint is violated.

Is catching this exception the normal way to handle this? Could I get good performance if instead of the above c# code, I made a stored procedure that locked the table, and then inserted a new row only if it was allowed, and then returned that row? Is that commonly done?

Upvotes: 3

Views: 233

Answers (2)

phil soady
phil soady

Reputation: 11308

Thats why many devs use GUIDs or DB allocated Ints as the unique key. If your externally key is legitimately being allocated and is a logical duplicate, then use the DB duplicate constraint as you have demonstrated or consider using a pessemistic lock . Sql server has a tool you can use. Search for examples and blogs

  • Use Guid key
  • Use DB allocated Int as key
  • Use Column constraint and catch exception (as you explained)
  • Use pessimistic locking, eg SQL inbuilt features

search web for "SQL server sp_getapplock" on how to use.

Upvotes: 0

Darren Kopp
Darren Kopp

Reputation: 77627

Well, there are many ways to do this. I would say catching the exception is the least expensive in this scenario. The other way is to raise the transaction isolation level to something much higher like Serializable.

In this scenario though, I would just catch the unique constraint violation and continue on. If we were too think about it in programming terms, we can think about this situation like double-checked locking, in that we check, it's not there, then enter into transaction, see it's there, then we can just discard our value and use the one that is there.

Upvotes: 1

Related Questions