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

[Mark Morgan Lloyd]

Joost van der Sluis wrote:

>> 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. 

Various parameters to begin transaction, set transaction and lock. It's 
a few years since I've fiddled with these and I only did it by hand, 
i.e. fed explicit commands through BDE to the server.

If I could ask: where can I find a description of how TSQLQuery etc. 
should be used for non-trivial applications? For example, if I want to 
set up an explicit transaction, insert a few hundred rows of data, and 
then make an explicit decision on whether I commit or rollback?

I can see TSQLQuery.InsertSQL etc., but how should they be used? If I 
want to force a lock on a table how should I do it?

-- 
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]