[wp-trac] Re: [WordPress Trac] #9422: Allow for start transaction/rollback/commit sql statements in WP

[WordPress Trac]

#9422: Allow for start transaction/rollback/commit sql statements in WP
-------------------------------+--------------------------------------------
 Reporter:  Denis-de-Bernardy  |       Owner:  Denis-de-Bernardy            
     Type:  enhancement        |      Status:  new                          
 Priority:  normal             |   Milestone:  2.8                          
Component:  Optimization       |     Version:  2.8                          
 Severity:  major              |    Keywords:  has-patch tested dev-feedback
-------------------------------+--------------------------------------------

Comment(by Denis-de-Bernardy):

 @Ryan: I'd be curious to have your thoughts on the following:

 wp_delete_user() has multiple calls to wp_delete_post(), which then fire
 wp_delete_attachment() etc. in a cascaded manner. if an error occurs due
 to tables that got added, we could potentially have stuff that is only
 partially deleted (see code below).

 to work around this, we'd need, at minimum, and in each of the wp_delete_*
 functions, a hook like delete_* at the very beginning, and a hook like
 deleted_* at the very end. then, we've two options.

 either WP sets a global variable that contains the name of a savepoint
 (set by the initial call to whichever wp_delete_* function got called), or
 the plugin takes care of doing it itself. both options have pros and cons.

 the pro for WP managing it is that plugins can rely on it being set where
 appropriate, the con being that this adds some code.

 the pro for the plugin managing it adds no code to WP, the con being two
 plugins that rely on it may end up conflicting, due to the following:

 {{{
 start transaction;
 select * from table; -- returns 2 rows
 delete from table where foo -- trigger error due to check or foreign key
 delete from table where bar -- 1 row affected
 commit;
 select * from foo; -- returns 1 row in mysql, as opposed to the expected 2
 in pgsql
 }}}

 that can lead to plugin A sending a commit before plugin B has only
 partially validated a transaction.

 my own preference would be to use something like:

 {{{
 function wp_start_transaction($transaction_id) {
   global $wp_transaction_id;
   if ( !isset($wp_transaction_id) ) {
     $wp_transaction_id = $transaction_id;
     $wpdb->query("START TRANSACTION;");
   }
 }

 function wp_rollback_transaction($wp_error) {
   global $wp_transaction_id;
   unset($wp_transaction_id);
   $wpdb->query("ROLLBACK;");
   wp_die($wp_error);
 }

 function wp_commit_transaction($transaction_id) {
   global $wp_transaction_id;
   if ( isset($wp_transaction_id) && $wp_transaction_id == $transaction_id
 ) {
     unset($wp_transaction_id);
     $wpdb->query("COMMIT;");
   }
 }
 }}}

 If the code is unwanted most of the time, we could add some kind of define
 (e.g. WP_TRANSACTIONS) that adds them as needed to the various plugin
 hooks when a plugin needs them.

 Thoughts?

 D.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/9422#comment:20>
WordPress Trac <http://trac.wordpress.org/>
WordPress blogging software