How do I only select rows that have been committed - sql2008.

Question

How do I select all rows for a table, their isn't part of any transaction that hasn't committed yet?

Example: Let's say,

Table T has 10 rows.

User A is doing a transaction with some queries:

INSERT INTO T (...)
SELECT ...
FROM T

// doing other queries 

Now, here comes the tricky part:

What if User B, in the time between User A inserted the row and the transaction was committed, was updating a list in the system with a select on Table T.

I only want that the SELECT User B is using returned the 10 rows(all rows from the table, that can't later be rolled back). How do I do this, if it's even possible?

I have tried setting the isolationlevel on the transaction and adding "WITH(NOLOCK)" "WITH(READUNCOMMITTED)" to the query without any luck.

The query either return all 11 records or it's waiting for the transaction to commit, and that's not what I need.

Any tips is much appriciated, thanks.

Answer 1

Actually, read uncommitted and nolock are the same. They mean you get to see rows that have not been committed yet.

If you run at the default isolation level, read committed, you will not see new rows that have not been committed. This should work by default, but if you want to be sure, prefix your select with set transaction isolation level read committed.

Answer 2

You need to use (default) read committed isolation level and the READPAST hint to skip rows locked as they are not committed (rather than being blocked waiting for the locks to be released)

This does rely on the INSERT taking out rowlocks though. If it takes out page locks you will be back to being blocked. Example follows

Connection 1

IF OBJECT_ID('test_readpast') IS NULL
BEGIN
   CREATE TABLE test_readpast(i INT PRIMARY KEY CLUSTERED)
   INSERT INTO test_readpast VALUES (1)
END

BEGIN TRAN
      INSERT INTO test_readpast 
      WITH(ROWLOCK) 
      --WITH(PAGLOCK)
      VALUES (2)
   SELECT * FROM sys.dm_tran_locks WHERE request_session_id=@@SPID
   WAITFOR DELAY '00:01';
ROLLBACK

Connection 2

SELECT i
FROM test_readpast WITH (readpast)

Answer 3

Snapshot isolation ?

Either I or else the three people who have answered early have misread/ misinterpreted your question, so I have given a link so you can determine for yourself.