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

Transaction IsolationLevel problem

P: n/a
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.ReadUncommitt ed);

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!

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
Nov 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi Hardy,

Check IsolationLevel property after you opened transaction. Is it still
ReadUncommited after transaction was opened?
--
Val Mazur
Microsoft MVP

http://xport.mvps.org

"Hardy Wang" <ha*******@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...
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.ReadUncommitt ed);

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!

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy

Nov 17 '05 #2

P: n/a
Yes, I am sure isolation level is still ReadUncommitted.

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
"Val Mazur (MVP)" <gr******@hotmail.com> wrote in message
news:OG****************@TK2MSFTNGP12.phx.gbl...
Hi Hardy,

Check IsolationLevel property after you opened transaction. Is it still
ReadUncommited after transaction was opened?
--
Val Mazur
Microsoft MVP

http://xport.mvps.org

"Hardy Wang" <ha*******@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...
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.ReadUncommitt ed);

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!

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy


Nov 17 '05 #3

P: n/a
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" <ha*******@hotmail.com> wrote in message
news:up**************@TK2MSFTNGP14.phx.gbl...
Yes, I am sure isolation level is still ReadUncommitted.

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
"Val Mazur (MVP)" <gr******@hotmail.com> wrote in message
news:OG****************@TK2MSFTNGP12.phx.gbl...
Hi Hardy,

Check IsolationLevel property after you opened transaction. Is it still
ReadUncommited after transaction was opened?
--
Val Mazur
Microsoft MVP

http://xport.mvps.org

"Hardy Wang" <ha*******@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...
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.ReadUncommitt ed);

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!

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy



Nov 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.