There is no active transaction

martin · Post by **martin** » Wed 01 Jul 2009 13:06

Hi !

We are randomly getting "There is no active transaction". What can cause this problem ?

dbexpress for sql server, latest version.
Delphi 6.02
SQL Server 2008 32bit
Windows 2003 32bit

Any help greatly appreciated.

Thanks
Martin

martin · Post by **martin** » Thu 02 Jul 2009 11:56

DevArt help please ?

Dimon · Post by **Dimon** » Thu 02 Jul 2009 15:39

I can not reproduce the problem.
Please give a more detailed description of the problem, or try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com.

martin · Post by **martin** » Thu 02 Jul 2009 18:56

It's a 700K Lines Project and we cannot find any pattern when the errors occur so far.

I'm wondering where this error message is coming from, maybe you can help. Is it generated by the vcl layer, the driver, the sql server ?

Can this happen, when a connection is ungracefully closed/lost between the calls to StartTransaction and CommitTransaction ?

Dimon · Post by **Dimon** » Fri 03 Jul 2009 10:09

It seems that this error is raised by SQL Server. Try finding out if you use the save transaction operation without start transaction.

martin · Post by **martin** » Sat 04 Jul 2009 10:55

After a lot of thinking, I guess I have found the source of the "There is no active transaction" problem

Here is some Pseudo Code to explain:

Code: Select all

Begin Transaction
try
   Exec SQL Code
   Exec SQL Code
   Exec SQL Code <------------- Deadlock in Server
   Commit Transaction
except
  on Exception do
  begin
    Rollback Transaction  <------------- "There is no active transaction"
  end
end

What happens ? According to Technet, SQL Server does implicitly rollback the active transaction when choosing the statement as deadlock victim. So when the exception handler is called, the transaction is already cancelled and rolled back in sql server, but the dbexpress driver is unaware of that. So the call to rollback the transaction fails with "There is no active transaction", beause the server does not have an active transaction anymore.

According to Technet, sql server returns error code 1205 when he chooses a statement as deadlock victim.

Is there something you could do to solve this problem on the driver level ?

martin · Post by **martin** » Thu 09 Jul 2009 21:10

To whom it may concern,

We are a paying customer of DevArt (formerly CoreLAb) since years and we are therefore expecting a certain level of support.

This is an urgent problem for us (as are the other two issues currenlty open see forum). We are using dbexpress drivers for real world applications not just for fun.

If DevArt is unable or unwilling to solve the problem, please drop a message saying that so we don't have to poll the forum every hour to see if you have kindly posted a solution or answer.

If there is another support channel that we should use, let us know.

Thanks
Martin

Dimon · Post by **Dimon** » Tue 21 Jul 2009 07:15

martin wrote:Is there something you could do to solve this problem on the driver level ?

We can't solve this problem on the driver level. You should process the raised exception and rollback transaction depending on the error code.