Transactions
-
Comments posted to this topic are about the item Transactions
Sriram
-
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.
-
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...
-
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.
-
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
-
Since the question didn't mention any details about the isolation level, I assumed that it is the default READ COMMITTED...
-
Expected READ_COMMITTED_SNAPSHOT to be set to OFF, thought will remain default atleast for test db's ...
Sriram
-
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.
-
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 -
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.
-
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.
-
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.
-
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....
-
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.
-
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