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

[Joost van der Sluis]

On Fri, 2010-11-26 at 12:14 -0200, Alexsander Rosa wrote:
> 2010/11/26 Mark Morgan Lloyd <markMLl.lazarus at telemetry.co.uk>
>         
>                         I've tried to use sqldb but it adds too much
>                         unneeded overhead. Under PostgreSQL it
>                         actually locks the tables for DDL
>                         modifications by default, rendering the
>                         development machines ridden with "IDLE in
>                         transaction" connections while the apps
>                         are running, even for mere SELECT's.
>         
>         
>         Which locks- Postgres or sqldb?
>         
>         
> 
> PostgreSQL gets "locked", I mean, the tables being used by sqldb can't
> be modified (new columns, new constraints, etc) while the sqldb
> connection is active, even if it's a read-only SELECT. If someone is
> testing a heavy report the tables affected can't be changed by the DBA
> -- it's a major headache. 

Thing is, that TSQLQuery by default fetches only the first 10 records,
and then keeps the cursor open, so when you need more records, those are
fetched too. Setting PacketRecords to -1 will make it fetch the complete
table at once. 

But then still, you have the data fetched in memory, so if you want to
change the data, and apply changes, you have to keep the transaction
open. (Well, it's up to you. But think through what you're doing)

And I assume that Postgres (don't remember exactly) can set the behavior
of transactions. Apparently they are blocking by default. 

Joost.