ManuelWagnerprogsign
Tue May 16 09:39:02 CDT 2006
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
"Sahil Malik [MVP C#]" wrote:
> 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
> ----------------------------------------------------------------------------
>
>
>
>
>
>
> "Manuel Wagner/progsign" <ManuelWagnerprogsign@discussions.microsoft.com>
> wrote in message news:DB48E4FB-B2DD-4408-923B-F733B09DFF8B@microsoft.com...
> > 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();
> > }
> >
> >
> >
> >
> >
> >
> >
> > --------------------------
> >
> > "Sahil Malik [MVP C#]" wrote:
> >
> >> Manuel,
> >>
> >> What that means is, you open the connection, and you close the
> >> connection,
> >> rather have GetData do that for you.
> >>
> >> HTH,
> >>
> >>
> >> --
> >> - Sahil Malik [MVP]
> >> ADO.NET 2.0 book -
> >>
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
> >> ----------------------------------------------------------------------------
> >>
> >>
> >> "Manuel Wagner/progsign" <ManuelWagnerprogsign@discussions.microsoft.com>
> >> wrote in message
> >> news:10A72CC1-68B7-4930-BAED-8E888309D5A1@microsoft.com...
> >> > 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
> >> >
> >> > "Sahil Malik [MVP C#]" wrote:
> >> >
> >> >> 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
> >> >> ----------------------------------------------------------------------------
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "Manuel Wagner/progsign" <Manuel
> >> >> Wagner/progsign@discussions.microsoft.com>
> >> >> wrote in message
> >> >> news:E238F886-0BAE-4E2F-B4DD-E3F748476B5F@microsoft.com...
> >> >> > 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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>