How to configure a lock for a read-manipulate-write operation?

Question

Given the following table

The sequence value is a custom auto-increment key that combines letters and numbers needed by a particular client for his system.

We created the GetNextSequence () function, which should return the next value of the sequence. The step for reading and updating the sequence is as follows

• Read the sequence value using KeyId: SELECT Sequence FROM [Key] WHERE KeyId = @Id
• Parse the value of the sequence and determine the following value
• Enter the sequence value in the table: UPDATE [Key] SET Sequence = @Sequence WHERE KeyId = @Id

Here is the C # code (simplified for clarity):

var transaction = connection.BeginTransaction(IsolationLevel.RepeatableRead);
var currentSequenceValue = SqlUtils.ExecuteScalar(connection, transaction, "SELECT Sequence FROM [Key] WHERE KeyId = @Id", new SqlParameter("@Id", keyId));
var updatedSequenceValue = ParseSequence(currentSequenceValue);
SqlUtils.ExecuteScalar(connection, transaction, "UPDATE [Key] SET Sequence = @Sequence WHERE KeyId = @Id", new SqlParameter("@Id", keyId), new SqlParameter("@Sequence", updatedSequenceValue));
transaction.Commit();
return updatedSequenceValue;

Our problem is that two different servers can access the same sequence, and we get a dead end

( X) . .

# , IsolationLevel.RepeatableRead IsolationLevel.Serializable SQL ROWLOCK HOLDLOCK, .

, , . ?

Answer 1

(ROWLOCK, XLOCK, HOLDLOCK). ..

BEGIN TRAN
    SELECT Sequence FROM [Key] WITH (ROWLOCK, XLOCK, HOLDLOCK) WHERE KeyId = @Id

    Parse the sequence value and determine the next value

    UPDATE [Key] SET Sequence = @Sequence WHERE KeyId = @Id
COMMIT

, , ,

    UPDATE [Key] WITH (ROWLOCK, XLOCK, HOLDLOCK)
    SET Sequence = dbo.scalarudf(...)
    WHERE KeyId = @Id

: HOLDLOCK, SERIALIZABLE. "RepeatableRead" - ,

Answer 2

, , , , .

, A X. B , A " " ( ). A , B , , B. B tols, , A . , Write.

- ; XLOCK. - , , , , , -, . , , , , " ", , , , .

(ROWLOCK), ; EVERYBODY , , .