Как установить блокировку для операции чтения-изменения-записи?

Учитывая следующую таблицу

Значение последовательности представляет собой настраиваемый ключ автоматического увеличения, объединяющий буквы и цифры, которые нужны конкретному клиенту для его системы.

Мы создали функцию под названием GetNextSequence ( ), который должен вернуть следующее значение последовательности. Этап чтения и обновления последовательности выполняется следующим образом

1. Считывание значения последовательности с использованием KeyId: ВЫБРАТЬ Последовательность FROM [Key] ГДЕ KeyId = @Id
2. Анализировать значение последовательности и определять следующее значение
3. Запишите значение последовательности в таблицу: UPDATE [Key] SET Sequence = @Sequence WHERE KeyId = @Id

Вот код C # (упрощенный для ясности):

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;

Наша проблема заключается в двух разных серверы могут получить доступ к той же последовательности, и мы в конечном итоге получаем взаимоблокировку

. Транзакция (ID процесса X) была заблокирована на ресурсах блокировки с другим процессом и была выбрана жертвой взаимоблокировки. Повторите транзакцию.

В C # я попытался установить другую комбинацию блокировки, например, изоляцию транзакции IsolationLevel.RepeatableRead или IsolationLevel. Сериализуемый или в SQL с использованием табличной подсказки ROWLOCK и HOLDLOCK , но безуспешно.

Я хочу, чтобы каждый сервер мог читать, управлять и обновлять последовательность в атомный способ. Как правильно установить блокировку в этой ситуации?