IMPLICIT_TRANSACTIONS & BEGIN TRAN

  • Comments posted to this topic are about the item IMPLICIT_TRANSACTIONS & BEGIN TRAN

  • Nice question.

    I think that having BEGIN TRANSACTION increment trancount by 2 instead of 1 is utterly silly. It's a really nasty trap that can cause pointless and damaging rollbacks.

    Tom

  • Nice question & informative explanation, thanx.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • Very interesting question, thanks.

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

  • TomThomson (1/10/2015)


    Nice question.

    I think that having BEGIN TRANSACTION increment trancount by 2 instead of 1 is utterly silly. It's a really nasty trap that can cause pointless and damaging rollbacks.

    ROLLBACK TRAN, if not specified the name of transaction, rollbacks all open transaction and reset @@trancount to ZERO.

    Some problem may occur if you think to commit all modifications.

    So, when I want to be sure about commitment I run this command:

    WHILE @@TRANCOUNT > 0

    COMMIT

    Here is an example of loosing data:

    -- new connection

    SET IMPLICIT_TRANSACTIONS ON

    BEGIN TRAN

    UPDATE mytab SET mycol = 0

    COMMIT -- Here, I think it's all right

    -- disconnect = ROLLBACK

    Here is an example of an application locking resources:

    SET IMPLICIT_TRANSACTIONS ON

    BEGIN TRAN

    UPDATE mytab SET mycol = 0

    COMMIT

    Application continues but it is still locking resources and in case of disconnection or rollback all modifications are lost.

  • good question.

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • Nice question. Thanks for sharing

  • Interesting, if I run this in SQL Management Studio I get 0,0,2,2,1

    but if I run it from TOAD for SQL Server I get 2,2,3,3,2

  • richxs (1/12/2015)


    Interesting, if I run this in SQL Management Studio I get 0,0,2,2,1

    but if I run it from TOAD for SQL Server I get 2,2,3,3,2

    From BOL:

    Implicit transactions may unexpectedly be on due to ANSI defaults.

  • This was removed by the editor as SPAM

  • Good question. I don't like the increment by 2 business, but it probably exists for a reason.

  • I've never used this.

  • Great, thought-provoking question. Thanks.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Thank you for the post, Carlo, really good one.

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

  • I never liked implicit transaction, and I never will.

    I am also glad that Carlo used PRINT, not SELECT. I thought that using SELECT would have started the implicit transaction, but I tested after answering this question and nothing changes if I use SELECT instead of PRINT. That was a surprise to me.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

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

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