CODEWITHSUNDEEP
sqlsql-server
Rommel20
Rommel20

Reputation: 119

Delete and Insert Inside one Transaction SQL

I just want to ask if it is always the first query will be executed when encapsulate to a transaction? for example i got 500 k records to be deleted and 500 k to be inserted, is there a possibility of locking? Actually I already test this query and it works fine but i want to make sure if my assumption is correct.

Note: this will Delete and Insert the same record with possible update on other columns.

BEGIN TRAN;
    DELETE FROM OUTPUT TABLE WHERE ID = (1,2,3,4 etc)
    INSERT INTO OUTPUT TABLE Values (1,2,3,4 etc) 
COMMIT TRAN;

Upvotes: 8

Views: 31278

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294387

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.

Upvotes: 8

TT.
TT.

Reputation: 16146

From the documentation on TRANSACTION (emphasis mine):

BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency. Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.

BEGIN TRANSACTION starts a local transaction for the connection issuing the statement. Depending on the current transaction isolation level settings, many resources acquired to support the Transact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. Transactions left outstanding for long periods of time can prevent other users from accessing these locked resources, and also can prevent log truncation.

Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement. An application can perform actions such as acquiring locks to protect the transaction isolation level of SELECT statements, but nothing is recorded in the log until the application performs a modification action.

Upvotes: 1

Related Questions