Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Transactions Expand / Collapse
Author
Message
Posted Thursday, March 19, 2009 10:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 20, 2013 2:04 AM
Points: 222, Visits: 331
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

Post #680124
Posted Friday, March 20, 2009 9:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 22, 2010 10:07 AM
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...
Post #680505
Posted Friday, March 20, 2009 10:15 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 8,287, Visits: 8,738
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
Post #680533
Posted Friday, March 20, 2009 10:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, March 28, 2014 11:01 AM
Points: 3,047, Visits: 1,229
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.



Post #680540
Posted Friday, March 20, 2009 12:03 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 2:35 AM
Points: 362, Visits: 1,311
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.
Post #680621
Posted Friday, March 20, 2009 1:23 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 8,287, Visits: 8,738
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
Post #680660
Posted Friday, March 20, 2009 1:46 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 2:35 AM
Points: 362, Visits: 1,311
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.
Post #680676
Posted Friday, April 24, 2009 7:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 29, 2010 7:15 AM
Points: 331, 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"
Post #703933
Posted Wednesday, January 06, 2010 5:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 6:08 AM
Points: 1,076, Visits: 591
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
Post #842701
Posted Tuesday, July 06, 2010 3:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:17 PM
Points: 40, Visits: 114
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
Post #948221
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse