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

It might be related to the transaction isolation level. You might need

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

before you start the transaction.

Also, if you have more updates than inserts, you should try the update first and check rowcount and do the insert second.

Answer 2

This is very similar to post 939831. Ultimately you want to use the hints (ROWLOCK, READPAST, UPDLOCK). READPAST tells sql server to skip to the next record if the current one is locked. UPDLOCK tells sql server that the read lock is going to escalate to an update lock.

When I implemented something similar I locked the next record by the threadID

UPDATE TOP (1)
    foo
SET
    ProcessorID = @PROCID
FROM
    OrderTable foo WITH (ROWLOCK, READPAST, UPDLOCK)
WHERE
    ProcessorID = 0

Then selected the record

SELECT *
FROM foo WITH (NOLOCK)
WHERE ProcessorID = @PROCID

Then marked it as processed

UPDATE foo
SET ProcessorID = -1
WHERE ProcessorID = @PROCID

Later in off hours I perform the relatively expensive operation of performing the delete operation to clear the queue of processed records.

Answer 3

Common problem. Explained here:

Defensive database programming: eliminating IF statements

Answer 4

The atomicity of the following statement is what you are after:

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

According to this person, it depends on the current isolation level.

Answer 5

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 6

On a tangent, if you're thinking of a high volume trading system you might want to consider a tick database designed for such data [I'm not exactly sure what "message" you are storing here], such as discussed in this thread for example: http://www.elitetrader.com/vb/showthread.php?threadid=81345.

These are typically in-memory solutions with proprietary query languages. We use kdb+ at our shop.

Answer 7

Not sure what Messaging product you use - but it may be worth looking at the transactions not at the DB level, but at the MQ Level.

Of course, if you are using a TM (Transaction manager), the two operations : 1)Get from MQ and 2)Write to DB are both 'bracketed' under the same parent commit.

So I am not sure if you are using an implicit or explicit or any TM here (for example, Microsoft's DTC).

• MessageSequence is the PK, so could the same Message from the MQ be getting processed twice.
• When you perform a 'GET" from MQ, make sure the GET is committed (i.e. not a db-commit, but a MQ-commit) - that will ensure the same MessageID cannot be 'popped' by the next thread that writes messages to the DB.