Transaction Error on None-Transaction Query

[Peter]

I am using DB2 database and I am getting transaction error. I am using
transactions only to get Uncommited records so why am I getting this error
specially on a SELECT statement? This only occurs intermittently and if I do
a re-try right after the error occured the process works normally.

Thank You


Peter


Here's my code:

public static DataSet ExecuteDataset(OdbcConnection connection, CommandType
commandType, string commandText, int timeOut, params OdbcParameter[]
commandParameters)
{
OdbcTransaction myTrans = null;

OdbcCommand cmd = new OdbcCommand();

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

PrepareCommand(cmd, connection, myTrans, commandType,
commandText, timeOut, commandParameters);

OdbcDataAdapter da = new OdbcDataAdapter(cmd);
da.SelectCommand.Transaction = myTrans;

DataSet ds = new DataSet();

try
{
da.Fill(ds); // <---------------- this is where the error
occurs
}
catch (Exception e)
{
throw e;
}
finally
{
myTrans = null;
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}

return ds;
}

Here's the SQL:

SELECT TEMPLATENAME, KEYFIELDNAME, REPORTTITLE, AC10, EMAIL_TO_CUST,
FAX_TO_CUST, INCLUDE_BARCODE, BARCODE_SIZE, CUSTOMER_NUMBER, SHIPTO_NUMBER,
ATTACHMENT FROM PRINT.TEMPLATES AS TEMPLATES WHERE TEMPLATES.DOCTYPE = ? AND
TEMPLATES.AC10 = ? AND ((CUSTOMER_NUMBER=? AND SHIPTO_NUMBER=?) OR
(CUSTOMER_NUMBER=? AND SHIPTO_NUMBER=?)) ORDER BY CUSTOMER_NUMBER DESC


Here's the error:

System.InvalidOperationException: ExecuteReader requires the command to have
a transaction when the connection assigned to the command is in a pending
local transaction. The Transaction property of the command has not been
initialized.
at System.Data.Odbc.OdbcConnection.SetStateExecuting(String method,
OdbcTransaction transaction)
at System.Data.Odbc.OdbcCommand.ValidateConnectionAndTransaction(String
method)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
behavior, String method, Boolean needReader, Object[] methodArguments,
SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior
behavior)
at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

 

[Arne Vajhøj]

I am using DB2 database and I am getting transaction error. I am using
transactions only to get Uncommited records so why am I getting this error
specially on a SELECT statement? This only occurs intermittently and if I do
a re-try right after the error occured the process works normally.

OdbcTransaction myTrans = null;
OdbcCommand cmd = new OdbcCommand();
try
{
myTrans =
connection.BeginTransaction(IsolationLevel.ReadUncommitted);
myTrans.Commit();
}
catch { }

PrepareCommand(cmd, connection, myTrans, commandType,
commandText, timeOut, commandParameters);

OdbcDataAdapter da = new OdbcDataAdapter(cmd);
da.SelectCommand.Transaction = myTrans;

DataSet ds = new DataSet();

try
{
da.Fill(ds); // <---------------- this is where the error
occurs
}

Here's the SQL:

SELECT TEMPLATENAME, KEYFIELDNAME, REPORTTITLE, AC10, EMAIL_TO_CUST,
FAX_TO_CUST, INCLUDE_BARCODE, BARCODE_SIZE, CUSTOMER_NUMBER, SHIPTO_NUMBER,
ATTACHMENT FROM PRINT.TEMPLATES AS TEMPLATES WHERE TEMPLATES.DOCTYPE = ? AND
TEMPLATES.AC10 = ? AND ((CUSTOMER_NUMBER=? AND SHIPTO_NUMBER=?) OR
(CUSTOMER_NUMBER=? AND SHIPTO_NUMBER=?)) ORDER BY CUSTOMER_NUMBER DESC


Here's the error:

System.InvalidOperationException: ExecuteReader requires the command to have
a transaction when the connection assigned to the command is in a pending
local transaction. The Transaction property of the command has not been
initialized.

1) If the connection has a transaction associated then the command
need it too - also for SELECT !

cmd.Transaction = myTrans;

2) Why use ODBC and not the DB2 ADO.NET provider ??

Arne

 

[Peter]

Too many problems with DB2 ADO.NET and too many workstations to update.


The PrepareCommand is setting the transaction so I m seting cmd.Transaction
= myTrans;



private static void PrepareCommand(OdbcCommand command, OdbcConnection
connection, OdbcTransaction transaction, CommandType commandType, string
commandText, int timeOut, OdbcParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
}

//associate the connection with the command
command.CommandTimeout = timeOut;
command.Connection = connection;

//set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;

//if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
}

//set the command type
command.CommandType = commandType;

//attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}

return;
}

 

[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.ReadUncommitted);
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 ([email protected], 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:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/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.

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.ReadUncommitted);
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 ([email protected], 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:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/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/library/c5kehkcz(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 ([email protected], 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:
(e-mail address removed).

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/library/c5kehkcz(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 ([email protected], 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:
(e-mail address removed).

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

 

[Arne Vajhøj]

Too many problems with DB2 ADO.NET and too many workstations to update.

The DB2 ADO.NET provider should be XCOPY deployment. Isn't it ?

Arne

 

[Arne Vajhøj]

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)

 

[Peter]

No it's not, there's a full setup from IBM which includes unmanaged
libraries.

 

[Arne Vajhøj]

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.

 

[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 ([email protected], 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:
(e-mail address removed).

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!

 

[Arne Vajhøj]

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

 

[Arne Vajhøj]

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/library/system.data.odbc.odbcconnection.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