transaction test!

  • VM-723206

    SSCrazy

    Points: 2964

    Comments posted to this topic are about the item transaction test!

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4297

    Good question!

    (seeing as we regularly complain, thought I should show some support :))

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    Seconded.

    (I lost my point - I was so focused on the details of the nested transactions that I failed to see that the code used a table variable instead of a temp or perm table - silly 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/

  • kevriley

    SSCrazy Eights

    Points: 8907

    Hugo Kornelis (7/7/2009)


    (I lost my point - I was so focused on the details of the nested transactions that I failed to see that the code used a table variable instead of a temp or perm table - silly me!)

    Ditto!

    Must wake up with more coffee before attempting QoTD!

    Kev

  • magasvs

    SSCertifiable

    Points: 7659

    Good question!

  • Jonathan Melo

    SSC Veteran

    Points: 279

    Very good catch.

  • Rudyx - the Doctor

    SSC-Forever

    Points: 43691

    Very good question.

    How does this behavior stack up against ACID though ?

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717408

    I thought this was great, and I learned something when this came through.

    I don't know how this is related to ACID, but it's a nice little loophole for logging things. Since it's not a "Real" table, but a variable, I assume this doesn't impact integrity.

  • webrunner

    SSC-Dedicated

    Points: 30095

    Great question. I lucked out by picking the right answer for the wrong reason: I mistakenly thought committing transaction T2 committed T1 as well, not that table variables ignore transaction statements.

    Shows me how much more I have to learn.:w00t:

    - 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

  • OCTom

    SSChampion

    Points: 11755

    Hee, hee. I, too got this right for the wrong reason. Like Webrunner, I thought the commitment of T2 committed T1. Oops.

    Good question.

  • Lynn Pettis

    SSC Guru

    Points: 442234

    I missed too! I knew the correct answer, if I had noticed it was using table variables. Missed that, choose the wrong answer.

  • ramu.valleti

    SSCrazy

    Points: 2229

    Can any one please help me...

    when i execute the following query,

    BEGIN TRANSACTION T1

    INSERT INTO table_1 VALUES ('ABA','xxx',130)

    COMMIT TRANSACTION T1

    BEGIN TRANSACTION T2

    INSERT INTO table_1 VALUES ('ABB','ccc',510)

    ROLLBACK TRANSACTION T2

    select * from table_1

    i'm getting this warning..

    "(1 row(s) affected)

    (1 row(s) affected)

    Msg 6401, Level 16, State 1, Line 6

    Cannot roll back T2. No transaction or savepoint of that name was found."

    is anything wrong with query?

    Ramu
    No Dream Is Too Big....!

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    ramu.valleti (7/7/2009)


    Can any one please help me...

    when i execute the following query,

    BEGIN TRANSACTION T1

    INSERT INTO table_1 VALUES ('ABA','xxx',130)

    COMMIT TRANSACTION T1

    BEGIN TRANSACTION T2

    INSERT INTO table_1 VALUES ('ABB','ccc',510)

    ROLLBACK TRANSACTION T2

    select * from table_1

    i'm getting this warning..

    "(1 row(s) affected)

    (1 row(s) affected)

    Msg 6401, Level 16, State 1, Line 6

    Cannot roll back T2. No transaction or savepoint of that name was found."

    is anything wrong with query?

    Hi Ramu,

    I copied and pasted that SQL in a new query window, added the CREATE TABLE for table_1 before it and a SELECT * FROM table_1 after it, and I got no errors. Only the first row was returned, as expected (since the second insert was rolled back).

    Are you sure this is the exact code you tried?


    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/

  • niall.baird

    Hall of Fame

    Points: 3468

    Seems a bit silly to have a transaction that doesn't apply to table variables - I think I'll have to go and read MSDN to find out a bit more!

    Great question by the way - just goes to show that I don't know everything (even though my manager seems to think so).

    Off to go fix up world peace now!

  • VM-723206

    SSCrazy

    Points: 2964

    This was a nice thing to learn! Thanks to all for the Boquets in this one and Brickbats in others! 😉

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

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