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?
The alternative (and generally preferred) method for INSERTING into rows that may contain duplicate UNIQUE or PRIMARY KEY values is to use the INSERT ... ON DUPLICATE KEY UPDATE statement and clause.
Often you have the situation that you need to check if an table entry exists, before you can make an update. If it does not exist, you have to do an insert first. Unfortunately, this the 'ON DUPLICATE KEY' statement only works on PRIMARY KEY and UNIQUE columns.
First, we check if the record exists with the EXISTS keyword. EXISTS executes the query we tell it to (the SELECT ) and returns a boolean value. If it finds the record, we return 'This record already exists!' to our recordset and do nothing else.
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:
Lesson 4: When developing the upsert proc prior to tuning the indexes, I first trusted that the
If Exists(Select…)
line would fire for any item and would prohibit duplicates. Nada. In a short time there were thousands of duplicates because the same item would hit the upsert at the same millisecond and both transactions would see a not exists and perform the insert. After much testing the solution was to use the unique index, catch the error, and retry allowing the transaction to see the row and perform an update instead an insert.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With