CODEWITHSUNDEEP
c#sql-server-2008
Pierre-Alain Vigeant
Pierre-Alain Vigeant

Reputation: 23103

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

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?

Upvotes: 5

Views: 1465

Answers (2)

KeithS
KeithS

Reputation: 71591

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.

Upvotes: 2

gbn
gbn

Reputation: 432672

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

Upvotes: 2

Related Questions