[fpc-devel] PostgreSQL SQLdb transactions

[Chris Dryburgh]

On 05/11/14 03:04 AM, Martin Schreiber wrote:
> On Tuesday 04 November 2014 19:33:22 Chris Dryburgh wrote:
>> The end result should be that server transactions only open when needed.
>> Users would likely commit write transactions quickly to save data to the
>> database. Read-only transactions might be left in a open state but can
>> be closed by an aware developer when not needed. To eliminate open
>> read-only transactions an option is to never open a transaction for
>> select queries which would mean a overhead for the server opening and
>> closing transactions for each query. What do others here think?
> The MSEgui version of TSQLQuery has two transaction
> properties, "transactionwrite" which is used for write operations
> and "transaction" used for read operations and for write operations
> if "transactionwrite" is not assigned. So one can use different transactions
> and transaction isolation levels for reading and writing.
> The MSEgui version of TSQLTransaction has the flag "tao_fake" which omits
> sending "BEGIN", "COMMIT" and "ROLLBACK" in order to use implicit
> transactions if the server supports it.
> Additional it is possible to "disconnect" an open query dataset from database
> and transaction, they can be closed after disconnect. Later it is possible
> to "reconnect" the still open dataset.
> I suggest that you implement a "tao_fake"-like functionality yourself, maybe a
> patch will be accepted. I already suggested it several times because
> especially MySQL users don't like and often even don't know transactions. :-)
> If you like to know how it is done in MSEgui:
>
> https://gitorious.org/mseide-msegui
>
> Martin
> _______________________________________________
> fpc-devel maillist  -  fpc-devel at lists.freepascal.org
> http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
I'm aware of the MSEgui approach and have viewed the source code for how 
it handles transactions. I had trouble seeing how to integrate it with 
the PostgreSQL code. With my approach the programmer does not have to 
specify a transaction type. Multiple statements run faster in a single 
transaction batch and their read-only or read-write actions do not 
matter. The current MSEgui approach does not allow batch read-only 
queries which results in server overhead if there are multiple queries 
run together. It is also read-only transaction idiot proof which is a 
good thing. Michael's approach of allowing for closing a transaction 
without closing a still in use dataset looks like a better approach. It 
will still cause issues for users "that don't like and often even don't 
know transactions".