Home > mysql, Programming > Using mysql transaction

Using mysql transaction

There are times when mysql tables need to be truncated (or certain rows need to be removed) before new records are inserted, transaction statement should be used to ensure data integrity.

Example:



// blah blah establish mysql connection

mysql_query('START TRANSACTION');
$res1=mysql_query('TRUNCATE TABLE tbl_test');

// insert new rows into tbl_test, if all went well
$res2=true;   // or false if something goes wrong

If ( $res1 && $res2 ) {
    mysql_query('COMMIT');
} else {
    mysql_query('ROLLBACK');
}

// blah blah close connection

Note: transaction feature is only available when InnoDB engine is used.

Advertisements
Categories: mysql, Programming Tags: , ,
  1. altmannmarcelo
    May 19, 2011 at 2:32 pm

    Good post!
    Just remember, the truncate command does not accept rollback.

  2. ricoch3n
    May 20, 2011 at 12:43 am

    Thanks for pointing that out. Indeed, All DDL statements cannot be rolled back (http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-data-definition.html)

    Better examples illustrating mysql transactions can be found here:
    http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples

  1. No trackbacks yet.

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

%d bloggers like this: