Is my queue table implementation race condition safe?

Hello people smarter than me! I've created a sort-of-a-queue table system, but it seems too simple to be safe from race conditions. Am I missing something or is the following race condition safe?
The Schema

I have a table, let's call it ProductQueue:

CREATE TABLE dbo.ProductQueue
(
    SerialId BIGINT PRIMARY KEY,
    QueuedDateTime DATETIME NOT NULL -- Only using this for reference, no functionality is tied to it
);

I have procedure for adding to the queue called AddToProductQueue:

CREATE PROCEDURE dbo.AddToProductQueue (@SerialId BIGINT)
AS
BEGIN
    INSERT INTO dbo.ProductQueue (SerialId, QueuedDateTime)
    OUTPUT Inserted.SerialId
    SELECT @SerialId, GETDATE();
END

I also have a procedure for removing from the queue called RemoveFromProductQueue:

CREATE PROCEDURE dbo.RemoveFromProductQueue (@SerialId BIGINT)
AS
BEGIN
    DELETE FROM dbo.ProductQueue
    OUTPUT Deleted.SerialId
    WHERE SerialId = @SerialId;
END

Note, SerialId is globally unique for a Product in the source database / system. I.e. no two instances of a Product can ever have the same SerialId. That's the extent of it on the database side.
The Workflow

• I have an application process that runs hourly.

• That process gets a variable list of SerialIds from the source system.

• It iteratively calls the AddToProductQueue procedure on each SerialId in its list.

• If the procedure tries to insert a SerialId that exists in the ProductQueue table already, it throws a primary key violation error, and the application process catches that error and skips that SerialId.

• Otherwise, the procedure successfully adds that SerialId to the ProductQueue table and returns it back to the application process.

• The application process then adds that successfully queued SerialId to a separate list.

• After the application process finishes iterating its list of all candidate SerialIds to enqueue, it then iterates its new list of successfully queue

The only thing you're asking the database engine to do in this scenario is to enforce the PRIMARY KEY. It will do that under all conditions and isolation levels, of course.

The potential race conditions are all external to the database, considerations that aren't really on-topic here.

That said, the only way I can think of the database being involved in a race condition is for the process of adding candidate serial IDs to be wrapped in a database transaction, but you didn't mention anything about that.

Perhaps it's possible for the processes to use a database transaction in an unintended way, for example if you're using an ORM that does helpful things by magic without being explicitly asked. Or perhaps you are using implicit transactions.

In that scenario, app instance A would start adding its (long list of) serial IDs all within a single database transaction. Meanwhile, app instance B (with an equally long list, including at least one present in A's list) would become blocked on the insert of an overlapping serial ID (because instance A hasn't committed its transaction yet).

In an unfortunate sequence of events, instance A would finish asynchronous processing a duplicate serial ID (toward the start of its list) before blocked instance B can perform its insert (towards the end of its list).

In that case, both A and B would successfully process the same serial ID.

This seems unlikely, given the process outline you have described but not impossible.

StackExchange Database Administrators Q#337710, answer score: 9