Where to rollback a transaction in PDO?

Question

my problem is , i have a database design from this link is my database overdesigned?

edit* ok maybe useing transaction ? but where should i put the rollback if it fails ?

 $dbConnect->beginTransaction();
 $RegisterInsert = $dbConnect->prepare("INSERT INTO companies (
    `name`, `address`, `email`, `phone`, `link`, `verified`) VALUES (
    :name, :address, :email, :phone, :link, :verified)");
    $RegisterInsert->execute($RegisterData);

    $RegisterData2['CID'] = $dbConnect->lastInsertId();  

    $RegisterInsert = $dbConnect->prepare("INSERT INTO users_companies (
    `UID`, `CID`, `role`) VALUES (
    :UID, :CID, :role)");
    $RegisterInsert->execute($RegisterData2);
    $dbConnect->commit();

where should i put the rollback ?

Thanks

yup im a bit confused should i do transactions thing ? ( i never used it ) or just do like this, or maybe some joining ? or others that may i have missed, thanks pascal to leave a comment :) — , Jul 28 '10 at 09:21
possible duplicate of: http://stackoverflow.com/questions/2167853/insert-entries-in-multiple-tables-in-php, http://stackoverflow.com/questions/2449132/run-mysql-insert-query-multiple-times-insert-values-into-multiple-tables, http://stackoverflow.com/questions/3225024/mysql-insert-to-multiple-tables-relational, http://stackoverflow.com/questions/1582834/php-mysql-inserting-data-to-multiple-tables — Paresh Mayani, Jul 28 '10 at 09:24
yes i know that, i dont know the best title for this problem, please edit it. — , Jul 28 '10 at 09:26
You should always use transactions with appropriate error handling to ensure that your database remains consistent (what happens if your second insert fails because of insufficient disk space, or because MySQL has crashed), but not all MySQL table types support the use of transactions... e.g. MyISAM doesn't, InnoDB does. — Mark Baker, Jul 28 '10 at 09:27
Put in an error trap after the first $RegisterInsert->execute and rollback on error, else do your $dbConnect->lastInsertId(); and second $RegisterInsert->execute with a second error trap after that and rollback on error, else commit — Mark Baker, Jul 28 '10 at 10:10
ok can you give me an example ?, http://stackoverflow.com/questions/329622/how-can-i-implement-commit-rollback-for-mysql-in-php please take a look here, it says that commit does an rollback if it fails ? so we dont have to put an rollback or a error trap is that true ? thanks — , Jul 28 '10 at 10:15
Setting autoCommit to false should force a rollback at the end of your transaction unless you explicitly commit.... I don't know if commit does an automatic rollback if it fails. I prefer to make all my commits and rollbacks explicit in the code so that there's no misunderstanding when another developer looks at it. — Mark Baker, Jul 28 '10 at 10:34

Kalyan02 · Accepted Answer · 2010-07-28T10:23:11.007

5

A transaction should end with either a rollback() or a commit(), (only one of them)

Its usually used with an if...else statement as logically only one of them should be executed.

$dbConnect->beginTransaction();

//somecode
//$dbConnect->execute( $someInsert );
//some more code
//$result = $dbConnect->execute( $someSelect );
//$nextRow = $result->fetchRow();

//either commit or rollback!
if( $someResultCheck == true )
    $dbConnect->commit();
else
    $dbConnect->rollback();

Transactions are usually used when there is a complex logic involved with queries.

In case you are using MySQL, make sure you are not using MyISAM engine for tables, as it doesn't support transactions.

edited Jul 28 '10 at 10:23

answered Jul 28 '10 at 10:07

Kalyan02

1,416
11
16

Your code isn't guaranteed to automatically rollback unless autoCommit is set to false – Mark Baker Jul 28 '10 at 10:37
ok so were we should do an $someResultCheck ?, on RegisterInsert ? am i right ? – Jul 28 '10 at 10:50
@kaskus : Right. You only want to commit when both the inserts are successfully executed.So you will have to do `$someResultCheck = $RegisterInsert->execute($stmt);`. – Kalyan02 Jul 28 '10 at 11:11
@mark : autoCommit only matters for statements outside a transaction, where individual statements are wrapped inside a transaction. When you manually begin a transaction, it doesn't apply inside that. – Kalyan02 Jul 28 '10 at 11:12
@Kalyan - Thanks for the clarification. As I said elsewhere, I always code explicit transaction starts and commits/rollbacks because there'll never be any misunderstandings then with other developers reading the code – Mark Baker Jul 28 '10 at 11:20

score 5 · Answer 2 · answered Jul 28 '10 at 11:53

As soon as you know that the transaction as a whole is going to fail then you should rollback what you've done so far and not try any further updates - so in pseudo-code:

 function do_updates(array updates)
 { 
    PDO->beginTransaction();
    foreach (updates as statement) {
       run statement
       if failed {
         PDO->rollback(); 
         return false;
       }
    }
    return PDO->commit();

HTH

C.

Where to rollback a transaction in PDO?

2 Answers2