[bettor]

I would like to run two queries one after each other in a transaction.

so I have:

$transaction = Yii::app()->db->beginTransaction();
try
{
Model::model()->updateCounters(blablabla);
Yii::app()->db->createCommand($sql)->execute();
}
catch(Exception $e)
{
return $transaction->rollback();
}
$transaction->commit();

What I want to achieve here is to make sure that if one of the query fails to roll back the other as well. So either both or none! The above scheme does not work. I tested it by braking second query and I still end up with the first query executed successfully and the second failed.

Please point me to what I've gotten wrong or a better way of achieving this.

Cheers,
bettor

[PoL]

What motor are you using?

[bettor]

PoL, on 06 May 2010 - 02:13 PM, said:

What motor are you using?

Not quite sure what you mean by "motor". Both tables are innoDB if that is your question.

[PoL]

Yeah, that is my question!


By the way, I think you have an error in the order of the sentences.

From the guide:

$model=Post::model();
$transaction=$model->dbConnection->beginTransaction();
try
{
    // find and save are two steps which may be intervened by another request
    // we therefore use a transaction to ensure consistency and integrity
    $post=$model->findByPk(10);
    $post->title='new post title';
    $post->save();
    $transaction->commit();
}
catch(Exception $e)
{
    $transaction->rollBack();
}

Check the position of the commit!!!

[bettor]

Hi PoL,

I changed commit() possition but still no luck. The first query executes and than when the second fails there is no rollback of the first.

any thoughts would be welcome.

[tri]

bettor, on 06 May 2010 - 03:40 PM, said:

Hi PoL,

I changed commit() possition but still no luck. The first query executes and than when the second fails there is no rollback of the first.

any thoughts would be welcome.

Just thinking loud here...
What if you use AR only or DAO only for both operations?
Try to set Yii::app()->db->autoCommit to false.

/Tommy

[bettor]

tri, on 06 May 2010 - 04:36 PM, said:

Just thinking loud here...
What if you use AR only or DAO only for both operations?
Try to set Yii::app()->db->autoCommit to false.

/Tommy

Hi Tommy,
Yii::app()->db->autoCommit to false did not help.

However, converting my AR query to DAO one so having 3 DAO queries did it. I wonder why transactions don't work for AR? Should an enhancement be raised? Your thoughts?

the updateCounters() AR query is sooo comfortable and I don't want to swap with for couple of DAO queries.

Cheers,
bettor

[tri]

bettor, on 07 May 2010 - 03:44 AM, said:

Hi Tommy,
Yii::app()->db->autoCommit to false did not help.

However, converting my AR query to DAO one so having 3 DAO queries did it. I wonder why transactions don't work for AR? Should an enhancement be raised? Your thoughts?

the updateCounters() AR query is sooo comfortable and I don't want to swap with for couple of DAO queries.

Cheers,
bettor

I'd bet that AR-only would work too. Perhaps there is a hidden (partial) db close/reopen that leads to an autocommit? Just guessing, though. At least - several Yii versions back - I had to toggle the db->active property when mixing AR with DAO.

/Tommy

[bettor]

tri, on 07 May 2010 - 04:16 AM, said:

I'd bet that AR-only would work too. Perhaps there is a hidden (partial) db close/reopen that leads to an autocommit? Just guessing, though. At least - several Yii versions back - I had to toggle the db->active property when mixing AR with DAO.

/Tommy

Hi Tommy,

Thanks for you assistance. I would like to correct my comment above a bit. It works when all queries are DAO and it NEEDS autoCommit to be false.

Thanks again. Not sure if Yii have to compromise on anything to achieve transactions to work for a mix of AR and DAO?

Are you willing to share your work around to achieve transaction with a mix of AR and DAO in more details?

Cheers,
bettor

[tri]

bettor, on 07 May 2010 - 04:44 AM, said:

Hi Tommy,

Thanks for you assistance. I would like to correct my comment above a bit. It works when all queries are DAO and it NEEDS autoCommit to be false.

Thanks again. Not sure if Yii have to compromise on anything to achieve transactions to work for a mix of AR and DAO?

Are you willing to share your work around to achieve transaction with a mix of AR and DAO in more details?

Cheers,
bettor

Unfortunately I didn't use transactions in the case I referred to. I'm just guessing about any probable cause.

Cheers,
/Tommy

[bettor]

tri, on 07 May 2010 - 09:43 AM, said:

Unfortunately I didn't use transactions in the case I referred to. I'm just guessing about any probable cause.

Cheers,
/Tommy

Shit hit fan and came back! Even though I was able to move the transaction thing that autoCommit totally messed up my other queries where I am updating stuff. I have declared the autoCommit=false in config/main.php db secion Is this not the way to declare autoCommit value? when I remove the autoCommit all works well.

Any advice is welcome.

Thanks in advance
bettor

[Mike]

beginTransaction() is a wrapper for PDO::beginTransaction(). So maybe read here, there are some notes that could explain your problems:
http://de.php.net/ma...transaction.php

Quote

Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.