如何避免来自近乎同时的 SQL 添加的重复行? [英] How can I avoid duplicate rows from near-simultaneous SQL adds?

问题描述

我的 Razor 3 Web 应用程序正在为同一个外键 ID 创建多行，当多个输入输入同一个 Id 时，我想帮助您了解如何避免这种情况.

My Razor 3 web app is creating multiple rows for the same foreign key Id, when multiple input comes in for the same Id, and I would like help on how to avoid this.

SQL Server 表将有关记录的数据存储在另一个表中(它是用户对某些事物给出的评分，其中还有一个用户表和一个可评分的事物表，因此评分表有一个外键 id对于用户，评价事物的外键 ID 和评价值).如果未给出评级，则该用户 id & 没有行.事物 ID.

The SQL Server table stores data about records in another table (it's ratings users have given about certain things, where there is also a table of users and a table of rate-able things, so the ratings table has a foreign key id for user, a foreign key id for the thing rated, and a value for the rating). When no rating has been given, there is no row for that user id & thing id.

当用户评价某事物时，代码调用服务器，服务器检查该用户之前是否评价过该事物，如果是，则更新该行，如果没有，则创建一个新行:

When a user rates a thing, the code calls the server, which checks to see if that user has rated that thing before, and if so, it updates the row, but if not, it creates a new row:

// Get the member's rating for the thing, or create it.
Member_Thing_Rating memPref = (from mip in _myEntities.Member_Thing_Rating
                               where mip.thingID == thingId
                               where mip.MemberID == memberId
                               select mip).FirstOrDefault();
if (memPref == null)
{
   memPref = new Member_Thing_Rating();
   memPref.MemberID = memberId;
   memPref.thingID = thingId;

   _myEntities.Member_Thing_Rating.AddObject(memPref);
}

除了当用户非常快速地为同一件事发送两个评分时(这种情况经常发生)，这会很好地工作，这会导致服务器创建两行，因为显然它是多线程的，并且两个线程都看不到现有的行，所以他们都创建了一个新的.

Which works fine EXCEPT when the user sends two ratings for the same thing very quickly (which happens rather often), which results in the server creating two rows, because apparently it is multi-threaded and neither thread sees an existing row, so they both create a new one.

那么……我怎样才能避免这种情况?

So... how can I avoid this?

• 我想我可以以某种方式(?)告诉 SQL Server 做出一个约束，即 memberID 和 thingID 的组合在这个表中应该是唯一的，然后它会是SQL Server 的工作是自动神奇地解析插入并希望使用最新值.

• I assume I can somehow (?) tell SQL Server to make a constraint that combos of memberID and thingID should be unique in this table, and then it would be SQL Server's job to auto-magically resolve the insertions and hopefully use the latest value.

或

• 我想我可以以某种方式 (?) 告诉此例程锁定数据库或成为单线程，以便在允许执行对同一例程的下一次调用之前完成添加行.

我只是不知道要执行的语法或 UI/SQL 步骤，尽管看了一些.我想我更喜欢线程锁的解决方案，因为我更喜欢程序员而不是 DB 人，所以我更喜欢我在代码中的复杂性.

I just don't know the syntax or UI/SQL steps to do either, despite a bit of looking. I think I prefer the thread lock solution, because I am more programmer than DB person, so I prefer my complexity in the code.

感谢您的帮助！

推荐答案

您可以轻松地向 SQL Server 表添加唯一约束，以确保您的 (memberID, thingID) 上永远不会出现重复项列:

You could easily add a unique constraint to your SQL Server table to make sure you never get duplicates on your (memberID, thingID) columns:

ALTER TABLE dbo.YourTableNameHere
ADD CONSTRAINT UQ_MemberID_ThingID UNIQUE(MemberID, ThingID)

现在，如果您的第二个连接尝试插入包含表中已经存在的 (MemberID, ThingID) 值的行，INSERT 将失败，您会得到一个你可以处理的异常，例如获取其中一个或两个 ID 的新值.

Now, if your second connection tries to insert a row with values for (MemberID, ThingID) that are already in the table, the INSERT will fail, you'll get an exception which you can handle and e.g. get new values for one or both of those ID's.

这篇关于如何避免来自近乎同时的 SQL 添加的重复行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！