SQL problem - high volume trans and PK violation

Question

I'm writing a high volume trading system. We receive messages at around 300-500 per second and these messages then need to be saved to the database as quickly as possible. These messages get deposited on a Message Queue and are then read from there.

I've implemented a Competing Consumer pattern, which reads from the queue and allows for multithreaded processing of the messages. However I'm getting a frequent primary key violation while the app is running.

We're running SQL 2008. The sample table structure would be:

TableA
{
    MessageSequence INT PRIMARY KEY,
    Data VARCHAR(50)
}

A stored procedure gets invoked to persist this message and looks something like this:

BEGIN TRANSACTION

INSERT TableA(MessageSequence, Data )
SELECT @MessageSequence, @Data
WHERE NOT EXISTS
(
  SELECT TOP 1 MessageSequence FROM TableA WHERE MessageSequence = @MessageSequence
)

IF (@@ROWCOUNT = 0)
BEGIN

UPDATE TableA
SET Data = @Data
WHERE MessageSequence = @MessageSequence

END

COMMIT TRANSACTION

All of this is in a TRY...CATCH block so if there's an error, it rolls back the transaction.

I've tried using table hints, like ROWLOCK, but it hasn't made a difference. Since the Insert is evaluated as a single statement, it seems ludicrous that I'm still getting a 'Primary Key on insert' issue.

Does anyone have an idea why this is happening? And have you got ANY ideas which may point me in the direction of a solution?

Answer 1

Why is this happening?

SELECT TOP 1 MessageSequence FROM TableA WHERE MessageSequence = @MessageSequence

This SELECT will try to locate the row, if not found the EXISTS operator will return FALSE and the INSERT will proceed. Hoewever, the decision to INSERT is based on a state that was true at the time of the SELECT, but that is no longer guaranteed to be true at the time of the INSERT. In other words, you have race conditions where two threads can both look up the same @MessageSequence, both return NOT EXISTS and both try to INSERT, when only the first one will succeed, second one will cause a PK violation.

How do I solve it?

The quickest fix is to add a WITH (UPDLOCK) hint to the SELECT, this will enforce the lock placed on the @MessageSequence key to be retained and thus the INSERT/SELECT to behave atomically:

INSERT TableA(MessageSequence, Data )
   SELECT @MessageSequence, @Data
   WHERE NOT EXISTS (
      SELECT TOP 1 MessageSequence FROM TableA WITH(UPDLOCK) WHERE MessageSequence = @MessageSequence)

To prevent SQL from doing fancy stuff like page lock, you can also add the ROWLOCK hint.

However, that is not my recommendation. My recommendation may surpise you, but is this: do the operation that is most likely to succeed and handle the error if it failed. Ie. if your business case makes it more likely for the @MessageSequnce to be new, try an INSERT and handle the PK if it failed. This way you avoid the spurious look-ups, and hte cost of the catch/retry is amortized over the many cases when it succeeds from the first try.

Also, it is perhaps worth investigating using the built-in queues that come with SQL Server.

Answer 2

Common problem. Explained here:

Defensive database programming: eliminating IF statements