How can I read the last commited record values while an other transaction is updating

The situation is like this:
I have a record in a table that I want to read and I want the last committed
version of it.
The problem is that another transaction is updating the record at the same
time in a verry long transaction. While this transaction is running I can
not read the record because it is locked. How can I read the current
commited values from this record without blocking?
I do not want to use READ UNCOMMITED isolation level because if the long
running transaction is rolled back than I would have read a uncommited
value.
This situation happens in a dotnet application using 2 SQLConnection classes
(and connections to the DB) to read and update the same record.
Thanks,
Sandor"Sandor Heese" <nospam@.hotmail.com> wrote in message
news:Ot2Iyf2HFHA.2156@.TK2MSFTNGP09.phx.gbl...
> The situation is like this:
> I have a record in a table that I want to read and I want the last
> committed version of it.
> The problem is that another transaction is updating the record at the same
> time in a verry long transaction. While this transaction is running I can
> not read the record because it is locked. How can I read the current
> commited values from this record without blocking?
> I do not want to use READ UNCOMMITED isolation level because if the long
> running transaction is rolled back than I would have read a uncommited
> value.
> This situation happens in a dotnet application using 2 SQLConnection
> classes (and connections to the DB) to read and update the same record.
>
First, why are you using 2 SqlConnections?
Second, no there is no way to read the last commited version of a locked
row. That is "snapshot isolation" and it's a new feature of SQL Server
2005. With SQL Server 2000 you must choose between reading the row after
it's commited, or read the dirty row.
David|||Thanks David for your comment.
I came to the same conclusion you did, but I did not want to beleave that it
was not possible :-)
I am using 2 connections because I can't do 2 transaction at the same time
over 1 SQLConnection.
In SQL Server 2005 this will be possible but not at the moment. (It is
always in the next version :-( )
Sandor
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uUvhOo2HFHA.2564@.tk2msftngp13.phx.gbl...
> "Sandor Heese" <nospam@.hotmail.com> wrote in message
> news:Ot2Iyf2HFHA.2156@.TK2MSFTNGP09.phx.gbl...
> First, why are you using 2 SqlConnections?
> Second, no there is no way to read the last commited version of a locked
> row. That is "snapshot isolation" and it's a new feature of SQL Server
> 2005. With SQL Server 2000 you must choose between reading the row after
> it's commited, or read the dirty row.
> David
>