March 18, 2009 at 10:22 pm
Comments posted to this topic are about the item Transactions
Sriram
March 19, 2009 at 3:36 am
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.
March 19, 2009 at 3:49 am
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...
March 19, 2009 at 5:26 am
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.
March 19, 2009 at 7:20 am
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
March 19, 2009 at 7:37 am
Since the question didn't mention any details about the isolation level, I assumed that it is the default READ COMMITTED...
March 19, 2009 at 7:56 am
Expected READ_COMMITTED_SNAPSHOT to be set to OFF, thought will remain default atleast for test db's ...
Sriram
March 19, 2009 at 8:02 am
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.
March 19, 2009 at 8:20 am
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
March 19, 2009 at 8:22 am
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.
March 19, 2009 at 8:26 am
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.
March 19, 2009 at 8:36 am
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. 🙂
March 19, 2009 at 8:42 am
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.... 😀
March 19, 2009 at 8:56 am
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.
March 19, 2009 at 9:10 am
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 31 total)
You must be logged in to reply to this topic. Login to reply