Problem using non-autocommit mode

matejka · Post by **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

Post by **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 · Post by **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

Post by **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 · Post by **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 · Post by **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 · Post by **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 · Post by **trupka** » 19.05.2009, 20:46

Cigydd, which ZEOS version you use?

Post by **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