Timeout in Transaction: Get two times the same data from table ada

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my function I have to retrieve some data two times. The first time it runs
without any problem but on second call I get an timeout error.
I´m very confused about this behavior because following code runs on two of
our machines and timeout error raises only on one machine.

At the following lines I´ve postet same sample code, which produces this
error:

TransactionOptions o = new TransactionOptions();
o.IsolationLevel = System.Transactions.IsolationLevel.Serializable;

for (int i = 0; i < 10000; i++)
{
listBox1.Items.Insert(0, i.ToString());

using (TransactionScope scope = new
TransactionScope(TransactionScopeOption.Required, o))
{
GetData();
listBox1.Items.Insert(0, "1111");
Application.DoEvents();

GetData();
listBox1.Items.Insert(0, "2222");
Application.DoEvents();

scope.Complete();
}
}

private void GetData()
{
TypedDataSet ds = new TypedDataSet();
TypedDataSetTableAdapters.tblActivityTableAdapter adapter = new
TypedDataSetTableAdapters.tblActivityTableAdapter();
adapter.Fill(ds.tblActivity);

listBox1.Items.Clear();
foreach (TypedDataSet.tblActivityRow row in ds.tblActivity)
{
listBox1.Items.Add(row.strName);
}
label1.Text = ds.tblActivity.Rows.Count.ToString();
}


for (int i = 0; i < 10000; i++)
{
listBox1.Items.Insert(0, i.ToString());

using (TransactionScope scope = new
TransactionScope(TransactionScopeOption.Required, o))
{
GetData();
listBox1.Items.Insert(0, "1111");
Application.DoEvents();

GetData();
listBox1.Items.Insert(0, "2222");
Application.DoEvents();

scope.Complete();



Thanks.
 
Manuel,

Check out my article on SYstem.Transactions in code-magazine - that should
explain why the below is happening.

In short - due to connection pooling, the two requests for GetData() open
two connections. The physical connection is not closed even though your
SqlConnection is - and the first connection's isolation level is bumped to
Serializable, soon as conn#2 opens.

And then when you attempt reading the data - your second request gets
blocked.

How can you prevent this? - Maintain your own connection lifetime, rather
than have Sys.Tx do it for you. This is explained in further detail in my
recent code-magazine article.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
 
First, thanks a lot for your fast response.

For me, it´s not completely clear how to maintain my own connection lifetime.
What I´ve done so far, was to close connection manually after data was
retrieved but this doesn´t fix my problem.

Maybe you can post the important part of your code-magazine arcticle,
because I´v no subscription of it.

Thanks,
Manuel
 
I´ve testet a "light" version of your sample code (from code-magazine)
at one machine. With an timeout at the line "conn2.Open();"
At all other machines it runs without any problem.
So, do you know why I get an timeout at this machine?

---------------------------
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection conn1 = new
SqlConnection(Properties.Settings.Default.newTrustConnectionString))
{
conn1.Open();
conn1.Close();
}

using (SqlConnection conn2 = new
SqlConnection(Properties.Settings.Default.newTrustConnectionString))
{
conn2.Open();
conn2.Close();
}

scope.Complete();
}
 
I am afraid the code below is not per the recommendation I had given in code
magazine. You need to read the article in depth. :-(

Let me try and explain the problem differently.

You are calling GetData() twice in your using block .. right? In each
instance, GetData uses a TableAdapter to fill a DataTable. In doing so, it

a) Opens a connection
b) Reads the data
c) Closes the connection ---- OR SO IT THINKS !!!

The SqlConnection is closed, but .. well the physical database connection
isn't. it is kept open for transactional sanctity reasons. The underlying
connection pool, now maintains an open connection for you - because it's
still on an active transaction.

So when you call GetData AGAIN in the same TransactionScope, (assuming you
are on SQL2k5), your transaction will now promote from LTM to MSDTC.

This promotion will cause the isolation level to bump up to Serializable. As
a result, your Query#1 (GetData call #1) will end up placing an exclusive
lock on the table, and hence prevent the second GetData call from
executing - Thus the CommandTimeOut.

So how do you prevent this from happening? - Well - don't let the second
SqlConnection accessing the same resource enlist in the same TxScope.

How can you make this happen? - Extend the TableAdapter, by adding a partial
class. Specify your own connection, open it, (instead of GetData or
adapter.fill opening it for you), and then call GetData twice - and then
close the connection.

This way, your entire operation is limited to a single SqlConnection and a
single physical database connection - which prevents timeouts.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
 
After a long search on this error, the solution was just so simple!
Windows Firewall of client machine has blocked msdtc, so transactions wasn´t
possible.
But anyway thank´s a lot for your help!

Kind Regards,
Manuel
 
Back
Top