TZConnection.AutoCommit

Summary

AutoCommit enables or disables AutoCommit mode.

Autocommit mode

In AutoCommit mode Zeos will do an implicit commit to the database after each write operation. For databases that do support auto commit on their own (Firebird, MS SQL, ...) Zeos will leave it to the database when to do a commit operation. Usually one would do that kind of thing, if you display some data to your user and any change should be committed to the database immediately. If you ocassionally need a transaction where you want to do multiple operations in one transaction and need to fully be in control of when to commit, you can start an explicit transaction by calling StartTransaction. In that case StartTransaction temporarily disables AutoCommit until you call Rollback or Commit. Either one of them will bring you back to the AutoCommit mode.

Beware: There are cases where Zeos will not be able to identify a write operation and possibly doesn't do a commit. That can happen if you have a stored procedure that changes data and also returns records. In that case your sql probably looks similar to this:

select * from someprocedure;

or

select * from someprocedure(:param1, :param2);

In some of these cases Zeos might think you are only doing a read and doesn't commit immediately afterwards.

Explicit transaction mode

If you always want to be in control when to commit, then AutoCommit mode probably isn't for you. In That case set AutoCommit to false. Zeos will start a transaction for you immediately after you connect to the database. Calling Commit or Rollback will end that transaction and immediately start a new transaction. Zeos will never do a commit or rollback on its own.

State Diagram

[Fr0sT.Brutal]

In that case StartTransaction temporarily disables AutoCommit until you call Rollback or Commit
Currently this acts quite weirdly... AutoCommit is disabled, but no transaction is started or finished.

procedure TZAbstractConnection.StartTransaction;
begin
  CheckAutoCommitMode;

  if FExplicitTransactionCounter = 0 then
    AutoCommit := False;
  DoStartTransaction;
  Inc(FExplicitTransactionCounter);
end;

Maybe there should be FConnection.StartTransaction line?

[marsupilami79]

Hello Fr0st,

TZAbstractConnection.StartTransaction is doing AutoCommit := False;. Imho this calls TZAbstractConnection.SetAutoCommit which in turn informs the driver about the change by calling FConnection.SetAutoCommit. For some drivers this is the hint to start an explicit transaction (PostgreSQL). For others it is the hint to stop their emulation of AutoCommit (Oracle, Firebird). I dislike Firebird being in the latter list but that is the current implementation.

[Fr0sT.Brutal]

Well, for FB it really does nothing... seem like forgotten override. As FB's internal autocommit is not used anyway

{EH: We do NOT handle the isc_tpb_autocommit of FB because we noticed a huge
performance drop especially for Batch executions. Note Zeos handles one Batch
Execution as one Update and loops until all batch array are send. FB with this
param commits after each "execute block" which definitally kills the idea and
the expected performance!}

I guess the override should be added with explicit transaction start.

[Fr0sT.Brutal]

 src/dbc/ZDbcInterbase6.pas | 12 ++++++++++++
 1 file changed, 12 insertions(+)

diff --git a/src/dbc/ZDbcInterbase6.pas b/src/dbc/ZDbcInterbase6.pas
index 991f03a..ca3dde1 100644
--- a/src/dbc/ZDbcInterbase6.pas
+++ b/src/dbc/ZDbcInterbase6.pas
@@ -127,6 +127,8 @@ type

     procedure SetReadOnly(Value: Boolean); override;

+    procedure SetAutoCommit(Value: Boolean); override;
+
     procedure Commit; override;
     procedure Rollback; override;

@@ -940,6 +942,16 @@ begin
   ReadOnly := Value;
 end;

+procedure TZInterbase6Connection.SetAutoCommit(Value: Boolean);
+begin
+  if AutoCommit = Value then Exit;
+  if not Closed and Value then
+    Commit;
+  inherited;
+  if not (AutoCommit or (GetTransactionIsolation = tiNone)) then
+    StartTransaction;
+end;
+
 { TZInterbase6CachedResolver }

 constructor TZInterbase6CachedResolver.Create(Statement: IZStatement; Metadata: IZResultSetMetadata);