Read both underlying and uncommitted transaction data

Question

Using SQL Server from .NET, is it possible (using the same or separate connections) to read both the underlying data as well as the as-yet uncommitted changes from another separate connection?

For example:

I have Connection1, which starts a transaction and inserts a record with Id == 1 into a table but doesn't commit it

From Connection2, I would like to read the table without that row existing

From Connection2 or Connection3, I would like to read the table with the row existing.

gbn has the right answer. But I have to warn you - I went down this road a few years ago for performance reasons and it ended up causing nothing but trouble. If you're trying to speed up a query spend more time on the query and let SQL Server do the right thing with locks. — n8wrl, Jan 18 '11 at 13:14
Why? Each connection shouldn't care about other transactions.. — gbn, Jan 18 '11 at 19:10
It was to implement [this](http://stackoverflow.com/questions/4725512/) but I've gone with a different solution now anyway. — enashnash, Jan 19 '11 at 16:11

gbn · Accepted Answer · 2011-01-18T13:49:15.767

0

Yes, you need to enable dirty reads aka READ UNCOMMITTED

Edit:

To read both sets of data you'd need a combination of "snapshot isolation" in one connection and "read uncommitted" in another.

YMMV. It isn't something...

I've tried
See a use for
I'd have confidence in

edited Jan 18 '11 at 13:49

answered Jan 18 '11 at 12:55

gbn

422,506
82
585
676

I'm aware of READ UNCOMMITTED, which will allow me to read the uncommitted changes, but I think maybe my question wasn't clear, so I've updated it. The question is can I access BOTH underlying and uncommitted changes for the SAME transaction. – enashnash Jan 18 '11 at 13:12
I've marked this as the answer, but I didn't go ahead with what I asked in my question in the end - it just seemed too flaky. – enashnash Feb 04 '11 at 10:13

score 0 · Answer 2 · answered Jan 18 '11 at 13:28

As you know, you can review uncommitted data within the current session like so:

CREATE TABLE TestTable
(
    ID int not null
);

INSERT INTO TestTable(ID) values(1);
INSERT INTO TestTable(ID) values(2);
INSERT INTO TestTable(ID) values(3);

SELECT * FROM TestTable;

BEGIN TRANSACTION

    INSERT INTO TestTable(ID) values(4);
    INSERT INTO TestTable(ID) values(5);

    --Although the transaction has not commited you can still see the records inserted from within the current sessions scope.
    SELECT * FROM TestTable;


COMMIT TRANSACTION

DROP TABLE TestTable;

Now suppose you where to open another connection that you wanted to be able to see this uncommited data. You could do so at the query level by using the NOLOCK query hint. For example:

SELECT * FROM TestTable WITH(NOLOCK)

Read both underlying and uncommitted transaction data

2 Answers2

Linked