SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactions


Transactions

Author
Message
Sriram-288748
Sriram-288748
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1260 Visits: 340
Comments posted to this topic are about the item Transactions

Sriram
michael.kaufmann
michael.kaufmann
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1737 Visits: 1082
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.
Ben Leighton
Ben Leighton
Ten Centuries
Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)

Group: General Forum Members
Points: 1000 Visits: 359
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...
dmoldovan
dmoldovan
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 1481
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.
StarNamer
StarNamer
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3700 Visits: 1992
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! :-D

Of course, since the scratch database rarely had any extended transactions in it, the difference isn't often noticed.

Derek
dmoldovan
dmoldovan
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 1481
Since the question didn't mention any details about the isolation level, I assumed that it is the default READ COMMITTED...
Sriram-288748
Sriram-288748
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1260 Visits: 340
Expected READ_COMMITTED_SNAPSHOT to be set to OFF, thought will remain default atleast for test db's ...

Sriram
Chris Harshman
Chris Harshman
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16350 Visits: 5174
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.
Sugesh Kumar
Sugesh Kumar
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12631 Visits: 358
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
Always Learning
Always Learning
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

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