I am using SQL Server 2008, and would like to be able to take advantage of something like mySQL's ON DUPLICATE KEY UPDATE
clause for INSERT
statements
Current legacy code does a delete and subsequent insert that is running into concurrency issues with duplicate key inserts from separate threads:
Here is the error I see in my production environment:
Violation of PRIMARY KEY constraint 'PK_Audience'. Cannot insert duplicate key in object 'dbo.Audience'.
(sp_ContentUpdate)
Primary Key:
AudienceId, VersionId
Offending SQL:
DELETE FROM dbo.Audience
WHERE VersionId = @VersionId
IF @AudienceXml IS NOT NULL
BEGIN
INSERT INTO dbo.Audience (
VersionId,
AudienceId,
CreatedDate,
CreatedByPersonId,
)
SELECT @VersionId,
AudienceId,
GETUTCDATE(),
@PersonId
FROM dbo.Audience
JOIN @AudienceXml.nodes('/Audiences/Audience') node(c)
ON Audience.AudienceName = c.value('@Name', 'nvarchar(50)')
END
Wrapping this TSQL in a transaction seems to either remove the concurrency issue or mask the issue by changing the timings. However, I do not think wrapping in a transaction has actually solved the concurrency.
Perhaps I am going about this wrong. Your suggestions are appreciated.
ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.
If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY , an UPDATE of the old row occurs.
The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.
So yes it is atomic in the sense that if the data that you are trying to insert will cause a duplicate in the primary key or in the unique index, the statement will instead perform an update and not error out.
Well, Bill beat us all, but here's a sample of what it might look like:
Merge dbo.Audience As target
Using (
Select @VersionId As VersionId, AudienceId, GetUtcDate() As CreatedDate, @PersonId As CreatedByPersonId
From dbo.Audience
Join @AudienceXml.nodes('/Audiences/Audience') node(c)
On Audience.AudienceName = c.value('@Name', 'nvarchar(50)')
)
When Matched Then
Update
Set VersoinId = target.VersionId, Audience = target.AudienceId
, CreatedDate = target.CreatedDate
, CreatedByPersionId = target.CreatedByPersonId
When Not Matched Then
Insert dbo.Audience(VersionId, AudienceId, CreatedDate, CreatedByPersonId)
You should read about how to use the MERGE
statement in Microsoft SQL Server 2008. This is actually the ANSI/ISO SQL way of handling this situation (MySQL's ON DUPLICATE KEY is a proprietary MySQLism).
See docs on the MERGE
statement at MSDN.
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