Transactions

  • Sriram-288748

    SSCrazy

    Points: 2874

    Comments posted to this topic are about the item Transactions

    Sriram

  • michael.kaufmann

    SSCrazy

    Points: 2816

    Aha--the trick is to run the second part in another query window ... not the same one (in which case all 3 records will be returned).

    Should have followed the instructions . . . 😉

    Thanks for the lesson.

  • Ben Leighton

    SSCommitted

    Points: 1838

    this question is flawed...

    if the SET READ_COMMITTED_SNAPSHOT is set to on for the database you are using then the query will return 1 and 2...

    I'm being picky here I admit...

  • dmoldovan

    SSCertifiable

    Points: 6334

    If you query sys.dm_tran_locks you'll see an IX lock taken on the test_tran table.

    If you run in the second window SELECT col1 FROM test_tran WHERE col1 = 1 (or 2) the query will work.

  • StarNamer

    SSCrazy Eights

    Points: 8633

    Ben Leighton (3/19/2009)


    this question is flawed...

    if the SET READ_COMMITTED_SNAPSHOT is set to on for the database you are using then the query will return 1 and 2...

    I'm being picky here I admit...

    I decided it should be 1 & 2 and gave that answer, then tested it in my scratch database and, of course, got 1 & 2 returned in the second window.

    QotD didn't state that database should have READ_COMMITTED_SNAPSHOT set to OFF and my scratch database still had it set to ON from tests done when responding to a QotD a few weeks back! 😀

    Of course, since the scratch database rarely had any extended transactions in it, the difference isn't often noticed.

    Derek

  • dmoldovan

    SSCertifiable

    Points: 6334

    Since the question didn't mention any details about the isolation level, I assumed that it is the default READ COMMITTED...

  • Sriram-288748

    SSCrazy

    Points: 2874

    Expected READ_COMMITTED_SNAPSHOT to be set to OFF, thought will remain default atleast for test db's ...

    Sriram

  • Chris Harshman

    SSC-Forever

    Points: 41843

    Ben Leighton (3/19/2009)


    this question is flawed...

    if the SET READ_COMMITTED_SNAPSHOT is set to on for the database you are using then the query will return 1 and 2...

    I'm being picky here I admit...

    I don't think you're being picky at all, I'm sure many people are using row versioning with the READ_COMMITTED_SNAPSHOT ON setting.

  • Sugesh Kumar

    One Orange Chip

    Points: 27311

    This was really a very good question. We had the same problem in the past where a developer had a transaction open and we went into facing slowness in the query. Nice Question.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • Always Learning

    SSCrazy

    Points: 2060

    Interestingly, running it in my test database it returned in a single query window. Regardless of how it is run, all as a single query, each separate, in different query windows, etc. The result is the same.

    Col1

    1

    2

    3

    It seems the question of the day has some shortcomings.

  • ppcx

    Ten Centuries

    Points: 1299

    As a long-time Oracle DBA and a new MSSQL DBA I find this whole concept mind-boggling. If it hadn't been for the prior isolation-level question (which I missed) and then yesterday's (Guest) Editorial I would've gotten today's question wrong too.

  • LGibson69

    SSC Rookie

    Points: 46

    I expected to get no records because the transaction was still open but, when I ran it on SQL 2008 I got an error message saying "Msg 208, Level 16, State1, Line 3. Invalid object name 'test_tran'. Since this is the actual result I got I selected "None of the above" as the correct answer and was told I was wrong. I think I should get my "point" because my answer was technically correct. 🙂

  • Bob Griffin

    SSCertifiable

    Points: 5976

    Good question because it made us think....I missed it, but I learned something which is really the point....It's very tough to cover all your bases on these questions.... 😀

  • JF1081

    SSC Eights!

    Points: 982

    Mine ran for a while with no results so being the impatient person I am I decided to see what would happen if I ran the first one again. Upon doing so it returned the first 2 rows (1 and 2) in the second query.

    I am using SQL Server 2005.

  • h84liang

    Grasshopper

    Points: 15

    dear Friends,

    i have testing this question and return all values (1,2,3) if i execute it 😀

    thx

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

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