提问



假设MyTable(KEY, datafield1, datafield2...)的表结构。


通常我想要更新现有记录,或者如果它不存在则插入新记录。


主要有:


IF (key exists)
  run update command
ELSE
  run insert command


写这个的最佳表现方式是什么?

最佳参考


不要忘记交易。性能好,但简单(如果存在......)方法非常危险
当多个线程尝试执行插入或更新时,您可以轻松完成
获得主键违规。


解决方案由@Beau Crawford& @Esteban显示一般的想法,但容易出错。


为了避免死锁和PK违规,您可以使用以下内容:


begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran


要么


begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

其它参考1


请参阅我之前非常类似问题的详细答案


@Beau Crawford在SQL 2005及以下版本中是一个很好的方法,但是如果你给予代表它应该去找第一个人来做它。唯一的问题是,对于插入,它仍然是两个IO操作。


MS Sql2008从SQL:2003标准中引入merge:


merge tablename with(HOLDLOCK) as target
using (values ('new value', 'different value'))
    as source (field1, field2)
    on target.idfield = 7
when matched then
    update
    set field1 = source.field1,
        field2 = source.field2,
        ...
when not matched then
    insert ( idfield, field1, field2, ... )
    values ( 7,  source.field1, source.field2, ... )


现在它真的只是一个IO操作,但可怕的代码:-(

其它参考2


做一个UPSERT:


UPDATE MyTable SET FieldA=@FieldA WHERE Key=@Key

IF @@ROWCOUNT = 0
   INSERT INTO MyTable (FieldA) VALUES (@FieldA)


http://en.wikipedia.org/wiki/Upsert[27]

其它参考3


很多人会建议你使用MERGE,但我提醒你不要这样做。默认情况下,它不会像多个语句那样保护您免受并发和竞争条件的影响,但它确实会引入其他危险:


http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/[28]


即使使用这种更简单的语法,我仍然更喜欢这种方法(为简洁省略了错误处理):


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE dbo.table SET ... WHERE PK = @PK;
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(PK, ...) SELECT @PK, ...;
END
COMMIT TRANSACTION;


很多人会这样建议:


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
IF EXISTS (SELECT 1 FROM dbo.table WHERE PK = @PK)
BEGIN
  UPDATE ...
END
ELSE
  INSERT ...
END
COMMIT TRANSACTION;


但所有这一切都可以确保您可能需要两次读取表以找到要更新的行。在第一个示例中,您只需要找到一次行。 (在这两种情况下,如果初始读取中未找到任何行,则会发生插入。)


其他人会建议这样:


BEGIN TRY
  INSERT ...
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 2627
    UPDATE ...
END CATCH


但是,如果除了在几乎每个插入失败的罕见情况下,除了让SQL Server捕获您可能首先阻止的异常之外没有其他原因,这是有问题的。我在这里证明了这一点:



  • http://www.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/

  • http://www.sqlperformance.com/2012/08/t-sql-queries/error-handling


其它参考4


IF EXISTS (SELECT * FROM [Table] WHERE ID = rowID)
UPDATE [Table] SET propertyOne = propOne, property2 . . .
ELSE
INSERT INTO [Table] (propOne, propTwo . . .)


     [29] [30]


唉,即使对我自己有害,我必须承认,没有选择的解决方案似乎更好,因为他们只需少一步即可完成任务。

其它参考5


如果要一次UPSERT多个记录,可以使用ANSI SQL:2003 DML语句MERGE。


MERGE INTO table_name WITH (HOLDLOCK) USING table_name ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])


在SQL Server 2005中查看模仿MERGE语句。[31]

其它参考6


虽然对此发表评论的时间已经很晚了但我想用MERGE添加一个更完整的例子。


此类Insert + Update语句通常称为Upsert语句,可以使用SQL Server中的MERGE实现。


这里给出了一个很好的例子:
http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx[32]


以上解释了锁定和并发方案。


我将引用相同的参考:


ALTER PROCEDURE dbo.Merge_Foo2
      @ID int
AS

SET NOCOUNT, XACT_ABORT ON;

MERGE dbo.Foo2 WITH (HOLDLOCK) AS f
USING (SELECT @ID AS ID) AS new_foo
      ON f.ID = new_foo.ID
WHEN MATCHED THEN
    UPDATE
            SET f.UpdateSpid = @@SPID,
            UpdateTime = SYSDATETIME()
WHEN NOT MATCHED THEN
    INSERT
      (
            ID,
            InsertSpid,
            InsertTime
      )
    VALUES
      (
            new_foo.ID,
            @@SPID,
            SYSDATETIME()
      );

RETURN @@ERROR;

其它参考7


/*
CREATE TABLE ApplicationsDesSocietes (
   id                   INT IDENTITY(0,1)    NOT NULL,
   applicationId        INT                  NOT NULL,
   societeId            INT                  NOT NULL,
   suppression          BIT                  NULL,
   CONSTRAINT PK_APPLICATIONSDESSOCIETES PRIMARY KEY (id)
)
GO
--*/

DECLARE @applicationId INT = 81, @societeId INT = 43, @suppression BIT = 0

MERGE dbo.ApplicationsDesSocietes WITH (HOLDLOCK) AS target
--set the SOURCE table one row
USING (VALUES (@applicationId, @societeId, @suppression))
    AS source (applicationId, societeId, suppression)
    --here goes the ON join condition
    ON target.applicationId = source.applicationId and target.societeId = source.societeId
WHEN MATCHED THEN
    UPDATE
    --place your list of SET here
    SET target.suppression = source.suppression
WHEN NOT MATCHED THEN
    --insert a new line with the SOURCE table one row
    INSERT (applicationId, societeId, suppression)
    VALUES (source.applicationId, source.societeId, source.suppression);
GO


根据您的需要替换表和字段名称。
使用ON 条件处理。
然后在DECLARE行上为变量设置适当的值(和类型)。


干杯。

其它参考8


您可以使用MERGE语句,此语句用于插入数据(如果不存在)或更新(如果存在)。


MERGE INTO Employee AS e
using EmployeeUpdate AS eu
ON e.EmployeeID = eu.EmployeeID`

其它参考9


在SQL Server 2008中,您可以使用MERGE语句

其它参考10


如果更新UPDATE if-no-rows-then INSERT route,请考虑首先执行INSERT以防止竞争条件(假设没有干预DELETE)


INSERT INTO MyTable (Key, FieldA)
   SELECT @Key, @FieldA
   WHERE NOT EXISTS
   (
       SELECT *
       FROM  MyTable
       WHERE Key = @Key
   )
IF @@ROWCOUNT = 0
BEGIN
   UPDATE MyTable
   SET FieldA=@FieldA
   WHERE Key=@Key
   IF @@ROWCOUNT = 0
   ... record was deleted, consider looping to re-run the INSERT, or RAISERROR ...
END


除了避免竞争条件,如果在大多数情况下记录已经存在,那么这将导致INSERT失败,浪费CPU。


使用MERGE可能更适合SQL2008以上版本。

其它参考11


MS SQL Server 2008引入了MERGE语句,我认为它是SQL:2003标准的一部分。正如许多人所说,处理一行案例并不是什么大问题,但在处理大型数据集时,需要一个游标,并且会出现所有性能问题。在处理大型数据集时,MERGE语句将非常受欢迎。

其它参考12


这取决于使用模式。人们必须看一下使用大图,而不会迷失在细节中。例如,如果在创建记录后使用模式为99%更新,则UPSERT是最佳解决方案。


在第一次插入(命中)之后,它将是所有单个语句更新,没有ifs或buts。插入上的where条件是必要的,否则它将插入重复项,并且您不想处理锁定。


UPDATE <tableName> SET <field>=@field WHERE key=@key;

IF @@ROWCOUNT = 0
BEGIN
   INSERT INTO <tableName> (field)
   SELECT @field
   WHERE NOT EXISTS (select * from tableName where key = @key);
END

其它参考13


之前每个人都因为害怕这些贪婪的用户直接运行你的sprocs而跳到HOLDLOCK-s :-)让我指出你必须保证新PK-s的独特性(身份键,序列Oracle中的生成器,外部ID的唯一索引,索引覆盖的查询)。这是问题的阿尔法和欧米茄。如果你没有这个,那么宇宙的HOLDLOCK-s就不会拯救你,如果你有,那么你在第一次选择时就不需要UPDLOCK以外的任何东西了。 (或首先使用更新)。


Sprocs通常在非常受控的条件下运行,并假设有可信赖的呼叫者(中间层)。这意味着如果一个简单的upsert模式(更新+插入或合并)曾经看到重复的PK,这意味着你的中间层或表设计中存在错误,那么SQL会在这种情况下大吼大叫并拒绝记录,这很好。在这种情况下,HOLDLOCK等于吃异常并接收可能有缺陷的数据,除了减少你的性能。


话虽如此,使用MERGE或UPDATE然后INSERT在您的服务器上更容易,并且更不容易出错,因为您不必记住添加(UPDLOCK)到第一次选择。此外,如果您正在小批量插入/更新需要知道你的数据,以便决定交易是否合适。它只是一组不相关的记录,然后额外的包络交易将是有害的。

其它参考14


如果您首先尝试更新然后插入,竞争条件真的重要吗?
假设您有两个线程想要为键设置一个值:


线程1:值= 1

线程2:值= 2


示例竞争条件场景



  1. 未定义

  2. 线程1因更新而失败

  3. 线程2因更新而失败

  4. 线程1或线程2中的一个成功插入。例如。线程1

  5. 另一个线程因插入失败(错误重复键) - 线程2。



    • 结果:要插入的两个胎面的第一个,决定值。

    • 通缉结果:写入数据(更新或插入)的2个线程中的最后一个应该决定值




但;在多线程环境中,OS调度程序决定线程执行的顺序 - 在上面的场景中,我们有这种竞争条件,它是决定执行顺序的操作系统。即:从系统角度来看,线程1或线程2是第一是错误的。


当线程1和线程2的执行时间非常接近时,竞争条件的结果并不重要。唯一的要求应该是其中一个线程应该定义结果值。


对于实现:如果更新后插入导致错误重复键,则应视为成功。


此外,当然不应该假设数据库中的值与您上次写入的值相同。

其它参考15


我曾尝试过以下解决方案,当我发生插入语句的并发请求时,它对我有用。


begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert table (key, ...)
   values (@key, ...)
end
commit tran

其它参考16


您可以使用此查询。适用于所有SQL Server版本。这很简单,也很清楚。但是你需要使用2个查询。如果你不能使用MERGE,你可以使用它


    BEGIN TRAN

    UPDATE table
    SET Id = @ID, Description = @Description
    WHERE Id = @Id

    INSERT INTO table(Id, Description)
    SELECT @Id, @Description
    WHERE NOT EXISTS (SELECT NULL FROM table WHERE Id = @Id)

    COMMIT TRAN


注意:请解释答案否定

其它参考17


如果您使用ADO.NET,DataAdapter将处理此问题。


如果你想自己处理它,这就是这样的:


确保密钥列上存在主键约束。


然后你:



  1. 执行更新

  2. 如果更新失败,因为已存在包含密钥的记录,请执行插入操作。如果更新没有失败,则表示您已完成。



您也可以反过来执行此操作,即首先执行插入操作,如果插入失败则执行更新。通常第一种方式更好,因为更新比插入更频繁。

其它参考18


执行if存在... else ...涉及最少两个请求(一个检查,一个采取行动)。以下方法仅需要记录存在的一个,如果需要插入,则需要两个:


DECLARE @RowExists bit
SET @RowExists = 0
UPDATE MyTable SET DataField1 = 'xxx', @RowExists = 1 WHERE Key = 123
IF @RowExists = 0
  INSERT INTO MyTable (Key, DataField1) VALUES (123, 'xxx')

其它参考19


我通常会做其他几张海报所说的关于首先检查它然后做正确路径的事情。在执行此操作时您应该记住的一件事是,sql缓存的执行计划对于一个路径或另一个路径可能不是最佳的。我认为最好的方法是调用两个不同的存储过程。


FirstSP:
If Exists
   Call SecondSP (UpdateProc)
Else
   Call ThirdSP (InsertProc)


现在,我不经常听从自己的建议,所以请耐心等待。

其它参考20


做一个选择,如果你得到一个结果,更新它,如果没有,创建它。