Yii Framework Forum: need help with transaction - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

need help with transaction Rate Topic: -----

#1 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 752
  • Joined: 02-February 09

Posted 06 May 2010 - 02:09 PM

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
0

#2 User is offline   PoL 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 506
  • Joined: 05-November 08
  • Location:Buenos Aires, Argentina

Posted 06 May 2010 - 02:13 PM

What motor are you using?
Don't say what you think, think what you say
The problem is communication! Excess of communication!
0

#3 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 752
  • Joined: 02-February 09

Posted 06 May 2010 - 03:14 PM

View PostPoL, 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.
0

#4 User is offline   PoL 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 506
  • Joined: 05-November 08
  • Location:Buenos Aires, Argentina

Posted 06 May 2010 - 03:20 PM

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!!!

Don't say what you think, think what you say
The problem is communication! Excess of communication!
0

#5 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 752
  • Joined: 02-February 09

Posted 06 May 2010 - 03:40 PM

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.
0

#6 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 06 May 2010 - 04:36 PM

View Postbettor, 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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#7 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 752
  • Joined: 02-February 09

Posted 07 May 2010 - 03:44 AM

View Posttri, 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
0

#8 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 07 May 2010 - 04:16 AM

View Postbettor, 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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#9 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 752
  • Joined: 02-February 09

Posted 07 May 2010 - 04:44 AM

View Posttri, 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
0

#10 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 07 May 2010 - 09:43 AM

View Postbettor, 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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#11 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 752
  • Joined: 02-February 09

Posted 07 May 2010 - 11:22 AM

View Posttri, 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
0

#12 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 08 May 2010 - 04:08 AM

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.

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users