COMMIT/ROLLBACK on Database recursive queries

This topic is about how you handle error on recursive/looped database transaction.

Imagine Inserting a batches(rows) of data from the database if ones batch(row) fails, the next rows to be inserted will be disregarded which results to a not complete batch of data inserted.

So below is how you handle this common scenario.

try {

            $wpdb->query(‘START TRANSACTION’);

            //recursive query here

            $wpdb->query(‘COMMIT’);

}catch(Exception $e){

     //some error handling code here

      $wpdb->query(‘ROLLBACK’);

}

Note i am using wpcodex functions, can be applied in any platforms as long as you have START TRANSACTION,COMMIT on success and ROLLBACK on failure.

1. START TRANSACTION will start the db TRANSACTION

2. Recursive queries examples are, multiple inserts from db.

foreach ($step->controls as $control) {

                    $wpdb->insert(‘controls’, array(

                        ‘control’ => $control->name,

                        ‘step_id’ => $step_id,

                    ));

      }

Assuming line above will insert multiple rows to the DB unless there is no error will occur it will fire the COMMIT that will execute all the inserts, else transaction will fire the ROLLBACK which will undo all the changes from the db within the transaction

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s