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

[Michael Van Canneyt]

On Mon, 29 Nov 2010, Joost van der Sluis wrote:

> On Mon, 2010-11-29 at 17:57 +0100, michael.vancanneyt at wisa.be wrote:
>>
>> On Mon, 29 Nov 2010, Joost van der Sluis wrote:
>>
>>> On Wed, 2010-11-24 at 20:57 +0100, Michael Van Canneyt wrote:
>>>>
>>>> On Wed, 24 Nov 2010, Alexsander Rosa wrote:
>>>>
>>>>> But it is transparent to the libpq programmer; why it's not transparent with sqldb?
>>>>
>>>> Correction: it can be transparant in libpq.
>>>>
>>>> But we can make it so in SQLDB.
>>>>
>>>> The SQLDB model is in fact modeled after Firebird.
>>>> Firebird offers more control over the transactions.
>>>>
>>>> We did not want to take away this possibility, so we modeled sqldb on
>>>> the most powerful RDBMS. This adds some overhead for the others.
>>>>
>>>> What we neglected to do is add what Martin added: offer a less intrusive
>>>> way for the programmer to use it (i.e. create a default transaction in the
>>>> background if none is specified, and close the transaction once the data
>>>> is read).
>>>>
>>>> But we'll do that too, all in good time.
>>>>
>>>> If someone is in a hurry, patches are definitely accepted.
>>>
>>> Problem is that I haven't found a good model for this. Martin's solution
>>> could be an idea, though.
>>>
>>> It could help if someone could sketch the typical wanted behavior from
>>> the transactions.
>>
>> I sent an idea for an implementation in a reply to Alexsander Rosa.
>> basically:
>>
>> TConnectionOption = (coAutoTransaction)
>> TConnectionOptions = set of TConnectionOption;
>>
>> Property TSQLConnection.options = TConnectionOptions;
>>
>> Procedure TSQLConnection.CreateDefaultTransaction;
>>
>> If coAutoTransaction is set, and TSQLQuery does not have a TSQLTransaction
>> assigned, it creates one through TSQLConnection.CreateDefaultTransaction.
>> (It should be of type TSQLAutoTransaction, a simple descendent of
>> TSQLtransaction, so detection of 'default transaction' is simple)
>>
>> Whenever a TSQLQuery statement is executed and the transaction of TSQLTransaction is
>> of type TSQLAutoTransaction, then it automatically commits (more flags can be introduced
>> for this).
> No, not the design. What I meant is to sketch what you want to do. Eg:
>
> I open a dataset
> A user changes some data
> Data is saved
>
> In this case it should be:
>
> Open transaction
> Open data
> Edit data
> Close data
> Close/commit transaction
>
> This can be done perfectly with sqldb. But which are the cases which are
> difficult?

There aren't any. What you described above is what it is :-)

No, there are of course some tricky cases:

* There is currently no 'offline' state, in the sense that the transaction/connection
   can be closed as soon as you have fetched the data. There is no need to keep the
   transaction active. (coCommitOnFetch ?)
* In such a model, a transaction would need to be opened whenever updating the database.
   One can imagine this too when the transaction is kept open: use the same transaction as
   the select transaction, or use a separate transaction (coUpdateInPrivateTransaction).
* A tricky one is: what to do with 2 simultaneous queries A and B, both connected
   to the same database. If they share a transaction, then committing A will also Commit B.
   (which is what happens in DBExpress, BDE).
   -> you could introduce a flag 'coPrivateTransactions'
In short, there are many possibilities. But for starters, I would implement the case
where all 'auto' TSQLQuery objects share the same 'default' transaction.

Michael.