5

I have table Messages that has fields, and another table called SentMessages, which has a one-to-one relationship to the Messages table.

Messages stores information about the message, including its text, and subject, and the recipient, etc.

create table Messages
(
    Id bigint not null identity(1, 1) primary key,
    Text nvarchar(max),
    Subject nvarchar(400)
    -- other fields
)

SentMessages stores information related to the transportation of the message to the clients.

create table SentMessages
(
    Id bigint not null primary key,
    SentOn datetime not null,
    DeliveredOn datetime null,
    -- other fields
)
go
alter table SentMessages with check add constrait [FK_SentMessages_Messages] 
foreign key([Id])
references Messages ([Id])

They are designed to be separate, so please no advice on merging them.

Now the logic is to find all messages that are not sent yet, and send them. Basically a simple not in clause in a single-threaded scenario would do the trick:

select *
from Messages
where Id not in 
(
    select Id 
    from SentMessages
)

But this database is used by many concurrent threads, therefore it's possible for a message to be taken by more than one thread, hence be sent twice or even more.

If they were a single table, I could use update select statement to select unsent messages in a single transaction.

What options do I have though to make selection transactional, so that a message won't be sent twice?

4

You can't have the cake and leave it whole; on one hand, you want to allow maximal concurrency, and on the other hand, you want each process to be 'isolated' and not visible to other processes. SQL Server offers Service Broker exactly for these queuing / asynchronous processing challenges. Check it out, it is an awesome infrastructure that will most likely work better than any custom user application solution.

Before we had Service Broker, what we used to do for similar challenges was create a 3rd table, call it "Email Send Queue" or something like that. Let it hold just the IDs of the messages that need to be sent, so every time you enter it into 'messages', also enter the ID to the queue.

When a process needs to pick a message to send, you open an explicit transaction in the REPEATABLE READ isolation level, and hold it open until the sending process completes. Every new process that needs to pick a job, uses the

SELECT TOP 1 ID FROM EmailSendQueue WITH (READPAST) ORDER BY ID ASC

This way, the reading process will simply skip locked rows, and take the next one that is not yet locked, i.e. - not yet being processed. Even if you were able (and you can BTW...) hold explicit locks on the original tables, without the READPAST trick, it would be a recipe for blocking hell. Here is a sample, simplified, untested code to do the trick.

CREATE TABLE [EmailSendQueue]
(
[MessageID] BIGINT NOT NULL 
-- Better use a heap here to prevent page level resrouce contention 
PRIMARY KEY NONCLUSTERED
REFERENCES [Messages] (ID)
-- In case someone deletes a message that has not been sent yet, 
-- and to prevent deleting a message which is in process
ON DELETE CASCADE 
ON UPDATE NO ACTION
);

-- New messages into queue when ready to send - allows delayed send logic as well...
INSERT INTO [EmailSendQueue]([MessageID]) 
VALUES (1), (2), (3);

-- First Process picks up #1 
-- Second process will pick up #2, if #1 is still locked, or is completed...
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;

DECLARE @MessageID BIGINT;

SELECT TOP (1) @MessageID = [MessageID] 
FROM [EmailSendQueue] WITH (READPAST, XLOCK)
ORDER BY [MessageID] ASC;

DECLARE @StartTime DATETIME = GETDATE();

-- Process and send message
EXECUTE [SendMessageProcedure] @MessageID; 

-- Send successful
IF @@Error = 0
BEGIN
-- Now the message has been sent, and can go into SentMessages
    INSERT INTO [SentMessages] ([ID], [SendOn], [DeliveredOn])
    VALUES (@MessageID, @StartTime, GETDATE());
    DELETE FROM [EmailSendQueue] WHERE [MessageID] = @MessageID
END;

-- If send unsuccesful, you can do nothing, or introduce some kind of retry logic
-- You can add a 'Retry' column to the queue, and increment it on every attempt 
-- A scheduled backgrond process can delete all messages that  exceed a retry threshold
-- This will prevent 'poisoning' the queue with error messages

COMMIT TRANSACTION;
2

I would add one column on Messages table: SendingStatus. This new column would be updated once one thread get the message to be send. Once that the sending process finish, the Thread update the status again to sent.

To pass only one ID to the thread, we can use the OUTPUT statement from update and rely on our concurrency control.

CREATE table dbo.Messages
(
    Id bigint not null identity(1, 1) primary key,
    Text nvarchar(max),
    Subject nvarchar(400)
    -- other fields
    ,SendingStatus tinyint DEFAULT 0 -- 0 = Not sent, 1 = Sending, 2 = Sent
)
GO

-- Sample data
INSERT INTO dbo.Messages VALUES (N'Bla bla bla', N'kkkkk',0)
INSERT INTO dbo.Messages VALUES (N'2 Bla bla bla', N'222222',0)


-- Get message to send
DECLARE @MySendQueue table (Id bigint NULL)

UPDATE TOP (1) dbo.Messages
SET SendingStatus = 1 -- Sending
OUTPUT Inserted.Id INTO @MySendQueue
WHERE SendingStatus = 0

SELECT * FROM @MySendQueue -- Table with the ID that this thread got!


-- At the end, set message as sent
UPDATE dbo.Messages
SET SendingStatus = 2 -- Sent
WHERE Id IN (SELECT id FROM @MySendQueue)

Using this strategy, you could even use your thread to send more than one message, balancing your load by just changing the TOP (K) statement on the update.

You could also add a filtered index SendingStatus = 0 on Messages to improve the performance.

Last, but not least, don't forget to back SendingStatus to 0 if your sending thread fails. This could be done by a catch on your application or by a monitor process to avoid messages stuck on Sending status. Anyway, it depends on your application architecture.

  • 1
    In addition to your answer, a filter index with SendingStatus = 0, I think will help – Sabin Bio May 17 '18 at 15:27
  • You read my mind. I'll add this to the answer. Thanks! – Evandro Muchinski May 17 '18 at 15:36
  • And for high concurrency , I like to test it with some of hints : rowlock, updlock, readpast... – Sabin Bio May 17 '18 at 15:39
  • Don't use readpast, or we could not rely on the concurrency control to avoid 2 threads getting the same Id =) – Evandro Muchinski May 17 '18 at 15:44
1

Another option - add a guid column to the Messages table called 'reservation'. When a thread wants to send a message, it first creates a guid and then executes update top(1) messages set reservation=@newguid where reservation is null. This is very transactional and concurrent. After that it selects back the row it updated by select * from messages where reservation=@newguid. Simple.

For bonus points you can also add a timestamp when it was reserved, and if the processing takes too long (meaning the sending process crashed), another worker can take over. This can be added in the same update query above.

0

I do it more or less the same way as you, and I don't get duplicate messages.

How are you actually sending the messages out? If you're doing them in batches and getting duplicates, then my guess would be that one batch is starting before the previous batch has been written to the SentMessages table.

To get around this, I'd start with smaller batches - or slow down the frequency of the job, so that the last batch has been written as sent before the next batch starts.

If that won't work, I'd put it in a cursor that looks something like:

    declare @ID int

    declare cursor c local for

    select ID from Messages
    where create_time >= dateadd(hour, -1, current_timestamp)

    open c

    fetch from c into @ID

    while @@FETCH_STATUS = 0

    begin

    fetch from c into @ID

    if @ID not in (select ID from sent_messages) 
        begin
        sp_do_your_thing @ID

        insert into sent_messages (ID)
        select @ID
        end

    fetch from c into @ID
    end

    close c
    deallocate c

I'd also make sure that I had an index on the ID's and timestamps.

  • 4
    I think OP is looking for some locking guarantees that simultaneous reads don't double up on work, like using UPDLOCK and HOLDLOCK when reading data. – Erik Darling May 17 '18 at 15:14
  • Well, it does not seem to solve the multithreading problem. How this avoid duplicate messages if you have 10 processes running this code at the same time? (PS: Cursors?! Are they still alive?) – Evandro Muchinski May 17 '18 at 15:41
  • @EvandroMuchinski if you're checking each ID right before you send it, then immediately writing it to the sent table, you are very unlikely to get duplicate sent messages. And yep, cursors can be very useful. – James May 17 '18 at 15:44
  • 1
    I disagree. The sending process will take some time before inserting on the SentMessages. In a high concurrent application, it will send duplicate messages for sure. But well, that is up to @SaeedNeamati to check – Evandro Muchinski May 17 '18 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.