Transaction IsolationLevel problem

[Hardy Wang]

Hi all,
I have an application, which requires several SQL activities, so I wrap
them in a transaction:
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadUncommitted);

I use MS Application Block for Data Access to handle all SQL statements:
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sql);

I have following 2 problems:
1) When I debug this application from my local machine, in the middle of
this transaction, I cannot use my Sql Query Analyzer to do anything against
some tables used in this transaction, even though I set transaction's
isolation level to read uncommitted. Until I finish my debugging, result
will return from query analyzer.

2) I have a common class, and it contains a function shared by some other
functions,
public bool Audit(SqlTransaction tran) {
string sql = "insert into AuditTable ....";
try {
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sql);
return true;
} catch (Exception ex) {
return false;
}
}
Every other function passes its own transaction object to this function.
I frequently find dead lock caused by sql statement in this function. Is
there any limitation to use transaction object across classes? The other
funny thing is, the audit table is used to be inserted only, no other piece
of code will read/write to this table, so how comes this relative
independent table will cause dead lock?

Welcome to any comment.

Thanks!

 

[Val Mazur \(MVP\)]

Hi Hardy,

Check IsolationLevel property after you opened transaction. Is it still
ReadUncommited after transaction was opened?

 

[Hardy Wang]

Yes, I am sure isolation level is still ReadUncommitted.

 

[Val Mazur \(MVP\)]

Then it looks like a bug. I will try to check if this the same on my PC

--
Val Mazur
Microsoft MVP

http://xport.mvps.org

 

[Hardy Wang]

Any found?