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