Is Neccessary to Encapsulate a Single Merge Statement (With Insert, Delete and Update) in a Transaction

Is neccessary to encapsulate a single merge statement (with insert, delete and update) in a transaction?

Any statement in SQL Server is a transaction in it's own right.

That is, it is atomic: everything succeeds or everything fails

An explicit transaction would be used to group multiple single atomic statements into one big atomic transaction.

This is beauty of MERGE: no need for an explicit transaction and 3 separate statements.

Delete and Insert Inside one Transaction SQL

Within a transaction all write locks (all locks acquired for modifications) must obey the strict two phase locking rule. One of the consequences is that a write (X) lock acquired in a transaction cannot be released until the transaction commits. So yes, the DELETE and INSERT will execute sequentially and all locks acquired during the DELETE will be retained while executing the INSERT.

Keep in mind that deleting 500k rows in a transaction will escalate the locks to one table lock, see Lock Escalation.

Deleting 500k rows and inserting 500k rows in a single transaction, while maybe correct, is a bad idea. You should avoid such large units of works, long transaction, if possible. Long transactions pin the log in place, create blocking and contention, increase recovery and DB startup time, increase SQL Server resource consumption (locks require memory).

You should consider doing the operation in small batches (perhaps 10000 rows at time), use MERGE instead of DELETE/INSERT (if possible) and, last but not least, consider a partitioned sliding window
implementation, see How to Implement an Automatic Sliding Window in a Partitioned Table.

Why is SQL Server upsert with MERGE inserting multiple records?

MERGE, results in multiple SQL statements, that means it can result in possible concurrency conflicts. You should implement locking:

MERGE dbo.TableName WITH (HOLDLOCK) AS target
USING ... AS source ...;

https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

When to use Transactions in SQL Server

You use transactions when the set of database operations you are making needs to be atomic.

That is - they all need to succeed or fail. Nothing in between.

Transactions are to be used to ensure that the database is always in a consistent state.

In general, unless there is a good reason not to use them (long running process for instance), use them. See this blog post for details.


Try/Catch blocks have nothing to do with transactions - they are used for exception handling. The two concepts are not related and are not replacements for each other.

Are Transactions Always Atomic?

The 2 points are unrelated

Sequential

If you insert values 1 to 1000, it will be sequential with an WHERE and ORDER BY to limit you to these 1000 rows in some column. Unless there are duplicates, so you'd need a unique constraint

If you rely on an IDENTITY, it isn't guaranteed: Do Inserted Records Always Receive Contiguous Identity Values.

Atomicity

All transactions are atomic:

  • Is neccessary to encapsulate a single merge statement (with insert, delete and update) in a transaction?
  • SQL Server and connection loss in the middle of a transaction
  • Does it delete partially if execute a delete statement without transaction?

Are Transactions Always Atomic?

The 2 points are unrelated

Sequential

If you insert values 1 to 1000, it will be sequential with an WHERE and ORDER BY to limit you to these 1000 rows in some column. Unless there are duplicates, so you'd need a unique constraint

If you rely on an IDENTITY, it isn't guaranteed: Do Inserted Records Always Receive Contiguous Identity Values.

Atomicity

All transactions are atomic:

  • Is neccessary to encapsulate a single merge statement (with insert, delete and update) in a transaction?
  • SQL Server and connection loss in the middle of a transaction
  • Does it delete partially if execute a delete statement without transaction?


Related Topics



Leave a reply



Submit