title: How to avoid using duplicate savepoint names in nested transactions in nested stored procs?
body: <p>I have a pattern that I almost always follow, where if I need to wrap up an operation in a transaction, I do this:</p>
  
  <pre><code>BEGIN TRANSACTION
  SAVE TRANSACTION TX
  
  -- Stuff
  
  IF @error &lt;&gt; 0
      ROLLBACK TRANSACTION TX
  
  COMMIT TRANSACTION
  </code></pre>
  
  <p>That's served me well enough in the past, but after years of using this pattern (and copy-pasting the above code), I've suddenly discovered a flaw which comes as a complete shock.</p>
  
  <p>Quite often, I'll have a stored procedure calling other stored procedures, all of which use this same pattern. What I've discovered (to my cost) is that because I'm using the same savepoint name everywhere, I can get into a situation where my outer transaction is partially committed - precisely the opposite of the atomicity that I'm trying to achieve.</p>
  
  <p>I've put together an example that exhibits the problem. This is a single batch (no nested stored procs), and so it looks a little odd in that you probably wouldn't use the same savepoint name twice in the same batch, but my real-world scenario would be too confusing to post.</p>
  
  <pre><code>CREATE TABLE Test (test INTEGER NOT NULL)
  
  BEGIN TRAN 
  SAVE TRAN TX
  
      BEGIN TRAN
      SAVE TRAN TX
          INSERT INTO Test(test) VALUES (1)
      COMMIT TRAN TX
  
      BEGIN TRAN
      SAVE TRAN TX
          INSERT INTO Test(test) VALUES (2)
      COMMIT TRAN TX
  
      DELETE FROM Test
  
  ROLLBACK TRAN TX
  COMMIT TRAN TX
  
  SELECT * FROM Test
  
  DROP TABLE Test
  </code></pre>
  
  <p>When I execute this, it lists one record, with value "1". In other words, <em>even though I rolled back my outer transaction</em>, a record was added to the table.</p>
  
  <p>What's happening is that the <code>ROLLBACK TRANSACTION TX</code> at the outer level is rolling back as far as the last <code>SAVE TRANSACTION TX</code> at the inner level. Now that I write this all out, I can see the logic behind it: the server is looking back through the log file, treating it as a linear stream of transactions; it doesn't understand the nesting/hierarchy implied by either the nesting of the transactions (or, in my real-world scenario, by the calls to other stored procedures).</p>
  
  <p>So, clearly, I need to start using unique savepoint names instead of blindly using "TX" everywhere. But - and this is where I finally get to the point - is there a way to do this in a copy-pastable way so that I <em>can</em> still use the same code everywhere? Can I auto-generate the savepoint name on the fly somehow? Is there a convention or best-practice for doing this sort of thing?</p>
  
  <p>It's not exactly hard to come up with a unique name every time you start a transaction (could base it off the SP name, or somesuch), but I do worry that eventually there would be a conflict - and you wouldn't know about it because rather than causing an error it just silently destroys your data... :-(</p>