5

Considering the following table

Key(KeyId int, Sequence varchar(14))

The sequence value is a custom auto increment key combining letters and numbers that a particular client need for his system.

We made a function called GetNextSequence() which should return the next value of the sequence. The step to reading and updating the sequence goes as follow

  1. Read the sequence value using the KeyId: SELECT Sequence FROM [Key] WHERE KeyId = @Id
  2. Parse the sequence value and determine the next value
  3. Write the sequence value to 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 reside in that two different servers can access the same sequence and we end up getting a deadlock

Transaction (Process ID X) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In C#, I tried to set up different lock combination like a transaction isolation IsolationLevel.RepeatableRead or IsolationLevel.Serializable or in SQL using table hint ROWLOCK and HOLDLOCK, but without success.

I want that each server be able to read, manipulate and update a sequence in a atomic way. What is the proper way to setup a lock for this situation?

Improve this question
1

2 Answers 2

Reset to default
2

I suggest using an exclusive row-level lock for the duration of a transaction (ROWLOCK, XLOCK, HOLDLOCK). Your use of hints etc so far isn't enough.

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

Although, I'd look at at least reducing scope to a single transaction

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

Edit: you don't need HOLDLOCK if you use SERIALIZABLE. And "RepeatableRead" may not be enough because of how ranges are locked

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Oh, I should specify that the "Parse sequence" is made in C# and not in SQL Server.
@Pierre-Alain Vigeant: you still need the hints and transactions. Can you move the c# to a CLR fucntion or into SQL?
It seem to work with ROWLOCK, XLOCK, HOLDLOCK, but I need further testing which I will do tomorrow.
Voted as accepted answer since you were the first one to give the combination of ROWLOCK, XLOCK, HOLDLOCK. While I cannot move the ParseSquence to sql server, it does work for the duration of the transaction.
2

The problem is that the default locks being acquired for reading do not avoid race conditions, because multiple read locks can be acquired on the same record.

The situation is, process A acquires a Read lock on row X. Process B then acquires a reader lock while A is working on its "client side" (within the server program). A then requests an upgrade to a Write lock while B is working on the client side, at which point it is told to wait until B's read lock is released. B then requests a Write lock and is tols to wait until A releases its Read. Both are now waiting on the other so they can acquire the more exclusive Write lock.

The solution is an exclusive lock; you can specify this using the XLOCK hint. An exclusive lock is basically a Write-level lock acquired for a read, and is used in this exact case, where you expect to write something you're reading. As noted in the comments, an exclusive lock is only maintained if the statement is executed in the scope of an explicit transaction, so make sure you are setting one up during the "unit of work" that is reading the value, determining how to advance it, and then updating it.

I would use this at the row level (ROWLOCK), unless you are updating a lot of similar sequences all at once; acquiring a page or table-level exclusive lock makes EVERYBODY wait for data that you don't need if you're only working on one row per transaction.

Improve this answer

3 Comments

XLOCK only persists for the current statement unless in an explicit transaction. It isn't enough by itself. So if used for the SELECT, you can still deadlock on the UPDATE.
I was assuming an explicit transaction; the function GetNextSequence() is, from the word go, an atomic "unit of work".
True, but it should be more clear about persistence of the exclusive lock

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.