[fpc-devel] PostgreSQL SQLdb transactions

[Martin Schreiber]

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