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

[Michael Van Canneyt]

On Fri, 10 Dec 2010, Mark Morgan Lloyd wrote:

> Michael Van Canneyt wrote:
>
>>>> Well, you always need a transaction. Without a transaction, Postgres
>>>> will do nothing, ever.
>>>> 
>>>> I assume you are used to the fact that postgres automatically creates an
>>>> transaction for you. With Sqldb you have to do this yourself.
>>> 
>>> Please excuse me for threading onto a fairly old message. In the specific 
>>> case of PostgreSQL, if I use SELECT * FROM pg_stat_activity to examine 
>>> backend state I can see that a Lazarus TSQLQuery, i.e. that has to have an 
>>> associated transaction object, is explicitly marked as being in a 
>>> transaction, while other methods of access (Delphi+BDE, PGAdmin3) are not.
>> 
>> This is one of the things we must still address: close the transaction 
>> after
>> the data has been fetched. (or rather: keep data available in case the 
>> transaction is closed)
>
> I've not had a chance to investigate exactly what the backend thinks Delphi 
> etc. is doing, but long-lived connections caused in particular by db-aware 
> components could be bad news when it comes to server pooling, failover and so 
> on. Long-lived transactions definitely will be.

Only for certain DB engines. Firebird has no problem with it.
I suspect it also depends on the transaction options (readcommitted etc.)
unfortunately these highly depend on the engine.

But we're aware of the issue.

Michael.