{"id":1727,"date":"2015-12-15T16:30:23","date_gmt":"2015-12-15T14:30:23","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=1727"},"modified":"2015-12-19T12:27:53","modified_gmt":"2015-12-19T10:27:53","slug":"when-naming-transactions-causes-an-error","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2015\/12\/15\/when-naming-transactions-causes-an-error\/","title":{"rendered":"When naming transactions causes an error"},"content":{"rendered":"

For the last part of the series on transactions, I\u2019m going to look at a problem that I ran across entirely by chance while doing some performance tuning. A procedure had a transaction in it, and it was named. Something like this:<\/p>\n

CREATE PROCEDURE RemoveUser (@UserID INT)\r\nAS\r\n\r\nBEGIN TRY\r\n  BEGIN TRANSACTION Important\r\n    DELETE FROM Users WHERE UserID = @UserID\r\n  COMMIT TRANSACTION Important\r\nEND TRY\r\nBEGIN CATCH\r\n  ROLLBACK TRANSACTION Important\r\nEND CATCH\r\nGO<\/pre>\n
The error\u2019s squelched, not handled, but let\u2019s ignore that, it\u2019s not the point of this post. The example as written doesn\u2019t need a transaction, but the real procedure was a tad more complex. The transaction is named, and the name is specified in the COMMIT and ROLLBACK as well.<\/p>\n
Before I go into the problem with this code, I\u2019m going to quote from three Books Online entries.<\/p>\n
BEGIN TRANSACTION<\/a><\/p>\n
transaction_name<\/em><\/p>\n
Is the name assigned to the transaction. transaction_name<\/em> must conform to the rules for identifiers, but identifiers longer than 32 characters are not allowed. Use transaction names only on the outermost pair of nested BEGIN…COMMIT or BEGIN…ROLLBACK statements. transaction_name<\/em> is always case sensitive, even when the instance of SQL Server is not case sensitive.<\/p><\/blockquote>\n
COMMIT TRANSACTION<\/a><\/span><\/p>\n
transaction_name<\/em><\/p>\n
Is ignored by the SQL Server Database Engine. transaction_name<\/em> specifies a transaction name assigned by a previous BEGIN TRANSACTION. transaction_name<\/em> must conform to the rules for identifiers, but cannot exceed 32 characters. transaction_name<\/em> can be used as a readability aid by indicating to programmers which nested BEGIN TRANSACTION the COMMIT TRANSACTION is associated with.<\/p><\/blockquote>\n
ROLLBACK TRANSACTION<\/a><\/p>\n
transaction_name<\/em><\/p>\n
Is the name assigned to the transaction on BEGIN TRANSACTION. transaction_name<\/em> must conform to the rules for identifiers, but only the first 32 characters of the transaction name are used. When nesting transactions, transaction_name<\/em> must be the name from the outermost BEGIN TRANSACTION statement. transaction_name<\/em> is always case sensitive, even when the instance of SQL Server is not case sensitive.<\/p><\/blockquote>\n
I do love how Books Online refers to nested transactions as though they were real things\u2026<\/p>\n
Now that we\u2019re perused the documentation, let\u2019s go back to the code. As-is it runs fine, however let\u2019s try that procedure called from within another transaction.<\/p>\n
BEGIN TRANSACTION\r\n  EXEC dbo.RemovePosts @UserID = 152;\r\n  EXEC dbo.RemoveThreads @UserID = 152;\r\n  EXEC dbo.RemoveUser @UserID = 152;\r\nCOMMIT<\/pre>\n
Some sort of \u2018remove all my details\u2019 functionality, except that I\u2019ve \u2018forgotten\u2019 to check one foreign key relationship and so the delete inside the inner transaction is going to throw an error. Execution is going to be transferred to the CATCH block and the ROLLBACK TRANSACTION is going to run.<\/p>\n
Msg 6401, Level 16, State 1, Procedure RemoveUser, Line 10
\nCannot roll back Important. No transaction or savepoint of that name was found.<\/span><\/p><\/blockquote>\n
Whoops.<\/p>\n
The documentation for ROLLBACK TRANSACTION states \u2018When nesting transactions, transaction_name<\/em> must be the name from the outermost BEGIN TRANSACTION statement.\u2019. In this case a second transaction has been wrapped around the first, the outer transaction had no name and hence an error occurred.<\/p>\n
No big deal though, right? It\u2019s just a different error being thrown, right? It\u2019s not as if we were handling and logging the error properly in the first place.<\/p>\n
Not quite.<\/p>\n
\"OpenTransaction\"<\/a><\/p>\n
The error was thrown by the ROLLBACK statement. As such, the transaction is still open, the locks are held and the transaction log space can\u2019t be reused. Unless the application that called this was checking for open transactions, that transaction could potentially be left open for quite some amount of time, causing blocking and\/or the transaction log to grow.<\/p>\n
It\u2019s not just that someone in the future might call the code from another stored proc within a transaction, it\u2019s also that it might be that the code is called from an application which started a transaction. Or called from SSIS which started a transaction. It’s very hard to ensure that code is never called from within an existing transaction<\/p>\n
In conclusion, if you want to put a name on a BEGIN TRANSACTION, for documentation purposes or to help with debugging, go ahead. If you want to put a name on a COMMIT, that\u2019s fine too, although it has no effect at all on how the code runs. Don\u2019t put names on the ROLLBACK ever, as it leaves the code with a potential hidden bug.<\/p>\n
This concludes the short series on transactions. The full list is:<\/p>\n
    \n
  1. A Mess of Nested Transactions<\/a><\/li>\n
  2. Savepoints and conditional transactions<\/a><\/li>\n
  3. Why would you want to name a transaction?<\/a><\/li>\n
  4. When naming transactions causes an error (this post)<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
    For the last part of the series on transactions, I\u2019m going to look at a problem that I ran across entirely by chance while doing some performance tuning. A procedure had a transaction in it, and it was named. Something… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"New blog post: When naming transactions causes an error","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[15,16,17],"tags":[],"class_list":["post-1727","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-syndication","category-t-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-rR","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1727","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=1727"}],"version-history":[{"count":8,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1727\/revisions"}],"predecessor-version":[{"id":1741,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1727\/revisions\/1741"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=1727"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=1727"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=1727"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}