SQL problem - violation of large volume and PC

Question

I am writing a large volume trading system. We receive messages at a speed of about 300-500 per second, and these messages should then be stored in the database as quickly as possible. These messages are deposited in the message queue and then read from there.

I implemented a competing consumer template that reads from a queue and allows multi-threaded message processing. However, while the application is running, I get frequent primary key violations.

We are launching SQL 2008. Sample table structure:

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

A stored procedure is called to save 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 this is in the TRY ... CATCH block, so if there is an error, it rolls back the transaction.

, ROWLOCK, . , , " ".

- , ? , ?

Answer 1

?

SELECT TOP 1 MessageSequence FROM TableA WHERE MessageSequence = @MessageSequence

SELECT , , EXISTS FALSE INSERT . , INSERT , SELECT, , INSERT. , , @MessageSequence, NOT EXISTS, INSERT, , PK.

?

, WITH (UPDLOCK) SELECT, , @MessageSequence, , , INSERT/SELECT :

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

SQL , , ROWLOCK.

. , : , , , , , . . - @MessageSequnce, INSERT PK, . , , hte catch/retry , .

, , , SQL Server.

Answer 2

. :

: IF

Answer 3

. ,

.

, , , .

Answer 4

939831. (ROWLOCK, READPAST, UPDLOCK). READPAST sql- , . UPDLOCK sql, .

- , threadID

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

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

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

, .

Answer 5

:

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

.

Answer 6

, , , . [ , "" ], , : http://www.elitetrader.com/vb/showthread.php?threadid=81345.

, , . kdb + .

Answer 7

, , , MQ.

, TM ( ), : 1) MQ 2) Write to DB .

, , (, DTC Microsoft).

• MessageSequence - PK, MQ .
• When you execute a “GET” from MQ, make sure that the GET is fixed (ie not the db-commit bit, but the MQ-commit) - this ensures that the same MessageID cannot be “pushed” by the next thread that writes messages in the database.