Why would you want to name a transaction?

Something that i keep seeing in documentation, in forum code and in real systems is transactions that have names

BEGIN TRANSACTION VeryImportantProcess

<do stuff>

COMMIT VeryImportantProcess

Now the question came up on the forums a while back as to what the point of this is. Why would you name a transaction, what effect does it have on behaviour and how is it treated internally?

In the first post in this series I had a look at nested transactions and in that post we saw that nested transactions don’t have any effect on how the transactions are treated. The BEGIN TRANSACTION statement isn’t even logged if there’s already an open transaction in that session and a ROLLBACK still rolls everything back (unless it’s specifically rolling back to a savepoint)

First question then regarding naming of transactions is whether it has any effect at all on that behaviour.

CREATE TABLE TestTrans (
ID int,
InsertDate DATETIME
);

BEGIN TRANSACTION OuterTran
  INSERT INTO TestTrans (ID, InsertDate) VALUES (1,GETDATE())
  BEGIN TRANSACTION InnerTran
    INSERT INTO TestTrans (ID, InsertDate) VALUES (2,GETDATE())
  COMMIT TRANSACTION InnerTran
ROLLBACK TRANSACTION OuterTran

SELECT * FROM TestTrans

NoRecords

Clearly none. Even with all the transactions named, a commit of a named transaction did not commit anything until the transaction nest level reached 0. A rollback still rolls back everything.

Ok, so maybe there’s some internal usage for a transaction’s name. Let’s have a look at the transaction log to see.

CHECKPOINT;

BEGIN TRANSACTION OuterTran
  INSERT INTO TestTrans (ID, InsertDate) VALUES (1,GETDATE())
  BEGIN TRANSACTION InnerTran
    INSERT INTO TestTrans (ID, InsertDate) VALUES (2,GETDATE())
  COMMIT TRANSACTION InnerTran
ROLLBACK TRANSACTION OuterTran

SELECT * FROM fn_DBLog(NULL, NULL)

TranLogContents

The outer transaction’s name appears in the log, replacing what was ‘user_transaction’ previously, but other than that there’s no differences. The inner transaction still doesn’t appear anywhere in the log, neither does the inner commit, and the rollback doesn’t reference the transaction name anywhere.

So what conclusion can we come to here? Pretty much that naming of transactions has one real use, as a form of documentation. Instead of putting a comment above an BEGIN TRANSACTION we can give the transaction a name that indicates what the transaction does, That’s about the only real use.

With two exceptions.

There’s a seldom used option on a BEGIN TRANACTION, an optional WITH clause, Using that along with the transaction name can have some uses in exceptional circumstances.

BEGIN TRANSACTION VeryImportantProcess WITH MARK

<do stuff>

COMMIT TRANSACTION VeryImportantProcess

Now if we run an example of that and look into the log, there’s something different there. Interestingly, the difference isn’t on the BEGIN_XACT, it’s on the COMMIT_XACT log record.

2015_10_05_15_55_36_SQLQuery1.sql_EXCALIBUR.TransactionDemo_Excalibur_Gail_54_Microsoft_SQL_

The question still stands though, why do this? Why name a transaction and put a mark of that into the transaction log. What  benefit does it give?

The mark can be used with a rather poorly known and seldom used pair of options on the restore command (when restoring transaction log backups)

  • WITH STOPAFTERMARK
  • WITH STOPBEFOREMARK

These two, similar to the better known STOPAT can be used to stop a transaction log restore part way through a log backup. Unlike STOPAT, they don’t take a time, but rather take a string, a name that was used for a marked transaction name.

Now to be honest there’s not all that much use for this. If a user drops a table or otherwise messes data up requiring a restore to the point before that happened they’re not going to be nice and run that accidental data mess up in a named, marked transaction.

Where it can be useful is when the transaction crossed multiple databases and/or multiple servers. The log mark appears in all of them and can therefore be used to restore multiple databases to a consistent state where that consistent state is not a single point in time.

Still, it’s useful to have in the toolbox just in case some circumstance comes up where it’s needed.

Edit: It was pointed out, both on twitter and in the comments, that adding a name to a transaction reflects in the transaction-related DMVs and potentially makes debugging easier. Absolutely, it can do and it’s a valid reason to name transactions, I’m lumping that in with ‘documentation’ in the discussions here.

There’s another case where naming transactions can affect behaviour and in this case usually in a bad way. I’ll look at in in more detail in part 4 of this series, but for now will just reference the relevant segment in Books Online:

transaction_name Is the name assigned to the transaction on BEGIN TRANSACTION. transaction_name must conform to the rules for identifiers, but only the first 32 characters of the transaction name are used. When nesting transactions, transaction_name must be the name from the outermost BEGIN TRANSACTION statement. transaction_name is always case sensitive, even when the instance of SQL Server is not case sensitive.

The full list is:

  1. A Mess of Nested Transactions
  2. Savepoints and conditional transactions
  3. Why would you want to name a transaction? (This post)
  4. When naming transactions causes an error

9 Comments

  1. Bennett Scharf

    Thank you! That was a very informative post.

    Reply
  2. Dony van Vliet

    Naming a long running transaction for some batch process might be useful, because the (probably well-known) name of this transaction appears in the internal process list of SQL Server. From this name it may be immediately clear which process has become stuck somewhere in the middle, possibly also being the source of some lock contention or other problems. The habbit of naming your transactions will some day be a life saver for you as the DBA of a production environment …

    Reply
  3. Jacob

    Maybe naming the transactions could be useful when it comes to debugging deadlocks?

    Reply
  4. Ray

    Hi Gail,
    Thanks for this series. It has pushed me into a new space.
    I am a bit confused about “save point” and the interaction of “nested transactions”. For example, the BOL article “SAVE TRANSACTION (Transact-SQL)” raises more questions for me than it answers.
    Another topic I find confusing is the interaction of transactions and triggers, BOL Topic “Rollbacks and Commits in Stored Procedures and Triggers ”
    I hope you will address these in more detail in a future post.
    Thanks,

    Reply
    1. Gail (Post author)

      Have you looked at the earlier parts of this series? They cover nested transactions and savepoints.

      Reply
  5. Brian J. Parker

    This “documentation” is really useful when you are a DBA dealing with a complex software product, trying to troubleshoot a long-running transaction. If that transaction has a name, then seeing “hmmm this transaction has been running for hours” is a little more useful if that transaction gives you a hint where it came from!

    Also, users aside, I don’t completely trust MYSELF! If I’m doing a large operation on the database that might break things, I put it inside a named transaction so it’s easier to roll back to before any stupid mistake I might make. I haven’t had to do so… YET. But I’m fallible!

    Thanks for sharing this. If you are writing code that will be used by others, name your transactions something useful. For their sake.

    Reply
    1. Gail (Post author)

      Absolutely. And if that’s why you’re naming transactions, to make deadlock identification easier or to be able to identify code from the transaction DMVs or otherwise troubleshoot SQL, that’s fine.

      I wrote this because I’ve seen a few people recently who thought that naming transactions changed the commit/rollback behaviours.

      Reply
      1. Brian J. Parker

        Thanks for writing it! It’s something I tried and tested myself when I was trying to make nested behavior work. (Usually because I tried to encapsulate code a lot, and was calling stored procedures that used transactions from within transactions.) If only I’d read this earlier on to save me the trouble!

        I hope I didn’t digress too much; I just like to encourage developers to name transactions even if it doesn’t give you a nested commit/rollback.

        Reply
  6. Aner Bautista

    Thank you for bringing light to this matter!

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *