Getting out of transaction if exception occurred

In this forum we will discuss things relating the ZEOSLib 6.6.x stable versions

Moderators: gto, EgonHugeist

gmb
Fresh Boarder
Fresh Boarder
Posts: 15
Joined: 19.05.2008, 14:05

Getting out of transaction if exception occurred

Postby gmb » 25.08.2011, 06:51

Hi there
Using Zeos 6.6.6-stable with Delphi6 on Postgres 8.3
I'm having difficulty implementing transactions in my application in the way that I feel will be most practical.
My app is running lengthy DB processes which I'd like to put in transactions; I've implemented it like this:

ExecSQL( 'begin;' ); //(function executing scripts using TZSQLProcessor)
try
//...lengthy process start
ExecSQL(.... )
//...lengthy process ends
ExecSQL('commit');
except
ExecSQL('rollback');
raise; // exception should occur
end;

My requirement is this when an exception occurs:
1 ) the exception (normally EZSQLException) should be raised
2 ) all DB changes made in this transaction should be rolled back
3) the DB session should be able to continue processing transactions

My problem is that no matter how I try to tweak the code above, when a EZSQLException occurs,
the system excepts with the standard:
"SQL Error: ERROR: current transaction is aborted, commands ignored until end of transaction block."
and the connection to the DB should be restarted.

Is there any way in which I can get past this?
Thanks for any help

Wild_Pointer
Expert Boarder
Expert Boarder
Posts: 159
Joined: 18.03.2008, 13:03
Contact:

Postby Wild_Pointer » 25.08.2011, 12:32

Hello gmb,

have you turned AutoCommit off and set TransactIsolationLevel to ilNone ?

In my projects I use the same approach (managing transactions manually). The difference is I use 'START TRANSACTION ISOLATION LEVEL READ COMMITTED;' to start the transaction.

Good luck!

gmb
Fresh Boarder
Fresh Boarder
Posts: 15
Joined: 19.05.2008, 14:05

Postby gmb » 30.08.2011, 23:04

Thanks for the response, Wild_Pointer (and sorry for my late reply)

I've read some articles about AutoCommit, but cannot quite understand what effect it will have on my code.
As far as I understand, setting this to TRUE will mean that each statement is committed automatically, which negates my goal of running statements in transactions... possibly I'm not understanding it correctly.

Anyway, I'll try your suggestion re IsolationLevel.
Is it possible for you to give a brief explanation of what I'll be doing by setting this?

Regards

Wild_Pointer
Expert Boarder
Expert Boarder
Posts: 159
Joined: 18.03.2008, 13:03
Contact:

Postby Wild_Pointer » 31.08.2011, 19:54

hello gmb,

You are right - AutoCommit generates transaction for every statement, so transaction control is not possible. You should turn it off. You can add SQL monitor component to see what is being done.

Setting isolation level to "Read committed" ensures that the changes made in this transaction are visible in it only until it is committed. Please see http://www.postgresql.org/docs/8.1/static/transaction-iso.html It will explain the subject better than me :)

Good luck!

User avatar
ribut
Fresh Boarder
Fresh Boarder
Posts: 10
Joined: 03.02.2017, 08:30

Re: Getting out of transaction if exception occurred

Postby ribut » 02.04.2017, 06:49

When I use zeoslib-7.2-r3929-testing together with the PostgreSQL database when using the code block

try
  BEGIN
except
ROLLBACK
  end;
COMMIT

rollback implemented and no problems error

When I use zeoslib-7.2-r3986-testing

try
  BEGIN
except
ROLLBACK
  end;
COMMIT

I get an error: commands ignored until end of transaction block

marsupilami
Gold Boarder
Gold Boarder
Posts: 578
Joined: 17.01.2011, 14:17

Re: Getting out of transaction if exception occurred

Postby marsupilami » 03.04.2017, 15:42

This is a copy of viewtopic.php?f=20&t=67078. Since it is old, I will lock it.

Jan


Return to “6.6 - stable”

Who is online

Users browsing this forum: No registered users and 1 guest