RE: Transaction Error on None-Transaction Query

[Jialiang Ge [MSFT]]

Hello Peter,

I once came across the same error "ExecuteReader requires the command to
have a transaction when the connection assigned? with running two threads
talking with the database. There was no problem if I use single thread or
two separate processes. These two threads are using the same connection
string, but with different connection instances. Peter, when you get the
error message, would you please check the thread list of the process
(Debug->Window->Threads)? Is there the second thread running this piece of
code?

In the meantime, I am trying the reproduce the symptom on my side. However,
I'm stuck by these lines of code:

try
{
myTrans =
connection.BeginTransaction(IsolationLevel.ReadUnc ommitted);
myTrans.Commit();
}
catch { }

Why do you commit the transaction immediately after it's created? What if
we commit the transaction after da.Fill(ds)?

Regards,
Jialiang Ge (ji****@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
ms****@microsoft.com.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[Peter]

Yes there are other threads running this piece of code. I think that's the
problem because if I run it on my machine where there is only one thread
running I never get this error.

Here's simplified pseudo code:

1) Create database connection
2) Open connection
3) Read database
4) Create a thread for each record read from the database and pass the same
connection object into each Thread
Inside the thread:
Run a Crystal Reports (Crystal Reports creates its own
connection)
Email a message
Update database
5) Sleep
6) Go to step #3
I have the transaction commited immediately after it's created to allow
'dirty reads' or uncommited reads, if I don't then I get record lock errors.
""Jialiang Ge [MSFT]"" <ji****@online.microsoft.comwrote in message
news:TN**************@TK2MSFTNGHUB02.phx.gbl...

Hello Peter,

I once came across the same error "ExecuteReader requires the command to
have a transaction when the connection assigned? with running two threads
talking with the database. There was no problem if I use single thread or
two separate processes. These two threads are using the same connection
string, but with different connection instances. Peter, when you get the
error message, would you please check the thread list of the process
(Debug->Window->Threads)? Is there the second thread running this piece of
code?

In the meantime, I am trying the reproduce the symptom on my side.
However,
I'm stuck by these lines of code:

try
{
myTrans =
connection.BeginTransaction(IsolationLevel.ReadUnc ommitted);
myTrans.Commit();
}
catch { }

Why do you commit the transaction immediately after it's created? What if
we commit the transaction after da.Fill(ds)?

Regards,
Jialiang Ge (ji****@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
ms****@microsoft.com.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

[Jialiang Ge [MSFT]]

Hello Peter,

A quick resolution of the problem can be to have a lock around this piece
of code so that threads will not run it simultaneously. For example:
http://msdn.microsoft.com/en-us/libr...cz(VS.71).aspx

I'm still researching the reason of the error when multiple threads run it
concurrently. I will be back as soon as possible.

Regards,
Jialiang Ge (ji****@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
ms****@microsoft.com.

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

[Jialiang Ge [MSFT]]

Hello Peter,

A quick resolution of the problem can be to have a lock around this piece
of code so that threads will not run it simultaneously. For example:
http://msdn.microsoft.com/en-us/libr...cz(VS.71).aspx

I'm still researching the reason of the error when multiple threads run it
concurrently. I will be back as soon as possible.

Regards,
Jialiang Ge (ji****@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
ms****@microsoft.com.

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Peter wrote:

Yes there are other threads running this piece of code. I think that's the
problem because if I run it on my machine where there is only one thread
running I never get this error.

Here's simplified pseudo code:

1) Create database connection
2) Open connection
3) Read database
4) Create a thread for each record read from the database and pass the same
connection object into each Thread
Inside the thread:
Run a Crystal Reports (Crystal Reports creates its own
connection)
Email a message
Update database
5) Sleep
6) Go to step #3

Using same connection for multiple threads sounds as
wrong design.

Arne

[Peter]

Openning and clossing connections is very expensive specially if you have
hundreds of records to process, am I supposed to open and close the same db
connection for each record? - that sounds silly to me. Plus I was asked by
our DBA to limit number of connections. If I can't share the connection
between threads than I will be foreced to Open and Close connection, but
until than I'll keep looking for solution.

(I am not saying there is nothing wrog with my design, but the same code
works with fine with the Informix database, of course the program might have
the same problems with Informix, I just never got the same error)
"Arne Vajhøj" <ar**@vajhoej.dkwrote in message
news:48***********************@news.sunsite.dk...

Peter wrote:

>Yes there are other threads running this piece of code. I think that's
the problem because if I run it on my machine where there is only one
thread running I never get this error.

Here's simplified pseudo code:

1) Create database connection
2) Open connection
3) Read database
4) Create a thread for each record read from the database and pass the
same connection object into each Thread
Inside the thread:
Run a Crystal Reports (Crystal Reports creates its own
connection)
Email a message
Update database
5) Sleep
6) Go to step #3

Using same connection for multiple threads sounds as
wrong design.

Arne

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Peter wrote:

"Arne Vajhøj" <ar**@vajhoej.dkwrote in message
news:48***********************@news.sunsite.dk...

>Peter wrote:

>>Yes there are other threads running this piece of code. I think that's
the problem because if I run it on my machine where there is only one
thread running I never get this error.

Here's simplified pseudo code:

1) Create database connection
2) Open connection
3) Read database
4) Create a thread for each record read from the database and pass the
same connection object into each Thread
Inside the thread:
Run a Crystal Reports (Crystal Reports creates its own
connection)
Email a message
Update database
5) Sleep
6) Go to step #3

Using same connection for multiple threads sounds as
wrong design.

Openning and clossing connections is very expensive specially if you

have

hundreds of records to process, am I supposed to open and close the

same db

connection for each record? - that sounds silly to me. Plus I was

asked by

our DBA to limit number of connections. If I can't share the connection
between threads than I will be foreced to Open and Close connection, but
until than I'll keep looking for solution.

..NET uses connection pooling by default.

You app will will get maybe 100 physical connection to the
database.

And then the open in your code will get a connection from the
pool and close/dispose will put it back in the pool.

That is two very fast operations.

And indeed you will get better scalability by opening
as late as possible and close as early as possible.

Arne

[Peter]

Unfortunatly you can not run Crystal Reports and IBM.DB2.NET provider from
the same program and that's what I have.
IBM.DB2.NET is using the same names for their functions as the unmanaged
code libraries do and Crystal Reports is using the unmaged code libraries.
So who ever is first wins - if you call and function in .NET provider then
Crystal Report will not work and if you call your Crystal Report before any
IBM.DB2.NET functions then your .NET program will not work. This problem
surfaced in the first version of .NET provider and it was supposed to be
fixed in the next version, but I guess it was not.
"Arne Vajhøj" <ar**@vajhoej.dkwrote in message
news:48***********************@news.sunsite.dk...

Peter wrote:

>"Arne Vajhøj" <ar**@vajhoej.dkwrote in message
news:48***********************@news.sunsite.dk. ..

>>Peter wrote:
Yes there are other threads running this piece of code. I think that's
the problem because if I run it on my machine where there is only one
thread running I never get this error.

Here's simplified pseudo code:

1) Create database connection
2) Open connection
3) Read database
4) Create a thread for each record read from the database and pass the
same connection object into each Thread
Inside the thread:
Run a Crystal Reports (Crystal Reports creates its own
connection)
Email a message
Update database
5) Sleep
6) Go to step #3
Using same connection for multiple threads sounds as
wrong design.

Openning and clossing connections is very expensive specially if you

have

hundreds of records to process, am I supposed to open and close the

same db

connection for each record? - that sounds silly to me. Plus I was

asked by

our DBA to limit number of connections. If I can't share the connection
between threads than I will be foreced to Open and Close connection, but
until than I'll keep looking for solution.

.NET uses connection pooling by default.

You app will will get maybe 100 physical connection to the
database.

And then the open in your code will get a connection from the
pool and close/dispose will put it back in the pool.

That is two very fast operations.

And indeed you will get better scalability by opening
as late as possible and close as early as possible.

Arne

[Jialiang Ge [MSFT]]

To supplement my last message, please note that the connection objects are
not thread-safe, we need to use separate connection objects if you need to
use connections from more than one thread concurrently.

Regards,
Jialiang Ge (ji****@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
ms****@microsoft.com.

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

[Peter]

So I am stuck either re-writing the application and spanning a new process
for each Crystal Report so I can use DB2.NET or opening and closing
connection for each record.
bummer.

I have changed the program to open and close connection for each record and
the errors disappeared. Of course this is not very efficient since I am
using ODBC and I don't think it provides the database pooling, but this will
do until I can redesign the program.

Thank You everyone for your help!

""Jialiang Ge [MSFT]"" <ji****@online.microsoft.comwrote in message
news:gW****************@TK2MSFTNGHUB02.phx.gbl...

To supplement my last message, please note that the connection objects are
not thread-safe, we need to use separate connection objects if you need to
use connections from more than one thread concurrently.

Regards,
Jialiang Ge (ji****@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
ms****@microsoft.com.

This posting is provided "AS IS" with no warranties, and confers no
rights.
=================================================

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Peter wrote:

"Arne Vajhøj" <ar**@vajhoej.dkwrote in message
news:48***********************@news.sunsite.dk...

>Peter wrote:

>>"Arne Vajhøj" <ar**@vajhoej.dkwrote in message
news:48***********************@news.sunsite.dk.. .
Peter wrote:
Yes there are other threads running this piece of code. I think that's
the problem because if I run it on my machine where there is only one
thread running I never get this error.
>
Here's simplified pseudo code:
>
1) Create database connection
2) Open connection
3) Read database
4) Create a thread for each record read from the database and pass the
same connection object into each Thread
Inside the thread:
Run a Crystal Reports (Crystal Reports creates its own
connection)
Email a message
Update database
5) Sleep
6) Go to step #3
Using same connection for multiple threads sounds as
wrong design.
Openning and clossing connections is very expensive specially if you

have

>>hundreds of records to process, am I supposed to open and close the

same db

>>connection for each record? - that sounds silly to me. Plus I was

asked by

>>our DBA to limit number of connections. If I can't share the connection
between threads than I will be foreced to Open and Close connection, but
until than I'll keep looking for solution.

.NET uses connection pooling by default.

You app will will get maybe 100 physical connection to the
database.

And then the open in your code will get a connection from the
pool and close/dispose will put it back in the pool.

That is two very fast operations.

And indeed you will get better scalability by opening
as late as possible and close as early as possible.

Unfortunatly you can not run Crystal Reports and IBM.DB2.NET provider

from

the same program and that's what I have.
IBM.DB2.NET is using the same names for their functions as the unmanaged
code libraries do and Crystal Reports is using the unmaged code

libraries.

So who ever is first wins - if you call and function in .NET provider

then

Crystal Report will not work and if you call your Crystal Report

before any

IBM.DB2.NET functions then your .NET program will not work. This problem
surfaced in the first version of .NET provider and it was supposed to be
fixed in the next version, but I guess it was not.

That I do not know anything about.

But I can not see that it relates to when to open and close
connections in a program.

No matter whether you can use CR or not in thar program then
open late and close early is good.

Arne

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Peter wrote:

I have changed the program to open and close connection for each record and
the errors disappeared. Of course this is not very efficient since I am
using ODBC and I don't think it provides the database pooling, but this will
do until I can redesign the program.

ODBC in .NET uses connection pooling.

http://msdn.microsoft.com/en-us/libr...onnection.aspx

<quote>
Note:

To deploy high-performance applications, you frequently must use
connection pooling. However, when you use the .NET Framework Data
Provider for ODBC, you do not have to enable connection pooling because
the provider manages this automatically.
</quote>

Arne