Commits or rollbacks issued following each select

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I have an application that uses the direct provider for Oracle to
connect to an Oracle database server. While looking at Oracle trace
files, I discovered something that bothers me. The following
pseudo-code describes the problem.

string statement = "Select fielda, fieldb from tablea where fieldc =
:Param1";

DbCommand.CommandType = CommandType.Text;
DbCommand.CommandText = statement;
DbCommand.Connection = current connection;
DataAdapter.SelectCommand = DbCommand;
try
{
DataAdapter.Fill(dataTable);
}
etc...

This is a simple select on a non-unique field that returns zero or more
rows. It works fine. However, when I look at the Oracle trace files,
after the last fetch, I see:
XCTEND rlbk=1, rd_only=1

Sometimes rlbk = 0, other times it is as above. This is telling Oracle
do do a commit or rollback on the read. I am not in a transaction. This
appears to happen with all reads, none of which were in an explicit
transaction.

I have seen references to this behavior elsewhere, but have found no
way to disable it. I tried both the MS and Oracle data providers, with
no apparent change in behavior. It's wasting time on the app server,
network and database server. Can anyone tell me how to disable this
"feature"?

Thanks in advance,

Jim Brandley
 
Jim said:
I have an application that uses the direct provider for Oracle to
connect to an Oracle database server. While looking at Oracle trace
files, I discovered something that bothers me. The following
pseudo-code describes the problem.

string statement = "Select fielda, fieldb from tablea where fieldc =
:Param1";

DbCommand.CommandType = CommandType.Text;
DbCommand.CommandText = statement;
DbCommand.Connection = current connection;
DataAdapter.SelectCommand = DbCommand;
try
{
DataAdapter.Fill(dataTable);
}
etc...

This is a simple select on a non-unique field that returns zero or more
rows. It works fine. However, when I look at the Oracle trace files,
after the last fetch, I see:
XCTEND rlbk=1, rd_only=1

Sometimes rlbk = 0, other times it is as above. This is telling Oracle
do do a commit or rollback on the read. I am not in a transaction. This
appears to happen with all reads, none of which were in an explicit
transaction.

I have seen references to this behavior elsewhere, but have found no
way to disable it. I tried both the MS and Oracle data providers, with
no apparent change in behavior. It's wasting time on the app server,
network and database server. Can anyone tell me how to disable this
"feature"?
Click to expand...

In Oracle you are always in a transaction. Autocommit mode is emulated by
issuing a commit after each statement.

David
 
Jim said:
I really do not want autocommit. Is there a way to turn it off?
Click to expand...

Just use explicit transactions (or System.Transactions in ADO.NET 2.0) in
your ADO.NET code. All ADO.NET data providers default to autocommit mode.

David
 
Back
Top