Do you mean connection?

As soon as you commit, the transaction is finished so I am a little
confused.

If you come from a desktop database background, you may be thinking
that a query is an operation, but it isn't really. It might be in a
particular situation, but normally an operation involves running
several queries.

Consider a bank transfering $50 from account 1 to account 2

You might consider something like

insert into accttrans (id, amt, descr) values (1, -50, 'paid to 2);
insert into accttrans (id, amt, descr) values (2, 50, 'paid from 1');
update acctbalance set bal = bal - 50 where id = 1;
update acctbalance set bal = bal + 50 where id = 2;

There is obviously a lot more than this. You would need to check
whether account 1 had $50 etc, but this is just an example.

Now if ALL of these queries are successful, then great. Otherwise,
there would be some inconsistency in the database state (which is
bad).

Transactions let you treat these operations as atomic. That basically
means that either all of them should succeed, or if there is a
problem, then any operation that was done should be then undone.
Firebird has a very clever way of doing this. Because it has a
multigenerational architecture, the old value is still there for
other transactions to reference until you commit, and there are no
other transactions that need to reference it at which point the
garbage collection will flag it as available space. When you commit
your transaction, the database then marks your version as the current
one.

So when is the best time?

It really depends on what you are doing. If you want a succession of
queries to be treated as atomic, then that gets its own transaction.
If each query is a different operation in its own right, then they
should be committed separately. If the entire program needs to run
before the operation is successful, and if anything goes wrong you
want to undo everything, then the entire program should go inside one
transaction.

Adam