我正在使用sql Server 2008,并希望能够利用像
MySQL的ON DUPLICATE KEY UPDATE子句之类的INSERT语句
当前的遗留代码执行一个删除和后续插入,它与单独线程中的重复键插入运行并发问题：
这是我在生产环境中看到的错误：
- Violation of PRIMARY KEY constraint 'PK_Audience'. Cannot insert duplicate key in object 'dbo.Audience'.
(sp_ContentUpdate)
首要的关键：
- AudienceId,VersionId
违规sql：
- DELETE FROM dbo.Audience
- WHERE VersionId = @VersionId
- IF @AudienceXml IS NOT NULL
- BEGIN
- INSERT INTO dbo.Audience (
- VersionId,AudienceId,CreatedDate,CreatedByPersonId,)
- SELECT @VersionId,GETUTCDATE(),@PersonId
- FROM dbo.Audience
- JOIN @AudienceXml.nodes('/Audiences/Audience') node(c)
- ON Audience.AudienceName = c.value('@Name','nvarchar(50)')
- END
将此Tsql包含在事务中似乎可以通过更改时序来删除并发问题或掩盖问题.但是,我不认为在事务中包装实际上已经解决了并发问题.
也许我这个错了.您的建议不胜感激
解决方法
那么,比尔打败了我们,但这里是一个可能的样子：
- Merge dbo.Audience As target
- Using (
- Select @VersionId As VersionId,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,CreatedByPersonId)
