Explicit Transactions

  • BrainDonor

    SSCoach

    Points: 19155

    Comments posted to this topic are about the item Explicit Transactions

    Steve Hall
    Linkedin
    Blog Site

  • mauriciorpp

    Default port

    Points: 1472

    great article. it's nice to revisit some basic topics from time to time. I never used marked transactions before, so I liked that example.

    cya

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Great article. I learned a lot today. Thanks.

  • jan.hausenblas

    SSC Rookie

    Points: 35

    Please keep in mind, or disproove, that an explicit rollback also reverts any SQL module compilations done in the course of the transaction (in the same session).

  • Herman van Midden

    SSChasing Mays

    Points: 643

    Great article! I know now what I didn't understand until now. Thanks

  • TomThomson

    SSC Guru

    Points: 104707

    Excellent article.

    A couple of points where it could maybe be improved:-

    In the Identity Columns section, while the statement "any IDENTITY columns do not have the values reverted" is true it's pretty meaningless, since values in IDENTITY columns can't be changed, and potentially misleading as it may lead people to think that they can be changed, and in the rest of that sentence your reference to the bext available identity value should be to the next automatically generated identity value. If you were intending to suggest that the IDENT_CURRENT table property is part of the value of the column your statement makes sense of a sort but if so you should state it because people generally don't think that way.

    It would be better to include a SQL Variables section between Identity Columns and Table Variables, to say that SQL Variables are not affected by rollback, using something like

    DECLARE @TestValue int to produce an example. Then the Table variables section could say "As with other SQL variables, Table Variables are not reverted by a ROLLBACK" instead of referring to IDENTITY.

    Tom

  • petebin.gm

    SSC Rookie

    Points: 33

    Great article - thanks.

    Maybe an error in Listing 1 - should not the SP RestoreTestTable include the drop/create of the test table ?

  • Eirikur Eiriksson

    SSC Guru

    Points: 182338

    Thank you Steve for this article, very good job indeed.

    ๐Ÿ˜Ž

  • sTTu

    SSC Veteran

    Points: 201

    Interesting stuff and a few things I wasn't aware of.

    Would have liked to see errors and xact_abort covered also as this has caught me out in the past.

  • quackhandle1975

    SSChampion

    Points: 10963

    Nice article Steve on what can be a complex subject matter.

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. โ€“ Carl Jung.[/font]
  • Solomon Rutzky

    SSCoach

    Points: 15935

    sTTu - Monday, January 4, 2016 4:17 AM

    Interesting stuff and a few things I wasn't aware of.Would have liked to see errors and xact_abort covered also as this has caught me out in the past.

    Hi there. I posted an answer to DBA.StackExchange a few years ago on this topic and did include error handling, XACT_ABORT, XACT_STATE(), etc:

    How to rollback when 3 stored procedures are started from one stored procedure

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

  • Dennis Q Miller

    Old Hand

    Points: 366

    Great explanation of transactions--an important topic with intricacies that every developer should understand.  I do think the article pertains equally well to implicit transactions, though, and could use a paragraph or two explaining the benefits of managing transaction scope explicitly.

  • Solomon Rutzky

    SSCoach

    Points: 15935

    The name can be in excess of 32 characters, but only the first 32 characters are actually used.

    Hi Steve. Regarding the statement from the article quoted above (pertaining to transaction name length), please note that the silent truncation you are referring to only applies to using a variable for the transaction name. If you are using a literal / constant for the transaction name, then you will get an error if you attempt to use more than 32 characters.

    Please also note that transaction names are treated as having a binary Collation (not case-sensitive as the documentation currently states), regardless of the Instance-level or Database-level Collations.

    For details, please see the Transaction Names section of the following post: Whatโ€™s in a Name?: Inside the Wacky World of T-SQL Identifiers

    Take care,
    Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

  • Lynn Pettis

    SSC Guru

    Points: 442101

    Solomon Rutzky - Friday, July 13, 2018 12:23 PM

    The name can be in excess of 32 characters, but only the first 32 characters are actually used.

    Hi Steve. Regarding the statement from the article quoted above (pertaining to transaction name length), please note that the silent truncation you are referring to only applies to using a variable for the transaction name. If you are using a literal / constant for the transaction name, then you will get an error if you attempt to use more than 32 characters.

    Please also note that transaction names are treated as having a binary Collation (not case-sensitive as the documentation currently states), regardless of the Instance-level or Database-level Collations.

    For details, please see the Transaction Names section of the following post: What’s in a Name?: Inside the Wacky World of T-SQL Identifiers

    Take care,
    Solomon...

    I thought that binary collations were case-sensitive?  Decided to check for myself and was surprised.

  • Solomon Rutzky

    SSCoach

    Points: 15935

    Lynn Pettis - Friday, July 13, 2018 12:57 PM

    Solomon Rutzky - Friday, July 13, 2018 12:23 PM

    Please also note that transaction names are treated as having a binary Collation (not case-sensitive as the documentation currently states), regardless of the Instance-level or Database-level Collations.

    For details, please see the Transaction Names section of the following post: What’s in a Name?: Inside the Wacky World of T-SQL Identifiers

    Take care,
    Solomon...

    I thought that binary collations were case-sensitive?  Decided to check for myself and was surprised.

    It is a very common misconception that binary comparisons are case-sensitive (or anything sensitive, such as accent-sensitive, or width sensitive, etc), which is why I was clarifying there. Regarding the "checking", were you referring to this post:

    No, Binary Collations are not Case-Sensitive
    Take care,
    Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply