How to Efficiently Perform INSERT or UPDATE Operations in SQL Server While Preventing Deadlocks?

Optimizing INSERT and UPDATE Operations in SQL Server to Avoid Deadlocks

Managing concurrent INSERT and UPDATE operations on tables like MyTable (KEY, datafield1, datafield2...) often requires a "live query" approach – updating existing rows or inserting new ones if they don't exist. Performance and data integrity are paramount concerns.

A naive "if exists" method is inefficient and prone to primary key violations in multi-threaded environments. To guarantee data consistency and prevent deadlocks, a transaction-based strategy is superior:

Method 1:

<code class="language-sql">BEGIN TRANSACTION
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 TRANSACTION</code>

Method 2:

<code class="language-sql">BEGIN TRANSACTION
      UPDATE table WITH (SERIALIZABLE) SET ...
      WHERE key = @key

      IF @@ROWCOUNT = 0
      BEGIN
            INSERT INTO table (key, ...) VALUES (@key,...)
      END

COMMIT TRANSACTION</code>

Both methods utilize SERIALIZABLE isolation level to eliminate deadlocks and maintain data integrity. This ensures optimal performance, especially when multiple threads simultaneously attempt INSERT or UPDATE operations. The choice between the two methods may depend on specific performance characteristics and coding style preferences.

The above is the detailed content of How to Efficiently Perform INSERT or UPDATE Operations in SQL Server While Preventing Deadlocks?. For more information, please follow other related articles on the PHP Chinese website!