How to handle unique constraint exception to update row after failing to insert?

Question

I am trying to handle near-simultaneous input to my Entity Framework application. Members (users) can rate things, so I have a table for their ratings, where one column is the member's ID, one is the ID of the thing they're rating, one is the rating, and another is the time they rated it. The most recent rating is supposed to override the earlier ratings. When I receive input, I check to see if the member has already rated a thing or not, and if they have, I just update the rating using the existing row, or if they haven't, I add a new row. I noticed that when input comes in from the same user for the same item at nearly the same time, that I end up with two ratings for that user for the same thing.

Earlier I asked this question: How can I avoid duplicate rows from near-simultaneous SQL adds? and I followed the suggestion to add a SQL constraint requiring unique combinations of MemberID and ThingID, which makes sense, but I am having trouble getting this technique to work, probably because I don't know the syntax for doing what I want to do when an exception occurs. The exception comes up saying the constraint was violated, and what I would like to do then is forget the attemptd illegal addition of a row with the same MemberID and ThingID, and instead fetch the existing one and simply set the values to this slightly more recent data. However I have not been able to come up with a syntax that will do that. I have tried a few things and always I get an exception when I try to SaveChanges after getting the exception - either the unique constraint is still coming up, or I get a deadlock exception.

The latest version I tried was like this:

// 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();
bool RetryGet = false;
if (memPref == null)
{
    using (TransactionScope txScope = new TransactionScope())
    {
        try
        {
           memPref = new Member_Thing_Rating();
           memPref.MemberID = memberId;
           memPref.thingID = thingId;
           memPref.EffectiveDate = DateTime.Now;
           _myEntities.Member_Thing_Rating.AddObject(memPref);
           _myEntities.SaveChanges();
        }
        catch (Exception ex)
        {
            Thread.Sleep(750);
            RetryGet = true;
        }
    }
    if (RetryGet == true)
    {
        Member_Thing_Rating memPref = (from mip in _myEntities.Member_Thing_Rating
                                       where mip.thingID == thingId
                                       where mip.MemberID == memberId
                                       select mip).FirstOrDefault();
    }
}

After writing the above, I also tried wrapping the logic in a function call, because it seems like Entity Framework cleans up database transactions when leaving scope from where changes were submitted. So instead of using TransactionScope and managing the exception at the same level as above, I wrapped the whole thing inside a managing function, like this:

bool Succeeded = false;
while (Succeeded == false)
{
    Thread.Sleep(750);
    Exception Problem = AttemptToSaveMemberIngredientPreference(memberId, ingredientId, rating);
    if (Problem == null)
        Succeeded = true;
    else
    {
        Exception BaseEx = Problem.GetBaseException();
    }
}

But this only results in an unending string of exceptions on the unique constraint, being handled forever at the higher-level function. I have a 3/4 second delay between attempts, so I am surprised that there can be a reported conflict yet still there is nothing found when I query for a row. I suppose that indicates that all of the threads are failing because they are running at the same time and Entity Framework notices them all and fails them all before any succeed. So I suppose there should be a way to respond to the exception by looking at all the submissions and adjusting them? I don't know or see the syntax for that. So again, what is the way to handle this?

Update: Paddy makes three good suggestions below. I expect his Stored Procedure technique would work around the problem, but I am still interested in the answer to the question. That is, surely one should be able to respond to this exception by manipulating the submission, but I haven't yet found the syntax to get it to insert one row and use the latest value.

Paddy · Answer 1 · 2014-07-15T06:59:03.147

To quote Eric Lippert, "if it hurts, stop doing it". If you are anticipating getting very high volumnes and you want to do an 'insert or update', then you may want to consider handling this within a stored procedure instead of using the methods outlined above.

Your problem is coming because there is a small gap between your call to the DB to check for existence and your insert/update.

The sproc could use a MERGE to do the insert or update in a single pass on the table, guaranteeing that you will only see a single row for a rating and that it will be the most recent update you receive.

Note - you can include the sproc in your EF model and call it using similar EF syntax.

Note 2 - Looking at your code, you don't rollback the transaction scope prior to sleeping your thread in the case of exception. This is a relatively long time to be holding a transaction open, particularly when you are expecting very high volumes. You may want to update your code something like this:

    try
    {
       memPref = new Member_Thing_Rating();
       memPref.MemberID = memberId;
       memPref.thingID = thingId;
       memPref.EffectiveDate = DateTime.Now;
       _myEntities.Member_Thing_Rating.AddObject(memPref);
       _myEntities.SaveChanges();
       txScope.Complete();
    }
    catch (Exception ex)
    {
        txScope.Dispose();
        Thread.Sleep(750);
        RetryGet = true;
    }

This may be why you seem to be suffering from deadlocks when you retry, particularly if you are getting rapid concurrent requests.

Thanks. Wise words. I have already thought of other ways to handle the situation, but since it was suggested I do it this way, I am curious what the way to do this is. It seems useful in general to know how to actually handle exceptions without aborting. But yes, I am thinking that using a mutex or just allowing multiple rows and cleaning them up later would work and be easier for me. — Dronz, Jul 15 '14 at 05:13
It was a very good point, so thank you for the syntax for that, but when I tested out as you suggested, I still end up getting the unique constraint exceptions over and over after it tries to re-submit. So Dispose() does not seem to be resulting in forgetting about the submissions that ran into exceptions, and neither does going up a function call level. There must be some way to get and manipulate the pending changes (I've done it with LINQ), but as you said first, it looks easier to back out the constraint and just handle the extra rows in code. — Dronz, Jul 15 '14 at 21:42
Of course that joke is far, far older than I am. http://en.wikipedia.org/wiki/Smith_%26_Dale — Eric Lippert, Jul 18 '14 at 18:23

How to handle unique constraint exception to update row after failing to insert?

1 Answers1