March 19, 2009 at 9:11 am
Good question. I didn't read it correctly and executed in same window. Which gave me 1,2,3 but after it got wrong, then tried it in another window and it was still running.
Will see if it returns something after sometime.
SQL DBA.
March 19, 2009 at 9:12 am
h84liang (3/19/2009)
dear Friends,i have testing this question and return all values (1,2,3) if i execute it 😀
thx
Dude, read question correctly. It say's execute it in another window and not the same.
Try again.
SQL DBA.
March 19, 2009 at 10:42 am
Sometimes missing information in a question induces thought, teaches, and also helps one to permanently retain what was learned.
I think this was a great question, and also the points brought out in discussion.
March 19, 2009 at 11:02 am
LGibson69 (3/19/2009)
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. 🙂
I am guessing you are running in a case-sensitive environment. I am too and had the same error. I corrected the second query to use the same EXACT name as the first. I then had the proper result (based on SNAPSHOT ISOLATION being OFF).
March 19, 2009 at 11:10 am
At first I was confused about why it would not return the already committed data and I did an experiment by changing the code. Very interesting question. Thanks!
--first window
CREATE TABLE Test_Tran(id int identity, col1 int)
ALTER TABLE Test_Tran ADD CONSTRAINT [pk_Test_Tran] PRIMARY KEY CLUSTERED (id ASC)
insert into Test_Tran values(1)
insert into Test_Tran values(2)
Begin Tran
insert into Test_Tran values(3)
--second window
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select * from a_test_tran where id < 3
--results
id col1
----------- -----------
1 1
2 2
(2 row(s) affected)
March 19, 2009 at 10:31 pm
Thanks for all the people who say the question is interesting, and also thanks to all those people who have pointed out the short comings in the question.
Sriram
March 20, 2009 at 9:48 am
Regardless of the settings, shouldn't the Start Tran had implicitely Committed the previous activities? and returned 1 and 2?
After all, the Start Tran started a new Logical Unit of Work...
March 20, 2009 at 10:15 am
Chris Harshman (3/19/2009)
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.
I agree, he certainly isn't being picky. 🙂
And that's not the only flaw in the question and its answer. 🙁
The answer stated is WRONG even if read_committed_snapshot is off. The correct answer is the one I gave: "none of the above", because the query is NOT running; it is blocked from running by a lock.
So there are two possible correct answers depending on the sate of read_committed_snapshot, and the poser of the question managed to select neither of them as the correct answer. Amazing.
Tom
March 20, 2009 at 10:22 am
natet (3/20/2009)
Regardless of the settings, shouldn't the Start Tran had implicitely Committed the previous activities? and returned 1 and 2?After all, the Start Tran started a new Logical Unit of Work...
I am not sure but I think that since there is no index or key defined on the table the select statement will cause a table scan and because there is an open transaction against this table the query will not run to completion.
March 20, 2009 at 12:03 pm
kevin.l.williams (3/20/2009)
I am not sure but I think that since there is no index or key defined on the table the select statement will cause a table scan and because there is an open transaction against this table the query will not run to completion.
Even if the table had a clustered index defined, if you issue a "SELECT *" this statement will not work, because you scan all the data. If you issue a "SELECT col1 FROM Test_Tran WHERE col1 = 1" this will work, if the table has a clustered index defined on col1. If not, it will not work. Sorry for forgetting to specify this in my previous post.
March 20, 2009 at 1:23 pm
dmoldovan (3/20/2009)
kevin.l.williams (3/20/2009)
I am not sure but I think that since there is no index or key defined on the table the select statement will cause a table scan and because there is an open transaction against this table the query will not run to completion.Even if the table had a clustered index defined, if you issue a "SELECT *" this statement will not work, because you scan all the data. If you issue a "SELECT col1 FROM Test_Tran WHERE col1 = 1" this will work, if the table has a clustered index defined on col1. If not, it will not work. Sorry for forgetting to specify this in my previous post.
This is slightly wrong - it makes no difference whether the index is clustered or not. What's required for the query to run instead of waiting for the lock to be released is that the where clause of the query is such that the index (clustered or not) is used and causes the uncommitted row not to be visited so that the lock is not encountered.
At least this is true of SQLS 2000. I very much doubt if it's changed in later versions, since it delivers what MS wanted (high concurrency) for this isolation level.
Tom
March 20, 2009 at 1:46 pm
Tom.Thomson (3/20/2009)
This is slightly wrong - it makes no difference whether the index is clustered or not. What's required for the query to run instead of waiting for the lock to be released is that the where clause of the query is such that the index (clustered or not) is used and causes the uncommitted row not to be visited so that the lock is not encountered.
At least this is true of SQLS 2000. I very much doubt if it's changed in later versions, since it delivers what MS wanted (high concurrency) for this isolation level.
Yes, it is true for a non clustered index, too. The point is to use a "WHERE" in order to avoid selecting the uncommited row.
April 24, 2009 at 7:22 am
following the example from the question the second batch running on a different windows is waiting for the first one: therefore the answer "None of the above"
January 6, 2010 at 5:49 am
I'll also add on by saying that the qry will run till you add the "commit tran"
statement at the end of the 1st qry
July 6, 2010 at 3:32 pm
Why does this lock the entire table when only one of the rows is locked in a transaction? I thought the first two inserts were implicit transactions - so they would be committed when they ran? Or is is just locking the entire table because all 3 rows are on the same page?
I have always wondered exactly how this works at this level. I seem to have more locking issues on new systems with few rows in the tables.
CREATE TABLE Test_Tran( col1 int)
insert into Test_Tran values(1)
insert into Test_Tran values(2)
Begin Tran
insert into Test_Tran values(3)
--------------
--2nd window
--------------
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select * from test_tran
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply