A ADO.net Transaction question

[ms]

Hi,
I used SqlTransaction to insert datas to table A in my program ,but I
find that before I commit the transaction ,I even can not use query analyzer
to query data in table A, could anybody tell me why and is it normal or not?

Thanks
-ja

 

[Marc Gravell]

the transaction carries an "isolation level", which can extend the
duration of locks into the lifetime of the transaction (rather than
just the duration of the individual DML instruction). Essentially,
your transaction has a write-lock on all-or-some of the data in that
table; until the transaction is complete, other spids are (by default)
prevented from even *seeing* the uncertain changes (they could be
rolled back) to prevent phantom/non-repeatable reads.

The caller can blitz through this by adding the (NOLOCK) hint to the
table in the query, but that risks data issues if used
inappropriately.

The exact behavior depends on the exact isolation level - the most
exacting being "serializable".

In SQL server terms, the isolation levels are:
http://msdn.microsoft.com/en-us/library/ms173763.aspx

And the reference from .NET:
http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx

Not quite a 1:1 mapping, but close enough.

Marc

 

[Arne Vajhøj]

I used SqlTransaction to insert datas to table A in my program ,but I
find that before I commit the transaction ,I even can not use query analyzer
to query data in table A, could anybody tell me why and is it normal or not?

To simplify Marc's explanation: unless you are using transaction
isolation level read uncommitted (which is not the default in SQLServer)
then this is expected behavior.

Arne

 

[Arne Vajhøj]

the transaction carries an "isolation level", which can extend the
duration of locks into the lifetime of the transaction (rather than
just the duration of the individual DML instruction). Essentially,
your transaction has a write-lock on all-or-some of the data in that
table; until the transaction is complete, other spids are (by default)
prevented from even *seeing* the uncertain changes (they could be
rolled back) to prevent phantom/non-repeatable reads.

Technically speaking it is a dirty read he was expecting.

Arne