Only inserting a row if it's not already there

Question

Hello,

I had always used something similar to the following to achieve it:

INSERT INTO TheTable
SELECT
    @primaryKey,
    @value1,
    @value2
WHERE
    NOT EXISTS
    (SELECT
        NULL
    FROM
        TheTable
    WHERE
        PrimaryKey = @primaryKey)

...but once under load, a primary key violation occurred. This is the only statement which inserts into this table at all. So does this mean that the above statement is not atomic?

The problem is that this is almost impossible to recreate at will.

Perhaps I could change it to the something like the following:

INSERT INTO TheTable
WITH
    (HOLDLOCK,
    UPDLOCK,
    ROWLOCK)
SELECT
    @primaryKey,
    @value1,
    @value2
WHERE
    NOT EXISTS
    (SELECT
        NULL
    FROM
        TheTable
    WITH
        (HOLDLOCK,
        UPDLOCK,
        ROWLOCK)
    WHERE
        PrimaryKey = @primaryKey)

Although, maybe I'm using the wrong locks or using too much locking or something.

I have seen other questions on stackoverflow.com where answers are suggesting a "IF (SELECT COUNT(*) ... INSERT" etc., but I was always under the (perhaps incorrect) assumption that a single SQL statement would be atomic.

Does anyone have any ideas?

Thanks.

Adam

Answer 1

I don't know if this is the "official" way, but you could try the INSERT, and fall back to UPDATE if it fails.

Answer 2

I would go with the solution you proposed

Answer 3

I added HOLDLOCK which wasn't present originally. Please disregard the version without this hint.

As far as I'm concerned, this should be enough:

INSERT INTO TheTable 
SELECT 
    @primaryKey, 
    @value1, 
    @value2 
WHERE 
    NOT EXISTS 
    (SELECT 0
     FROM TheTable WITH (UPDLOCK, HOLDLOCK)
     WHERE PrimaryKey = @primaryKey) 

Also, if you actually want to update a row if it exists and insert if it doesn't, you might find this question useful.

Answer 4

I've done a similiar operation in past using a different method. First, I declare a variaable to hold the primary key. Then I populate that variable with the output of a select statement which look sfor a record with those values. Then I do and IF statement. If primary key is null, then do insert, else, return some error code.

     DECLARE @existing varchar(10)
    SET @existing = (SELECT primaryKey FROM TABLE WHERE param1field = @param1 AND param2field = @param2)

    IF @existing is not null
    BEGIN
    INSERT INTO Table(param1Field, param2Field) VALUES(param1, param2)
    END
    ELSE
    Return 0
END

Answer 5

What about the "JFDI" pattern?

BEGIN TRY
   INSERT etc
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
END CATCH

Seriously, this is quickest and the most concurrent without locks, especially at high volumes. What if the UPDLOCK is escalated and the whole table is locked?

Read lesson 4

Answer 6

You could use MERGE:

MERGE INTO Target
USING (VALUES (@primaryKey, @value1, @value2)) Source (key, value1, value2)
ON Target.key = Source.key
WHEN MATCHED THEN
    UPDATE SET value1 = Source.value1, value2 = Source.value2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Name, ReasonType) VALUES (@primaryKey, @value1, @value2)

Answer 7

The simplest solution, in my opinion, is to simply use the statement

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

before executing your statement. This requires no locking hints to achieve your desired output, which are a bit of a "code smell", and can become tough to manage if queries get sufficiently complex.

You were close when you asked if the operation is atomic. It is, but your problem is that it's not sufficiently isolated from other statements that run.

You might ask yourself why this occurs. The answer is scalability. Operations that aren't as isolated don't require the level of locks as those that are more isolated. By being "looser" with the locks, you cut the lock contention on the system and the system becomes more scalable.

When you increase your transaction isolation level to REPEATABLE READ, the database server automatically puts the proper locks on objects to ensure that no other transaction can change what your transaction reads while it's reading it. By putting the UPDLOCK hint on the table, you have essentially manually increased the isolation level of your transaction.

I hope this clears up a bit of conceptual stuff. Oh, and one more thing. If you're using connection pooling, make sure to set the transaction isolation level back to READ COMMITTED (which is usually the default) at the end of your statement, or future statements executed against that pooled connection may also run in the higher isolation level, which could cause your system to scale poorly.

(also see gbn's answer, which has the benefit of using less locks, but for your specific situation should be adequate)