Post reply

Begin Transaction - End Transaction in a select statement

  • October 22, 2013 at 8:14 am

    #395703

    General question - Has anyone seen or had experience with stored procedures that include BEGIN TRANSACTION and END TRANSACTION commands in a select statement? If so, what advantages/disadvantages are there to including them and conversely, what advantages/disadvantages are there to excluding them?

    I am of the belief that they serve no purpose in a select statement and are totally unnecessary. Agree? Disagree?

    Thanks,

  • October 22, 2013 at 8:23 am

    #1660550

    In the default isolation level, no effect. In other isolation levels it can change behaviour and results.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 22, 2013 at 8:26 am

    #1660554

    As was stated above it doesn't have an effect on READ COMMITTED or READ UNCOMMITED, but it has a big effect if your isolation level is set to REPEATABLE READ or SERIALIZABLE. So, the answer is it depends on isolation level.


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • October 22, 2013 at 8:29 am

    #1660557

    Keith Tate (10/22/2013)

    but it has a big effect if your isolation level is set to REPEATABLE READ or SERIALIZABLE.

    or SNAPSHOT

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 24, 2013 at 7:38 am

    #1661279

    I am of the belief that they serve no purpose in a select statement and are totally unnecessary. Agree? Disagree?

    Thanks,

    Agree.

  • October 24, 2013 at 8:19 am

    #1661307

    T.Ashish (10/24/2013)

    I am of the belief that they serve no purpose in a select statement and are totally unnecessary. Agree? Disagree?

    Thanks,

    Agree.

    Incorrect. See my and Keith's posts above.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

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