15

How to use mysql transaction in wordpress? I want delete 10 child, if one is active, the total delete will be rollback.

1 Answers1

37

I've never tried it and there is nothing extra ordinary but it's just about running a query like (Run your queries after START TRANSACTION and use COMMIT or ROLLBACK depending on the result) :

mysql_query('START TRANSACTION');
$res1 = mysql_query('query1');
$res2 = mysql_query('query2');
If ( $res1 && $res2 ) {
    mysql_query('COMMIT'); // commits all queries
} else {
    mysql_query('ROLLBACK'); // rollbacks everything
}

So, it could be converted to wordpress, using something like this

$wpdb->query('START TRANSACTION');
$result1 = $wpdb->delete( $table, $where, $where_format = null );
$resul2 = $wpdb->delete( $table, $where, $where_format = null );
if($result1 && $result2) {
    $wpdb->query('COMMIT'); // if you come here then well done
}
else {
    $wpdb->query('ROLLBACK'); // // something went wrong, Rollback
}

You may also use try catch like this answer, (not WordPress but same idea). You can read more about $wpdb query functions (query and delete) on Codex.

MySQL’s default MyISAM storage engine does not support transactions, so it’s not an option. If you want to use transactions, ensure all your tables are defined as InnoDB.

Community
  • 1
  • 1
The Alpha
  • 143,660
  • 29
  • 287
  • 307
  • This does not seem to work for me. I `START TRANSACTION`, truncate a table, insert 59 rows, and `COMMIT`. But I can open the table in Navicat during this and it shows less than 59 rows, so it appears that the transaction is not working for me. Is there any way to confirm in code that `START TRANSACTION` has succeeded? – Liam Nov 20 '19 at 20:50
  • 2
    the problem here is that truncate has an implicit commit: https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html – Full Jul 22 '22 at 13:04