Transaction For Multiple Activerecord

Yii 1.1 General Discussion
1

hi every body

suppose i’ve 2 active record name bank_fund and user_fund

i want to transfer value from bank to user say 10$

i use transaction to make sure that following steps are excuted:




1- check if fund in bank is > 10$ so to withdraw

2- update bank fund with 10$ deduction

3- add 10$ to user_fund

so how to excute that using transaction with active record ?

my second question : if step num 1 and num 2 successed but step 3 failed so rollover the process again .ok is that means another deduction of 10$ will occurs again or what will happend?

1 Like
2

If you use db transaction and do rollback then ALL inserts and updates are undone.

There is however one problem - you cannot simply do ‘SELECT FOR UPDATE’ with active record and that is required to lock account record for concurrent reads/updates. Another solution is to add filter ‘AND fund > 10’ on update statement and check if there are updated records after execution.

using transaction is quite simple: http://www.yiiframework.com/doc/guide/1.1/en/database.dao#using-transactions

transactions are bound to DB connection so AR operations will use them as well.

3

ok i can clarify more>>>> take a look at the following code and if it’s right




$model=bank_fund::model();

$model1 = user_bank::model()

$transaction=$model->dbConnection->beginTransaction();

try

{

    $bank_fund=$model->findByPk(1);

    $bank_fund->fund -= 10 ;

    $bank_fund->save();

    $user_bank = $model->findByPk(56) //id of user

    $user_bank->fund += 10;

    $user_bank->save()


    $transaction->commit();

}

catch(Exception $e)

{

    $transaction->rollback();

}

is that code will apply transaction to both queries inside try ?

4

yes. both ‘save’ functions generating insert/update will be called inside same transaction so either both will be applied or none of them.

5

ok after i had finished testing some senarios ,if you want to use transaction for multiple tables you should use only the following code (not active record)




transaction=$connection->beginTransaction();

try

{

    $connection->createCommand($sql1)->execute();

    $connection->createCommand($sql2)->execute();

    //.... other SQL executions

    $transaction->commit();

}

catch(Exception $e) // an exception is raised if a query fails

{

    $transaction->rollback();

}
6

what tests did you run? did you also check returned value from save() functions? it returns false when saving failed…

transaction is created on database level so it is practically impossible for any insert/update statements run between ‘start transaction’ and ‘rollback’ not being undone…

there is another thing: if you use MySQL MyISAM tables - they do not support transaction but MySQL will not throw any errors in such case.

7

ok let me explain what i did :

using active record :

when using active record in transaction for multiple table insertion i did a trick of leaving a required

field blank on the one table so insertion query for this table fails (this is query 1) while the other insertion query succeed(that is query 2)

so when testing in that way inside a transaction the query 1 does not insert the record while query 2 insertion

was successful

using DAO :

it works fine if a required field is not assigned non of the queries in the transaction is excuted

8

ok I understand.

but you confused transactions with error handling. Transactions are to ensure that a set of statements is executed in full or none of the statements. It has nothing to do with error handling. you have to decide by yourself whether you want to commit the transaction or rollback it. When using AR save() method you have to check return value if the saving was successfull or not and handle that information somehow.

try this code with AR and it should do the trick and work like you want:




$model=bank_fund::model();

$model1 = user_bank::model()

$transaction=$model->dbConnection->beginTransaction();

try

{

    $bank_fund=$model->findByPk(1);

    $bank_fund->fund -= 10 ;

    if( !$bank_fund->save() ) {

       throw new Exception( 'Bank fund saving error' );

    }

    $user_bank = $model->findByPk(56) //id of user

    $user_bank->fund += 10;

    if( !$user_bank->save() ) {

       throw new Exception( 'User fund saving error' );

    }


    $transaction->commit();

}

catch(Exception $e)

{

    $transaction->rollback();

}
1 Like
9

you are right , i figure out what’s wrong with active record code that make one query successed and the other fail, it was the validation rule that prevent the query from beign excuted ie: no sql query is generated.

thanks for help

1 Like