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 Wednesday, March 18, 2009 10:22 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
Comments posted to this topic are about the item Transactions

Sriram

Post #679141
Posted Thursday, March 19, 2009 3:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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.
Post #679246
Posted Thursday, March 19, 2009 3:49 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 5, 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
Posted Thursday, March 19, 2009 5:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:08 AM
Points: 363, Visits: 1,319
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
Posted Thursday, March 19, 2009 7:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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!

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


Derek
Post #679382
Posted Thursday, March 19, 2009 7:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:08 AM
Points: 363, Visits: 1,319
Since the question didn't mention any details about the isolation level, I assumed that it is the default READ COMMITTED...
Post #679412
Posted Thursday, March 19, 2009 7:56 AM
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
Expected READ_COMMITTED_SNAPSHOT to be set to OFF, thought will remain default atleast for test db's ...

Sriram

Post #679436
Posted Thursday, March 19, 2009 8:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:07 AM
Points: 1,866, Visits: 2,018
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
Posted Thursday, March 19, 2009 8:20 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: Monday, August 11, 2014 9:39 AM
Points: 3,461, Visits: 350
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
Posted Thursday, March 19, 2009 8:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:18 AM
Points: 1,604, Visits: 3,359
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 »

Add to briefcase 1234»»»

Permissions Expand / Collapse