A ADO.net Transaction question

  • Thread starter Thread starter ms
  • Start date Start date
M

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
 
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
 
ms said:
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?
Click to expand...

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
 
Marc said:
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.
Click to expand...

Technically speaking it is a dirty read he was expecting.

Arne
 
Back
Top