CODEWITHSUNDEEP
sqlsql-servert-sqltransactionssql-server-2016
James Wierzba
James Wierzba

Reputation: 17548

Use T-SQL Transaction for batch of delete statements?

I have a stored procedure that deletes records from multiple tables. I wish for either all of the delete statements to complete successfully, or none. The actual purpose here is to wipe all data related to a particular user.

Note that none of this data is related in any way to any other data. E.g. a user's data is not referenced in any way by another users data. However it is possible to have concurrent client sources accessing one user's data simultaneously. I don't know if this is relevant

So I've wrapped it in BEGIN TRANSACTION ... COMMIT TRANSACTION

like so:

CREATE PROCEDURE [dbo].[spDeleteData]
    @MyID AS INT
AS
    BEGIN TRANSACTION
        DELETE FROM [Table1] WHERE myId = @MyID;
        DELETE FROM [Table2] WHERE myId = @MyID;
        ....
    COMMIT TRANSACTION
RETURN 0

My question here is what are the implications of wrapping multiple DELETE calls in a transaction? Will it create possible deadlock scenarios, or hurt performance in some way?

From what I am reading, using TRANSACTION ISOLATION LEVEL only applies to read operations, is this true?

Upvotes: 1

Views: 3906

Answers (1)

benjamin moskovits
benjamin moskovits

Reputation: 5458

What you are guaranteeing is that either all the rows that match the conditions in both tables are successfully deleted or none of the rows are deleted (i.e. if there is a problem the deletes are rolled back.) There are more locks and they are kept for a longer period but if it fails you don't have to manually recreate the rows the deletes are undone for you automatically. You probably want to add the statement:

 set xact_abort on

at the beginning of the transaction and to wrap the whole thing in a begin try/begin catch statement.

Please see sommarskog.se/error-handling-I.html#XACT_ABORT for an execellent discussion on this statement and on error handling for TSQL.

Upvotes: 1

Related Questions