如何处理网站中的sql并发问题

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

我正在制作一个使用asp.net mvc的网站。我有一张像这样的桌子

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#

在我的网站上，我经常尝试创建新项目，或者从数据库中选择项目(使用实体框架)。下面是它在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.

有时，两个请求同时出现，都试图创建具有相同colname的项。当唯一约束阻止数据库存储任何坏值时，尝试保存更改会抛出异常，因为违反了约束。

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?

捕获这个异常是处理这个异常的常规方法吗?如果我不使用上面的c#代码，而是做了一个存储过程来锁定表，然后只在允许的情况下插入一个新行，然后返回该行，那么我能获得良好的性能吗?一般是做什么?

2 个解决方案

#1

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.

有很多方法可以做到这一点。我认为在这种情况下捕获异常是最便宜的。另一种方法是将事务隔离级别提高到类似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.

在这个场景中，我将捕获唯一的约束违反并继续。如果我们从编程的角度考虑它，我们可以考虑这种情况，比如双重检查锁定，在这里我们检查，它不在那里，然后进入事务，看到它在那里，然后我们可以丢弃我们的值并使用那个在那里的值。

#2

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

这就是为什么许多开发人员使用gui或分配Ints作为惟一的键。如果您的外部密钥是合理分配的，并且是逻辑副本，那么使用DB duplicate约束，就像您已经演示的那样，或者考虑使用pessemistic锁。Sql server有一个可以使用的工具。搜索示例和博客

• Use Guid key
• 使用Guid键
• Use DB allocated Int as key
• 使用DB分配的Int作为键
• Use Column constraint and catch exception (as you explained)
• 使用列约束和捕获异常(如您所解释的)
• Use pessimistic locking, eg SQL inbuilt features
• 使用悲观锁定(如SQL内置特性)

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

搜索web上的“SQL server sp_getapplock”如何使用。