view release on metacpan or  search on metacpan

lib/DBIx/TxnPool.pm  view on Meta::CPAN

=head1 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 (to 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 no deadlocks because all keys are sorted before transaction:
    # circle blocks inside the InnoDB not occur
    $pool->add( { key => int( rand(100) ), val => $_ } ) for ( 0 .. 300 );
    $pool->finish;

=head1 DESCRIPTION

If you need massive quickly updates or inserts into InnoDB database - this
module for you! It helps to wrap some SQL manipulation statements to one
transaction and has the deadlock and signal solution.

=head1 DETAILS

If you make alone insert/delete/update statement in the InnoDB engine, MySQL
server does fsync (data flushing to disk) after each statement. It can be very
slow for many updates. The best solution can be to wrap some
insert/delete/update statements in one transaction for example. But this raises
a new problem - deadlocks. If a deadlock occurs a DBI module 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 your pool to be fed by your data, it wraps data in one
transaction up to the maximum defined size or up to the finish method. If
deadlock occurs a pool 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.

=head1 CONSTRUCTOR

Please to see L</SYNOPSIS> section

=head2 Shortcuts:

The C<txn_item> should be first. Other sortcuts can follow in any order.
Parameters should be the last.

=over

=item txn_item B<(Required)>

The transaction item callback. There should be SQL statements and code should be safe for repeating (when a deadlock
occurs). The C<$_> consists a current item. You can modify it if one is hashref for example. Passing arguments will be
I<DBIx::TxnPool> object and I<current item> respectively. B<Please don't catch exceptions here (by try{} or eval{} for
example)> - by this way deadlocks are defined outside under the hood!

=item txn_sort B<(Optional)>

Here you can define sort function for your data before a transaction will be
made. If you have only one type SQL statement in L<txn_item|/"txn_item
(Required)"> but you didn't sort keys before transaction you can have deadlocks
(they will be resolved and transaction will be repeated but you will lose a
processing time) unless you define this function. This method minimize deadlock
events!

=item txn_post_item B<(Optional)>

The post transaction item callback. This code will be executed once for each item (defined in C<$_>). It is located
outside of the transaction. And it will be called if whole transaction was successful. Passing arguments are
I<DBIx::TxnPool> object and I<current item> respectively. You can do here your own error handling in callback. If your
code here will throw an excetption it will be propagated above.

=item txn_commit B<(Optional)>

This callback will be called after each SQL commit statement. Here you can put
code for logging for example. The first argument is I<DBIx::TxnPool> object

=back

=head2 Parameters:

=over

=item dbh B<(Required)>

The dbh to be needed for begin_work & commit method (wrap in a transaction).

=item size B<(Optional)>

The size of pool when a commit method will be called when feeding reaches the same size.

=item block_signals B<(Optional)>

An arrayref of signals (strings) which should be blocked in slippery places for