title: Use T-SQL Transaction for batch of delete statements?
body: <p>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.</p>
  
  <p>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</p>
  
  <p>So I've wrapped it in <code>BEGIN TRANSACTION ... COMMIT TRANSACTION</code></p>
  
  <p>like so:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>My question here is <strong>what are the implications of wrapping multiple DELETE calls in a transaction?</strong> Will it create possible deadlock scenarios, or hurt performance in some way?</p>
  
  <p>From what I am reading, using <code>TRANSACTION ISOLATION LEVEL</code> only applies to read operations, is this true?</p>