[Lazarus] What is a TSQLTransaction and why do I need one?

[Joost van der Sluis]

On Tue, 2010-11-30 at 11:54 +0100, michael.vancanneyt at wisa.be wrote:
> 
> On Tue, 30 Nov 2010, Joost van der Sluis wrote:
> 
> > On Mon, 2010-11-29 at 17:57 +0100, michael.vancanneyt at wisa.be wrote:
> >> Procedure TSQLConnection.CreateDefaultTransaction;
> >>
> >> If coAutoTransaction is set, and TSQLQuery does not have a TSQLTransaction
> >> assigned, it creates one through TSQLConnection.CreateDefaultTransaction.
> >> (It should be of type TSQLAutoTransaction, a simple descendent of
> >> TSQLtransaction, so detection of 'default transaction' is simple)
> >>
> >> Whenever a TSQLQuery statement is executed and the transaction of TSQLTransaction is
> >> of type TSQLAutoTransaction, then it automatically commits (more flags can be introduced
> >> for this).
> >
> > This doesn't change Alexsander problem, because there's still the same
> > transaction. Only difference is that he doesn't have to place it on the
> > form.
> 
> I thought that exactly this was his problem ? Namely, that he is forced to
> use a transaction component.

No, his problem is that the table was locked. He thinks/though(?) that
the reason for this is/was that he had to explicitly create the
transaction component. While Zeos didn't use any transaction.

This is simply not true. The difference with Zeos is that Zeos by
defaults opens a implicit transaction which is closed immediately after
fetching the data. And all data is fetched at once, so the lock is there
for only a very short time. So, depending on his implementation, he
won't notice the lock, or it will pop up as a random bug in the
application, which will only occur at some spare moments. (More under
heavy load)

The other solution would be to change the transaction-isolation level.
Maybe that Zeos by default also uses another transaction-isolation
level.

> > Besides that, I think that disabling the explicit transaction for
> > Postgres can be done much easier.
> 
> Maybe, but that would be a postgres-only solution, which I don't think is good. 
> I heavily oppose such a solution: SQLDB should behave the same on all databases.
> 
> My proposal would work on all platforms, and alleviates Alexsander's problem.

I think we should not change a thing, until the problem is clear. Maybe
we can come up with some 'general' transaction-isolation levels. The
reason they are not implemented in a general way is that each DB has
it's own locking-style.

Joost.