CODEWITHSUNDEEP
sqlsql-servert-sql
SuperX
SuperX

Reputation: 63

Read-only committed data

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 committed data immediately?

Readcommited table hint holds a lock.

select * from test with(readcommitted)

Thank you.

Upvotes: 3

Views: 2420

Answers (2)

Martin Smith
Martin Smith

Reputation: 453648

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+).

Upvotes: 4

Liedman
Liedman

Reputation: 10329

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.

Upvotes: 1

Related Questions