CODEWITHSUNDEEP
sqlsql-server
Brynjar Bragason
Brynjar Bragason

Reputation: 53

Multiple inserts happening at the same time SQL Server

I have the problem that I'm getting multiple inserted rows where only 1 row should be inserted.

I have a procedure similar to this but the procedure is being executed multiple times with the same input parameters at the same moment by different sources so we are getting multiple duplicated lines with the same data.

Edit: more info, we are getting multiple different events and each event might execute this procedure if it has a new server_id that has not been added to the table before. Sometimes we get multiple events that all have the same server_id which has not yet been entered into the database so they all try to enter it into the db with this procedure at the same time and we get multiple rows in the table

CREATE PROCEDURE [stuff].[p_insert_missing_data]
    @in_event_timestamp DATETIME2(7),
    @in_source_system   NVARCHAR(50),
    @in_server_id       NVARCHAR(50)
AS
BEGIN
    INSERT INTO house.servers (
        xtract_datetime, 
        xtract_source, 
        server_id
    )
    SELECT GETDATE()           AS xtract_datetime,
           @in_source_system   AS xtract_source,
           @in_server_id       AS server_id
    WHERE
        NOT EXISTS (SELECT 1 FROM house.servers WHERE server_id = @in_server_id)
END

Upvotes: 1

Views: 1584

Answers (1)

Charlieface
Charlieface

Reputation: 72060

To ensure you do not get any chance of a race condition, where multiple processes try to insert the same row, you need SERIALIZABLE isolation level. You can set this on the whole procedure, or better is to add an explicit hint to just that table reference WITH (SERIALIZABLE) or WITH (HOLDLOCK). The server will then place a key range lock, which not only locks that key, it also locks the absence of the key. It will also hold that lock to the end of the transaction (statement if no transaction).

This has the downside that there is more locking, increasing the risk of deadlocks. Therefore you should also add a UPDLOCK hint.

 INSERT INTO house.servers (
     xtract_datetime, 
     xtract_source, 
     server_id
 )
 SELECT GETDATE(),
        @in_source_system,
        @in_server_id
 WHERE
     NOT EXISTS (SELECT 1
         FROM house.servers s WITH (SERIALIZABLE, UPDLOCK)
         WHERE s.server_id = @in_server_id);

You do not need a transaction, as every statement implicitly runs in its own transaction anyway.

For this to only place a key lock, and not lock the whole table up, you also need an index. This can either be clustered or non-clustered, although server_id I'm guessing is the primary key, therefore should normally be clustered anyway.

house.servers (server_id ASC)

Upvotes: 1

Related Questions