Sql Stored Procedure and records in "use"

How do I make sure the replica app or second task doesn't pull the same emails into it's own 10,000 addresses recordset?

If you work inside a transaction the records will be locked and no other process can access them until you release the lock with a COMMIT or ROLLBACK command.

12k rows is pretty few for SQL Server. No need for a replica.

You need to look at the bottleneck: Can that 3rd party API handle this? How long does your current program needs to send one mail? What size has one email? Do you have the necessary bandwidth?

My first attempt after looking at this information and those numbers would be distributing the load over more SMTP sender processes. Cause my tingling developer sense tell me, that it is just here, where we need to do something. Often a simple round-robin does the trick:

Just select rows by user modulo number_of_running_senders  and give each process a unique counter.

You basically need a look-up/log table where the first process logs the Ids for the addresses being fetched.

When the replica app runs, it should check (by looking at the log table) that it is processing only values that the other app has not processed.

NOTE: Depending upon your implementation, you may want to clear out/truncate the log table once all E-mails have been sent.

Here's a quick sample.

USE tempdb;
GO
--Safety Check
IF OBJECT_ID('dbo.AddressTable','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.AddressTable;
END
GO

--Safety Check
IF OBJECT_ID('dbo.LogTable','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.LogTable;
END
GO

--Safety Check
IF OBJECT_ID('dbo.proc_SendEmails','P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.proc_SendEmails;
END
GO

--Create required objects
CREATE TABLE dbo.AddressTable 
       ([PersonId]     INT           NOT NULL,
        [EmailAddress] NVARCHAR(255) NOT NULL,
        [Status]       BIT           NOT NULL
       );
GO

CREATE TABLE dbo.LogTable
      ([PersonId]    INT      NOT NULL,
       [ProcessDate] DATETIME NOT NULL
      );
GO
  
--Assume this SP is sending the E-mails                       
CREATE PROCEDURE dbo.proc_SendEmails 
        @batchSize INT
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #someTable ([DummyValue] INT NULL,
                             [DummyDate]  DATETIME NULL
                            )

    --When you fetch the E-mails to process,
    --log the Ids into your log table
    INSERT INTO #someTable (DummyValue, DummyDate)
        OUTPUT inserted.DummyValue, 
               inserted.DummyDate  
        INTO dbo.LogTable ([PersonId], [ProcessDate])
    SELECT TOP (@batchSize) 
           at.PersonId AS DummyValue,
           GETDATE()   AS DummyDate
    FROM dbo.AddressTable AS at
    LEFT OUTER JOIN dbo.LogTable AS lt ON at.PersonId = lt.PersonId
    WHERE at.[Status] = 1
      AND at.PersonId IS NOT NULL
      AND lt.PersonId IS NULL;
END
GO

--Generate some test data. NOTE: I have taken test data from the AdventureWorks sample database
USE tempdb;
GO
INSERT INTO dbo.AddressTable (PersonId, EmailAddress, [Status])
SELECT ea.BusinessEntityID,
       ea.EmailAddress,
       CASE (ea.BusinessEntityID % 2) WHEN 1 THEN 1 ELSE 0 END AS [Status]
FROM AdventureWorks2012.Person.EmailAddress AS ea;
GO

--Now, keep running the SP and observe the values logged in dbo.LogTable
USE tempdb;
GO
EXEC dbo.proc_SendEmails @batchSize = 5;
GO

Select all Open in new window

If status is an integer data type, you could set the status to a value other than 0 or 1 to indicate the record is being processed when you retrieve the records. After the email is sent you could then set the status to 1.

Fyi, you can mark the rows and pull data from them in the same statement:


DECLARE @unique_id_for_this_process_batch uniqueidentifier
SET @unique_id_for_this_process_batch = NEWID()

/* Mark rows as in-process while pulling details from them so that they can actually be processed. */
UPDATE TOP (10000) tn
SET process_start_date = GETDATE() /*, process_id = @unique_id_for_this_process_batch */
OUTPUT @unique_id_for_this_process_batch,
     INSERTED.process_start_date, INSERTED.id, ...<other_columns_needed_for_processing>
    INTO dbo.inprocess_data ( <column_names> )
FROM dbo.table_name tn
WHERE
    tn.process_start_date IS NULL

<code to process rows in "dbo.inprocess_data" table for the current unique id>