Transactions in T-SQL

  • venus.pvr

    SSCarpal Tunnel

    Points: 4813

    Comments posted to this topic are about the item Transactions in T-SQL

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Lots of reading 🙂

    Thanks for the question.

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

  • Stuart Davies

    SSCoach

    Points: 18878

    Koen Verbeeck (11/12/2012)


    Lots of reading 🙂

    Thanks for the question.

    Agree - good question though.

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    awesome read. 🙂

    (there were some statements where I guess were added to create extra confusion... OR to make it more interesting... 😉 :rolleyes:)

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

  • This was removed by the editor as SPAM

  • Toreador

    SSChampion

    Points: 11261

    Rather complicated!

    But easy to guess the right answer, as there had to be at least 2 rows in #temp (the 2 inserted outside the loop) which ruled out half the answers; and the loop was either going to insert 6 rows or none, which ruled out the other.

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    Thanks for the question.

    Got it wrong because I missed the last INSERT statement after the WHILE LOOP.

    If the intention was to test the knowledge of nested transactions then the code was too complicated in my opinion.

    A much more readable example that also tests the users knowledge of transactions could have been

    create table #t1(id int)

    insert into #t1 values(1)

    begin tran t1

    insert into #t1 values(2)

    begin tran t2

    insert into #t1 values(3)

    begin tran t3

    insert into #t1 values(3)

    if @@trancount>0

    rollback tran

    if @@trancount>0

    commit tran t2

    if @@trancount>0

    rollback tran t1

    select * from #t1

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Nils Gustav Stråbø (11/13/2012)


    Thanks for the question.

    Got it wrong because I missed the last INSERT statement after the WHILE LOOP.

    If the intention was to test the knowledge of nested transactions then the code was too complicated in my opinion.

    A much more readable example that also tests the users knowledge of transactions could have been

    create table #t1(id int)

    insert into #t1 values(1)

    begin tran t1

    insert into #t1 values(2)

    begin tran t2

    insert into #t1 values(3)

    begin tran t3

    insert into #t1 values(3)

    if @@trancount>0

    rollback tran

    if @@trancount>0

    commit tran t2

    if @@trancount>0

    rollback tran t1

    select * from #t1

    the point here is.. once you check the tran count and you roll back them - it rolls back all of them... so there is no point to check the transaction again and commit it, as it never going to be greater than 0 after rolled back, in your example you created the transaction each one manually and the same is achieved via while loop in the Qtod, and there were some statements which adds kind of interest to the analysis where the making a choice going to take a while.

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

  • rodjkidd

    SSCoach

    Points: 15847

    Godd Question.

    I did a big oops though, missed the first insert before the loop, so my second count was always out. Must rememeber to drink cofee before QOD...

    Rodders...

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    Raghavendra, exactly. Keep it simple. That way you'll be able to test the reader's knowledge about transactions. No need to add a lot of extra noise.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Nils Gustav Stråbø (11/13/2012)


    Raghavendra, exactly. Keep it simple. That way you'll

    be able to test the reader's knowledge about transactions. No need to add a lot of extra noise.

    I agree with that Nils. 🙂

    (Simple is best... but as you know these Qtods can be tricky and it cannot meet the standards of acceptance to all so it is hard to form a question which is easily known to everyone, I just tag along and learn stuff. Actually not sure if this applies to all, but for me these noises are the best part where it makes the learning more fun/interesting and also it helps me to understand the (same) problem again in different way (if there's a chance) ... It can be pain sometimes but this pain makes us stronger... like a challenge.)

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

  • TomThomson

    SSC Guru

    Points: 104773

    Good question.

    I got it wrong through sheer carelessness - read too quickly, and failed to remember when I got to the end that the very first insert is before the first transaction begins so that it isn't rolled back and it contributes a row to the final total.

    The mixed style of some DML in explicit transactions and some not is something I rather dislike, since results are going to be different according to whether implicit transactions are off or on. There was no need, of course, to specify that they are off in this question since (a) implicit transactions are off by default (autocommit is the default transaction mode for DML outside explicit transactions), so that's what we should assume, and (b) none of the answers is correct for the case when implicit transactions are on, so thinking it's on wouldn't point at a wrong answer; but the question could form the basis of a nice fun demonstration of the discrepancies that can occurr when a chunk of code is called sometimes with autocommit on and sometimes with implicit transactions on.

    Tom

  • (Bob Brown)

    SSCrazy

    Points: 2705

    Great question! I like this type of question that needs some research and reading for some of us. Keep them coming.

  • Tyle

    SSC Rookie

    Points: 36

    Good question. Got it wrong though, missed the first select..into statement before the while loop.

  • marklegosz

    SSCrazy

    Points: 2816

    Today must be my lucky day. Saw the question, decided it was too long & complicated, guessed at random, got it right. Now off to buy a lotto ticket 🙂

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

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