Transactions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sriram » Transactions

32 posts, Page 1 of 4

1 2 3 4 »»»

Transactions

Rate Topic

Display Mode

Topic Options

Author

Message

Sriram-288748

Posted Wednesday, March 18, 2009 10:22 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 29, 2013 12:19 AM
Points: 219, Visits: 326

Comments posted to this topic are about the item Transactions

Sriram

Post #679141

michael.kaufmann

Posted Thursday, March 19, 2009 3:36 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 11:51 PM
Points: 1,263, Visits: 1,079

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 . . . Wink

Thanks for the lesson.

Post #679246

Ben Leighton

Posted Thursday, March 19, 2009 3:49 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 05, 2011 8:18 AM
Points: 582, 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...

Post #679255

dmoldovan

Posted Thursday, March 19, 2009 5:26 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:51 AM
Points: 360, Visits: 1,260

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.

Post #679295

Derek Dongray

Posted Thursday, March 19, 2009 7:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946

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! BigGrin

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

Derek

Post #679382

dmoldovan

Posted Thursday, March 19, 2009 7:37 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:51 AM
Points: 360, Visits: 1,260

Since the question didn't mention any details about the isolation level, I assumed that it is the default READ COMMITTED...

Post #679412

Sriram-288748

Posted Thursday, March 19, 2009 7:56 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 29, 2013 12:19 AM
Points: 219, Visits: 326

Expected READ_COMMITTED_SNAPSHOT to be set to OFF, thought will remain default atleast for test db's ...

Sriram

Post #679436

Chris Harshman

Posted Thursday, March 19, 2009 8:02 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 7:19 AM
Points: 1,562, Visits: 1,716

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.

Post #679439

Sugesh Kumar

Posted Thursday, March 19, 2009 8:20 AM

Hall of Fame

Group: General Forum Members
Last Login: Saturday, November 10, 2012 8:18 AM
Points: 3,461, Visits: 346

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

Post #679455

Always Learning

Posted Thursday, March 19, 2009 8:22 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 14, 2013 6:03 AM
Points: 1,236, Visits: 3,342

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.

Post #679459

« Prev Topic | Next Topic »

32 posts, Page 1 of 4

1 2 3 4 »»»

Permissions