Delete and Insert Inside one Transaction SQL

Question

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;

Answer 1

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.