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/libr...tionlevel.aspx

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

Marc

ms wrote:
> 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

Marc Gravell wrote:
> 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