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

[Mark Morgan Lloyd]

Joost van der Sluis wrote:
> On Tue, 2010-11-23 at 16:01 +0000, Mark Morgan Lloyd wrote:
>> Before anything else I'd like to say that I'm entirely familiar with 
>> transactions in the SQL context. However I've been having a bit of 
>> difficulty sorting out the relationship between a TPQConnection, 
>> multiple TSQLQuery, and transaction object(s).

>> Since this is strictly read-only, I can't see why I need transactions, 
>> but I obviously respect the judgment of whoever designed the architecture.
> 
> 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.

Just got there- I'd forgotten that one :-)

>> Am I correct in my understanding that in order to close the transaction 
>> I have to set Action (e.g. to caRollback) and then Active to False? It 
>> appears that if I don't do this the query never sees data being updated 
>> at the backend.
> 
> To close the transaction, use .commit or .rollback. (In your
> case .rollback)
> 
> That the query isn't updated, isn't that because it's cached? Did you
> close and reopened the query?

Yes, I was always seeing the same data until I started forcibly closing 
the transaction. When I queried server statistics I could see that the 
same transaction had been active for an extended period, and when 
running manual tests (using the psql utility) I determined that that was 
the way that PostgreSQL behaved.

> If that does not help, you have to set the proper
> transaction-properties, so that it can read data committed in other
> transactions. 
> 
>> To support the two TSQLQuery objects, do I need one or two 
>> TSQLTransaction objects? If I have to have two TSQLTransaction objects, 
>> does that imply that I also have to have two TPQConnection objects?
> 
> Just one. 

Thanks, working on it.

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

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