Implicit transaction mode

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Comments posted to this topic are about the item Implicit transaction mode

    Thanks

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • twin.devil

    SSC-Insane

    Points: 22208

    Good knowledge base question for the understanding of transaction scope. Thanks for sharing

  • Iulian -207023

    SSCertifiable

    Points: 7509

    OK I got it, so the key is in SET IMPLICIT_TRANSACTIONS ON ... interesting. Thank you for the question.

  • Ed Wagner

    SSC Guru

    Points: 286982

    This behavior is a lot more like Oracle (of old, anyway) than SQL Server. Thanks for the question.

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Iulian -207023 (8/12/2014)


    OK I got it, so the key is in SET IMPLICIT_TRANSACTIONS ON ... interesting. Thank you for the question.

    Correct 😛

    Thanks

  • SqlMel

    SSCrazy

    Points: 2891

    +1. Thanks.

    By the way, this also works the same in 2008 R2.

    ---------------
    Mel. 😎

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    SqlMel (8/12/2014)


    +1. Thanks.

    By the way, this also works the same in 2008 R2.

    +1

    (I have started a new thing ... testing each code block of the qtod from sql 2005 express and up till 2012, to see the behaviour, if it not works get the error message what it throws... its kind of fun)

    To post author: thank you for the post, interesting one. I had a clue on ALTER TABLE and TRUNCATE, but I had a vague idea on the CREAT, I selected the choice first and then I referred the local_help and there... my choice was sure.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Raghavendra Mudugal (8/12/2014)


    SqlMel (8/12/2014)


    +1. Thanks.

    By the way, this also works the same in 2008 R2.

    +1

    (I have started a new thing ... testing each code block of the qtod from sql 2005 express and up till 2012, to see the behaviour, if it not works get the error message what it throws... its kind of fun)

    To post author: thank you for the post, interesting one. I had a clue on ALTER TABLE and TRUNCATE, but I had a vague idea on the CREAT, I selected the choice first and then I referred the local_help and there... my choice was sure.

    Thanks for comments

    It works same in 2008 R2. Reference link is for SQL 2012 hence mentioned the same in QotD title. 🙂

    Thanks

  • This was removed by the editor as SPAM

  • Luis Cazares

    SSC Guru

    Points: 183633

    Ed Wagner (8/12/2014)


    This behavior is a lot more like Oracle (of old, anyway) than SQL Server. Thanks for the question.

    Except that Oracle will auto commit a transaction with any DDL statement. Or was it different before?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • TomThomson

    SSC Guru

    Points: 104773

    Stewart "Arturius" Campbell (8/12/2014)


    Good question, thanks Hugo

    The SET IMPLICIT_TRANSACTIONS setting caused a fair bit of fun when troubleshooting why one section of an application never committed any changes and took exclusive locks on the table...

    the developer never included a COMMIT or ROLLBACK in the respective stored proc.

    Developers who do that should be talked to; the aim of the conversation will be to discover which idiot DBA told the developer about implicit transactions but didn't mention that they had to be terminated, as well as to cause the developer sufficient embarrassment that in future he will look stuff up before he uses it.

    Tom

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    TomThomson (8/12/2014)


    Stewart "Arturius" Campbell (8/12/2014)


    Good question, thanks Hugo

    ....

    hmm "Hugo"?

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Thomas Abraham

    SSChampion

    Points: 10761

    Koen Verbeeck (8/12/2014)


    Nice question, thanks.

    Koen, are you doing something different with your hair? (He said, noticing something strange about Koen's icon.)

    (Later realizing that he was mistaken, as he was thinking of Koen's avatar on LinkedIn instead of on SSC.)

    Never mind.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Cliff Jones

    SSChampion

    Points: 10517

    Raghavendra Mudugal (8/12/2014)


    TomThomson (8/12/2014)


    Stewart "Arturius" Campbell (8/12/2014)


    Good question, thanks Hugo

    ....

    hmm "Hugo"?

    Yes, today's email stated that it was Hugo's question for some reason.

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

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