By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,419 Members | 1,432 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,419 IT Pros & Developers. It's quick & easy.

A ADO.net Transaction question

P: n/a
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
Jul 3 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Jul 3 '08 #2

P: n/a
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
Jul 3 '08 #3

P: n/a
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
Jul 3 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.