NAME DBIx::TxnPool - Auto resolver of MySQL InnoDB deadlocks and transaction wrapper for DML statements SYNOPSIS This module will help to you to make quickly DML statements of InnoDB engine. You can forget about deadlocks ;-) use DBIx::TxnPool; my $pool = txn_item { my ( $pool, $item ) = @_; $pool->dbh->do( "UPDATE table SET val=? WHERE key=?", undef, $_->{val}, $_->{key} ); # or $dbh->do("INSERT INTO table SET val=?, key=?", undef, $_->{val}, $_->{key} ); } txn_post_item { my ( $pool, $item ) = @_; # Here we are if transaction is successful unlink( 'some_file_' . $_->{key} ); # or unlink( 'some_file_' . $item->{key} ); } txn_commit { my $pool = shift; log( 'The commit was here...' ); } dbh => $dbh, size => 100; # Here can be deadlocks but they will be resolved by module and repeated (see example in xt/03_deadlock_solution.t) $pool->add( { key => int( rand(100) ), val => $_ } ) for ( 0 .. 300 ); $pool->finish; Or other way: my $pool = txn_item { $dbh->do( "UPDATE table SET val=? WHERE key=?", undef, $_->{val}, $_->{key} ); } txn_sort { $a->{key} <=> $b->{key} } dbh => $dbh, size => 100; # Here will not be deadlocks because all keys are sorted before transaction and this example is sinple (see example in xt/04_deadlock_sort_solution.t) # But module will resolve deadlocks in other difficult cases $pool->add( { key => int( rand(100) ), val => $_ } ) for ( 0 .. 300 ); $pool->finish; DESCRIPTION Sometimes i need in module which helps to me to wrap some SQL manipulation statements to one transaction. If you make alone insert/delete/update statement in the InnoDB engine, MySQL server for example does fsync (data flushing to disk) after each statements. It can be very slowly if you update 100,000 and more rows for example. The better way to wrap some insert/delete/update statements in one transaction for example. But there can be other problem - deadlocks. If a deadlock occur the DBI's module can throws exceptions and ideal way to repeat SQL statements again. This module helps to make it. It has a pool inside for data (FIFO buffer) and calls your callbacks for each pushed item. When you feed a module by your data, it wraps data in one transaction up to the maximum defined size or up to the finish method. If deadlock occurs it repeats your callbacks for every item again. You can define a second callback which will be executed for every item after wrapped transaction. For example there can be non-SQL statements, for example a deleting files, cleanups and etc. CONSTRUCTOR Please to see "SYNOPSIS" section Shortcuts: The "txn_item" should be first. Other sortcuts can follow in any order. Parameters should be the last. txn_item (Required) The transaction's item callback. Here should be SQL statements and code should be safe for repeating (when a deadlock occurs). The $_ consists a current item. You can modify it if one is hashref for example. Passing arguments will be *DBIx::TxnPool* object and *current item* respectively. txn_sort (Optional) Here you can define sort function for your data before transaction will be made. If you have only one type SQL statement in "txn_item" but have not sorted keys before transaction you can occur deadlocks (they will be resolved and transaction will be repeated of course but you will lose a processing time) unless you define this function. This method minimize deadlock events. txn_post_item (Optional) The post transaction item callback. This code will be executed once for each item (defined in $_). It is located outside of the transaction. And it will be called if whole transaction was succaessful. Passing arguments will be *DBIx::TxnPool* object and *current item* respectively. txn_commit (Optional) This callback will be called after each SQL commit statement. Here you can put code for logging for example. The first argument will be *DBIx::TxnPool* object Parameters: dbh (Required) The dbh to be needed for begin_work & commit method (wrap in a transaction). size (Optional) The size of pool when a commit method will be called when feeding reaches the same size. max_repeated_deadlocks (Optional) The limit of consecutive deadlocks. The default is 5. After limit to be reached the "add" throws exception. METHODS add You can add item of data to the pool. This method makes a wrap to transaction. It can finish transaction if pool reaches up to size or can repeat a whole transaction again if deadlock exception was thrown. The size of transaction may be less than your defined size! dbh The accessor of "dbh". It's readonly. finish It makes a final transaction if pool is not empty. amount_deadlocks The amount of deadlocks (repeated transactions) AUTHOR This module has been written by Perlover LICENSE This module is free software and is published under the same terms as Perl itself. SEE ALSO DBI, Deadlock Detection and Rollback TODO A supporting DBIx::Connector object instead DBI To add other callbacks in future.