Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactions


Transactions

Author
Message
Sriram-288748
Sriram-288748
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 340
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
natet-998902
natet-998902
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 10
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...
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10709 Visits: 12008
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. Sad

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

kevin.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3379 Visits: 1323
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.



dmoldovan
dmoldovan
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 1454
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.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10709 Visits: 12008
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

dmoldovan
dmoldovan
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 1454
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.
dstemate
dstemate
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 62
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"
BudaCli
BudaCli
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 598
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

What you don't know won't hurt you but what you know will make you plan to know better
gregbogard-502733
gregbogard-502733
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 144
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search