Azure WebJob因唯一约束报错，疑多线程并发导致，如何规避？

Hey Joe, let's break down what's going on here and fix that frustrating unique constraint issue once and for all!

What's Causing the Problem? #

Your current code has a classic race condition between the check (seek == 0) and the insert operation. Here's the play-by-play:

• Thread 1 checks if the item exists, finds it doesn't, and starts preparing the insert.
• Before Thread 1 calls SaveChanges(), Thread 2 does the exact same check and also finds the item doesn't exist.
• Both threads try to insert the same item, and one hits the unique constraint violation.

On top of that, there are a couple of other pain points:

• Per-item SaveChanges(): Calling SaveChanges() inside the loop means you're hitting the database once per item, which is slow and increases the window for race conditions.
• Fragile exception handling: Checking if the error message contains "UNIQUE KEY" is unreliable—database error messages can change between versions, and this won't work if you switch databases later.

Fixes to Eliminate the Race Condition #

Let's go through the most effective solutions, ordered by how well they solve the root problem:

1. Use Database-Level UPSERT (Best Approach)

The only way to truly make this atomic is to let the database handle the check-and-insert in a single operation. For SQL Server (since you're using Entity Framework with mobileEntities), use the MERGE statement to perform an atomic "upsert" (update if exists, insert if not).

Here's how you could implement this with EF by executing raw SQL (you can batch these calls for even better performance):

foreach (var item in stopPieces)
{
    var sql = @"
        MERGE INTO mobile_Item_Detail AS target
        USING (VALUES (@MobileStopsId, @UniqueScanCode, @ItemDescription, @LobItemDetailId, @DtSeqNo))
            AS source (mobile_stops_id, unique_scan_code, item_description, LOB_item_detail_id, dt_seq_no)
        ON target.mobile_stops_id = source.mobile_stops_id AND target.unique_scan_code = source.unique_scan_code
        WHEN NOT MATCHED THEN
            INSERT (mobile_stops_id, unique_scan_code, item_description, LOB_item_detail_id, dt_seq_no)
            VALUES (source.mobile_stops_id, source.unique_scan_code, source.item_description, source.LOB_item_detail_id, source.dt_seq_no);
    ";

    mdbPcs.Database.ExecuteSqlCommand(
        sql,
        new SqlParameter("@MobileStopsId", mstopId),
        new SqlParameter("@UniqueScanCode", item.item_detail.unique_scan_code),
        new SqlParameter("@ItemDescription", item.item_detail.item_description),
        new SqlParameter("@LobItemDetailId", item.item_detail.Id),
        new SqlParameter("@DtSeqNo", item.item_detail.dt_item_seq_no)
    );
}

This way, the database handles the check and insert in one atomic step—no race condition possible.

2. Batch Changes + Robust Exception Handling

If you want to stick with EF's change tracking, first collect all potential items to add, then save once at the end. Also, replace the fragile message check with specific SQL error code handling (2601 or 2627 are the unique constraint violation codes for SQL Server):

var itemsToAdd = new List<mobile_Item_Detail>();

foreach (var item in stopPieces)
{
    int seek = (from s in mdbPcs.mobile_Item_Detail 
                where s.mobile_stops_id == mstopId && s.unique_scan_code == item.item_detail.unique_scan_code 
                select s.Id).FirstOrDefault();
                
    if (seek == 0)
    {
        var newItem = new mobile_Item_Detail
        {
            item_description = item.item_detail.item_description,
            LOB_item_detail_id = item.item_detail.Id,
            mobile_stops_id = mstopId,
            dt_seq_no = item.item_detail.dt_item_seq_no,
            unique_scan_code = item.item_detail.unique_scan_code
        };
        itemsToAdd.Add(newItem);
    }
}

if (itemsToAdd.Any())
{
    try
    {
        mdbPcs.mobile_Item_Detail.AddRange(itemsToAdd);
        mdbPcs.SaveChanges();
    }
    catch (SqlException ex)
    {
        // Check for unique constraint error codes
        if (ex.Number is 2601 or 2627)
        {
            Console.WriteLine($"{DateTime.Now} Unique constraint hit for stop {mstopId} - some items already exist");
            // Optionally, you could retry or log specific items, but for now, continue
        }
        else
        {
            throw; // Re-throw other SQL errors
        }
    }
}

This reduces database round-trips but doesn't fully eliminate the race condition—it just makes it less likely. The UPSERT approach is still the better choice for guaranteed safety.

3. Queue-Level Deduping

Since you're using Azure Queue Storage, you could add a layer of protection by ensuring duplicate messages aren't processed multiple times. For example:

• Use a separate tracking table to log which queue message IDs have been processed.
• Set a visibility timeout on queue messages to prevent concurrent processing of the same message.

This adds another safety net but shouldn't replace the database-level fix.

Final Notes #

Double-check that your unique constraint is correctly defined on the pair (mobile_stops_id, unique_scan_code)—that's the key combination that should enforce uniqueness, right?

The database UPSERT method is the gold standard here because it eliminates the race condition at the source. The other options improve efficiency or error handling, but only the atomic database operation guarantees no unique constraint violations from concurrent inserts.

内容的提问来源于stack exchange，提问作者Joe Ruder