Problem using non-autocommit mode

Forum related to version 6.5.1 (alpha) and 6.6.x (beta) of ZeosLib's DBOs

Moderators: gto, cipto_kh, EgonHugeist

matejka
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 22.11.2005, 14:32
Location: Prague, Czech Republic
Contact:

Problem using non-autocommit mode

Postby matejka » 13.02.2007, 11:58

I've installed Zeos DBO 6.6.0 beta, connecting to MySQL 5.0.27, Delphi6 Professional. I have a following problem with ZConnection.AutoCommitMode:

When I set ZConnection.AutoCommit to False, then while calling TZConnection.StartTransaction it always raises an exception "Invalid operation in non AutoCommit mode".

I think there is a bug in TZConnection.CheckAutoCommitMode. There is a condition
if not FAutoCommit and (FExplicitTransactionCounter = 0) then
raise...

I guess that FExplicitTransactionCounter holds number of transactions that have been started programatically. So whe using non-autocommit mode it's impossible to call StartTransaction because FExplicitTransactionCounter is 0.

I changed then condition to:
if not FAutoCommit and (FExplicitTransactionCounter > 0) then
raise EZDatabaseError.Create(SInvalidOpInNonAutoCommit);

The exception is raised when some transaction is being used... It works properly in both autocommit and non-autocommit mode, but I'm not sure if I understood the meaning of FExplicitTransactionCounter correctly.

Does anyone have similar experience?

thanks

Jiri Matejka

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 13.02.2007, 13:12

Hi Jiri,

You don't need to use StartTransaction when in non-autocommit mode. StartTransaction is only intended to allow you to switch to non-autocommit behaviour until the next commit.
I agree it may not be the most intuitive interpretation, but it is what I concluded studying the coding (for mysql, not 100% sure about other db implementations).
Maybe a quick search on the forums can give you more info. You're not the first one to notice this behaviour.

Mark

matejka
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 22.11.2005, 14:32
Location: Prague, Czech Republic
Contact:

Postby matejka » 13.02.2007, 15:20

Hi Mark,
thanks for reply. So how can I start transaction manually without StartTransaction? Does it mean that when I call ZConnection.Commit or Rollback then it starts new transaction automatically?

I use StartTransaction a lot in my code, mainly in data update or insert blocks... start transaction ... do some updates ... commit / rollback transaction. So it would be time-consuming for me to change it. Are my changes in code in my first reply correct? Can I use it?

thanks

Jiri

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 13.02.2007, 16:36

As far as I know a new transaction is started when you commit/rollback. I can't guarantee everything will work correctly when you use it like that.
I think basically your change will work well as long as you database does not need a specific statement to start/end a transaction. (Mysql and oracle do not need it, connect/commit/rollback do all transaction handling)

Mark

matejka
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 22.11.2005, 14:32
Location: Prague, Czech Republic
Contact:

Postby matejka » 13.02.2007, 16:45

Thanks, so do you thing the best way (in terms of speed and stability) is to set autocommit to true and use StartTransaction (with automatic switching to autocommit=false) only for statements that require using of transactions?

thanks

Jiri

btrewern
Expert Boarder
Expert Boarder
Posts: 193
Joined: 06.10.2005, 18:51

Postby btrewern » 13.02.2007, 17:02

I've never understood why Zeos does transactions the way it does. Currently I have to call BEGIN, COMMIT & ROLLBACK manually using SQL calls.

How does dbExpress or the BDE deal with this?

Regards,

Ben

Cigydd
Fresh Boarder
Fresh Boarder
Posts: 11
Joined: 15.06.2008, 22:54
Location: Plzen, Czechia, Europe
Contact:

Postby Cigydd » 19.05.2009, 15:00

I have a simple question.
How do I get to and start a transaction in non-autocommit mode without an error?
(AutoCommit := False; StartTransaction => "Cannot start a transaction in autocommit mode")

trupka
Expert Boarder
Expert Boarder
Posts: 140
Joined: 26.08.2007, 22:10

Postby trupka » 19.05.2009, 20:46

Cigydd, which ZEOS version you use?

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 19.05.2009, 21:21

I'm not 100% sure if it WORKS, but... Just set autocommit to false. Then a transaction should automatically start. This is the way the non-autocommit mode is supposed to work. StartTransaction is ONLY used in autocommit mode when you want to do an execptional transaction without commit after every single statement.

Matk
Image


Return to “6.5.1 (alpha) - 6.6.x (beta)”

Who is online

Users browsing this forum: No registered users and 1 guest