[TYPO3-dev] Idea: add transaction support to t3lib_db

[Christian Futterlieb]

Hi Dmitry

I find it a good idea to add transactions to t3lib_db. However I'd like 
to point out two things that are problematically in my eyes:

I don't think that it is a good Idea to let transactions be started and 
committed this (nested) way, because

a) it is irritating, when a transaction could be started/committed, but 
in fact at this point it has no effect on the database (except no 
transaction was started before). Furthermore it may be never committed 
when another independent code segment rolls back the transaction.

b) it is the developers responsibility to do not nest transactions. If 
one tries to start a transaction and gets the error, he should know that 
the transaction is not needed there.

So, I'll prefer the methods
public function startTransaction();
public function commit();
public function rollback();

to be simple proxies to $this->sql_query('START TRANSACTION');

Additionally a public controlling method e.g 'hasActiveTransaction()' 
could be useful:

public function startTransaction() {
     if($this->hasActiveTransaction()) {
         throw new Exception('A transaction is already started on this 
connection');
     }
}

I hope I could explain myself in an understandable way.

Regards, Christian


On 03/04/2011 02:25 PM, Dmitry Dulepov wrote:
> Hi!
>
> I propose to add several new functions to t3lib_db:
>
> public function startTransaction();
> public function commit();
> public function rollback();
> public function hasFailedTransactions();
>
> Why it is necessary? Quite often you need to make changes to various
> tables. If a single change fails, that may leave the system in the
> corrupted state. Transactions solve that. Currently you can just use a
> simple way:
>
> $GLOBALS['TYPO3_DB']->sql_query('START TRANSACTION');
>
> However if your code is complex, transactions may be started multiple
> times by the code on various levels. Imagine something like that:
>
> updateUser -> start transaction
> updateGroups -> start trasaction / commit
> updateMediaAssets -> start trasaction / commit
> ...
> TCEmainHook::processDatamap() -> start trasaction / commit
> ...
> -> commit
>
> Here we have a major issue: transactions cannot be nested (it is a
> error). Thus we need a way to track that transaction is already started
> and not to issue another START TRANSACTION call. Also we need to watch
> amount of COMMIT statements and COMMIT only when it matches the number
> of START TRANSACTION calls. On the other hand, ROLLBACK should be
> possible from any point. So the first three new functions will handle
> that. The last function would tell if the transaction is rolled back by
> any inner levels or a error happened during execution.
>
> I could simply make and RFC but I want this to be discussed first. Also
> I would like to know Xavier's opinion because that affects DBAL. DBAL
> has its own methods like this, which I would copy from the MySQL driver.
> But still I would like to know what people think.
>
> P.S. I know that transactions do not work in MyISAM :)
>