Read only commited data

Question

Hi. I have a table called [Test], which has a single column [Id] int.

I open a transaction, insert one row to the table and NOT commit it.

begin tran
insert into [Test]([Id]) values(1)

In another request I want to select data from table [Test]. How can I read only commited data immediately?

Readcommited table hint holds a lock.

select * from test with(readcommitted) 

Thank you.

Answer 1

I think you are looking for the transaction isolation level settings: http://msdn.microsoft.com/en-us/library/aa259216(SQL.80).aspx

Using isolation level SERIALIZABLE ensures you can't read the uncommitted data, but will at the same time lower your server performance.

Answer 2

select * from test with(readpast) 

Will work as long as the transaction with uncommitted data only has row locks. Otherwise look into the two snapshot isolation alternatives (assuming you are on at least SQL Server 2005+).