{"id":1647,"date":"2015-12-01T16:30:00","date_gmt":"2015-12-01T14:30:00","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=1647"},"modified":"2015-12-17T11:29:18","modified_gmt":"2015-12-17T09:29:18","slug":"why-would-you-want-to-name-a-transaction","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2015\/12\/01\/why-would-you-want-to-name-a-transaction\/","title":{"rendered":"Why would you want to name a transaction?"},"content":{"rendered":"

Something that i keep seeing in documentation, in forum code and in real systems is transactions that have names<\/p>\n

BEGIN TRANSACTION VeryImportantProcess\r\n\r\n<do stuff>\r\n\r\nCOMMIT VeryImportantProcess<\/pre>\n
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?<\/p>\n
In the first post in this series I had a look at nested transactions and in that post we saw that nested transactions don\u2019t have any effect on how the transactions are treated. The BEGIN TRANSACTION statement isn\u2019t even logged if there\u2019s already an open transaction in that session and a ROLLBACK still rolls everything back (unless it\u2019s specifically rolling back to a savepoint)<\/p>\n
First question then regarding naming of transactions is whether it has any effect at all on that behaviour.<\/p>\n
CREATE TABLE TestTrans (\r\nID int,\r\nInsertDate DATETIME\r\n);\r\n\r\nBEGIN TRANSACTION OuterTran\r\n  INSERT INTO TestTrans (ID, InsertDate) VALUES (1,GETDATE())\r\n  BEGIN TRANSACTION InnerTran\r\n    INSERT INTO TestTrans (ID, InsertDate) VALUES (2,GETDATE())\r\n  COMMIT TRANSACTION InnerTran\r\nROLLBACK TRANSACTION OuterTran\r\n\r\nSELECT * FROM TestTrans<\/pre>\n
\"NoRecords\"<\/a><\/p>\n
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.<\/p>\n
Ok, so maybe there\u2019s some internal usage for a transaction\u2019s name. Let\u2019s have a look at the transaction log to see.<\/p>\n
CHECKPOINT;\r\n\r\nBEGIN TRANSACTION OuterTran\r\n  INSERT INTO TestTrans (ID, InsertDate) VALUES (1,GETDATE())\r\n  BEGIN TRANSACTION InnerTran\r\n    INSERT INTO TestTrans (ID, InsertDate) VALUES (2,GETDATE())\r\n  COMMIT TRANSACTION InnerTran\r\nROLLBACK TRANSACTION OuterTran\r\n\r\nSELECT * FROM fn_DBLog(NULL, NULL)<\/pre>\n
\"TranLogContents\"<\/a><\/p>\n
The outer transaction\u2019s name appears in the log, replacing what was \u2018user_transaction\u2019 previously, but other than that there\u2019s no differences. The inner transaction still doesn\u2019t appear anywhere in the log, neither does the inner commit, and the rollback doesn\u2019t reference the transaction name anywhere.<\/p>\n
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\u2019s about the only real use.<\/p>\n
With two exceptions.<\/p>\n
There\u2019s 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.<\/p>\n
BEGIN TRANSACTION VeryImportantProcess WITH MARK\r\n\r\n<do stuff>\r\n\r\nCOMMIT TRANSACTION VeryImportantProcess<\/pre>\n
Now if we run an example of that and look into the log, there\u2019s something different there. Interestingly, the difference isn\u2019t on the BEGIN_XACT, it\u2019s on the COMMIT_XACT log record.<\/p>\n
\"2015_10_05_15_55_36_SQLQuery1.sql_EXCALIBUR.TransactionDemo_Excalibur_Gail_54_Microsoft_SQL_\"<\/a><\/p>\n
The question still stands though, why do this? Why name a transaction and put a mark of that into the transaction log. What\u00a0 benefit does it give?<\/p>\n
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)<\/p>\n