Nested transactions> Do I understand it correctly?

[Enrico]

" Proper support of nested transactions means that you may commit or rollback an inner or nested transaction without affecting the outer transaction's ability to commit or rollback.".
Does this mean that I can have something like this, and still ensure that in the end either is all committed or nothing is? (I omit the RETRY stuff for simplicity of the example)
And if this pseudo-code is correct, if another user is waitng to access the affected tableS, right after the inner commit would he be allowed to start his transaction?

procedure do_long_update();*
var
  i: integer;
begin

  for i:=1 t0 100000 do begin
    starttransaction;
    try
      for n:=1 to i+110000 do
          update_it();
      commit;
    except
      raise;
    end;
  end;

end;

procedure main;
begin
  starttransaction
  try*
    do_long_update();*
    commit();
  except* *
     on E:Sysutils.Exception do begin
         rollback;
        show(e.message)
     end;

  end;
end;

[Enrico]

Sorry. This seems to make a little bit more sense?

procedure do_long_update();*
var
  i: integer;
begin

  for i:=1 t0 100000 do begin
    if i=1 then
       starttransaction;
    try
      if 1000000 mod 10000 =0 do
          update_it();
      commit;
      starttransaction
    except
      rollback;
      raise;
    end;
  end;
  if inTransaction then
   commit;

end;

> Does this mean that I can have something like this, and still ensure
> that in the end either is all committed or nothing is? (I omit the
> RETRY stuff for simplicity of the example)

That's essentially what the quoted text says. The outermost (first
started / "parent" transaction if you like) in the end controls if
everything is either committed or rolled back.

> And if this pseudo-code is correct, if another user is waitng to
> access the affected tableS, right after the inner commit would he be
> allowed to start his transaction?

If the table(s) in the inner transaction are not also referenced in the
outer transaction, then I think the answer would be yes. Thorsten?



--
Eivind Bakkestuen [NDD]

[Enrico]

Thanks Eivind.
In my example, the outer transaction does "nothing" except ensuring that the inner transaction is committed or rolled back.

Between Starttransacion/Commit/Rollback in the outer code there is no touching of the tables at all.
My intent is to avoid "freezing" the second user for too long: to that purpose I spawn the update to the inner code where commit() startransaction() gives the second user a chance to move on.
I have not seen Thorsten's answer to your question.
Can I assume my "logic" is correct?
Thanks.

> My intent is to avoid "freezing" the second user for too long: to that
> purpose I spawn the update to the inner code where commit()
> startransaction() gives the second user a chance to move on.

Actually, thinking about it, my previous answer was likely wrong. In
order to preserve transaction integrity for the outer transaction,
table locks in the inner transaction must propagate to the outer, and
have to stay in place for the outer transaction's duration. Otherwise,
integrity would not be guaranteed if other clients could update tables
while the outer transaction was still active.

So, nested transactions won't help.

What amount of record updates are you expecting to for a large update?
How regularly?

--
Eivind Bakkestuen [NDD]

Eivind wrote:

> > And if this pseudo-code is correct, if another user is waitng to
> > access the affected tableS, right after the inner commit would he be
> > allowed to start his transaction?
>
> If the table(s) in the inner transaction are not also referenced in the
> outer transaction, then I think the answer would be yes. Thorsten?

The inner transaction, and all its changes and locks, become part of the next
outer transaction on a nested commit.

[Enrico]

Hi Eivind.
The thing is that I am making a test with a large number of updates.
Say some 5,000 passes, each pass contains an Edit and an Insert on a table with record length of 152 bytes. But there are a dozen smoller tables that need to be updated concurrently, some with Edits, some with Inserts.
The test takes about 40 seconds on a PC with SSD.disk (75 on the other
hard-drive PC).
My only option is to raise the number of RETRY?

> My only option is to raise the number of RETRY?

That, and reducing the time the entire operation takes, if possible. If
you are using TnxTable components and you are doing this using a
transport to connect to a remote NexusDB Server, batch operations may
help reduce message overhead.

--
Eivind Bakkestuen [NDD]