SQL单语句实现TrnNumber自增并返回值，多客户端并发冲突咨询

Great question—your worry about concurrent clients causing wrong ID values is totally justified. The code you're using right now has a critical race condition because the UPDATE and SELECT are separate operations. Let me break down the problem and show you the fix.

The Problem with Your Current Approach #

Your current SQL runs two disconnected statements: first it increments TrnNumber for all rows in TrnDocuments, then it selects the TrnNumber values. In a multi-client scenario:

• Client A runs the UPDATE
• Before Client A runs the SELECT, Client B runs the UPDATE
• Both clients end up selecting the same (already incremented twice) value, or get mismatched results depending on timing

Even if you meant to update a single row (I assume you do—updating all rows every time is rare!), the split between update and select still leaves a window for concurrency conflicts.

The Fix: Use SQL's OUTPUT Clause for Atomic Operations #

The safest way to increment and retrieve the value in one atomic step is to use SQL Server's OUTPUT clause. This ties the update and value retrieval into a single, locked operation that the database will handle without race conditions.

Here's the corrected SQL and C# code:

Step 1: The Atomic SQL Statement

First, make sure you target the specific row you want to update (add a WHERE clause—never run an UPDATE without one unless you intentionally want to modify every row):

UPDATE TrnDocuments
SET TrnNumber = TrnNumber + 1
OUTPUT inserted.TrnNumber
WHERE YourPrimaryKeyColumn = @RecordId -- Replace with your actual primary key column and parameter

Step 2: Updated C# Code

Use ExecuteScalar() to get the returned value directly, since we're only expecting one result:

// Assuming your connection is handled properly with using blocks
using (SqlCommand cmd2 = new SqlCommand(@"
    UPDATE TrnDocuments
    SET TrnNumber = TrnNumber + 1
    OUTPUT inserted.TrnNumber
    WHERE YourPrimaryKeyColumn = @RecordId", con))
{
    // Add the parameter to target the correct row
    cmd2.Parameters.AddWithValue("@RecordId", yourTargetRecordId); // Replace with your actual record ID

    // Execute and get the updated value
    int updatedTrnNumber = (int)cmd2.ExecuteScalar();

    // Use updatedTrnNumber here—this is the safely incremented value
}

Why This Works #

The OUTPUT clause executes as part of the UPDATE operation atomically. SQL Server will lock the targeted row(s) for the duration of the operation, ensuring no other client can modify the row until your update and value retrieval are complete. This eliminates the race condition entirely.

Bonus: If You're Using a Single Row for Sequencing #

If your TrnDocuments table is actually a single-row sequence generator (like a ticket counter), the same approach applies—just use a WHERE clause that targets that single row (e.g., WHERE Id = 1). The atomicity of UPDATE ... OUTPUT still guarantees you get a unique, correct value every time.

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