Generate Transaction Deadlock Example

#!/usr/bin/perl -w
use strict;
use Fcntl qw(:flock);
use DBI;
use Data::Dumper;

=head1 transaction deadlock

Run this a couple of times, and you'll eventually see:

    Running 2 children...
    Error: 1213 / Deadlock found when trying to get lock; \
            try restarting transaction
    Finished child
    Finished child
    2 Children Finished...

    One should be prepared to issue transactions again if they fail - see
        "How to Cope with Deadlocks"
        http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html
    , but we won't do that here - we'll avoid using transactions instead.

=cut

my $flockFile = sprintf "/tmp/lockfile.flock";
my $NUM_CHILDREN = 2;

sub getDBH {
    return DBI->connect(
        sprintf(
            "DBI:mysql:database=%s;host=%s;port=%s",
            'dbname',
            'localhost',
            'unixsocket'
        ),
        'user',
        'pass',
        { PrintError => 0, AutoCommit => 1 } );
}

# Table is created further down - search for 'CREATE TABLE'

sub runTransaction {
    my ($childNum) = @_;
    my $dbh = getDBH;
    my $insertSTH = $dbh->prepare('
        INSERT INTO transactiontest
               (value)
        SELECT ?
          FROM DUAL
          WHERE NOT EXISTS (
            SELECT value
              FROM transactiontest
             WHERE value=?
          )
    ');

    my $value = 1;

    $dbh->{AutoCommit} = 0;

    my $rowsAffected = $insertSTH->execute($value, $value);
    if (! $rowsAffected) {
        printf "Error: %s / %s\n",
                ($dbh->err // '<undef>'),
                ($dbh->errstr // '<undef>');
    }
    $dbh->commit;
}

sub inChild {
    open my $i, '<', $flockFile;
    flock($i, LOCK_SH);

    runTransaction(@_);

    print "Finished child\n";
    flock($i, LOCK_UN);
    close $i;
    exit;
}

open my $flockExH, '>', $flockFile;
flock($flockExH, LOCK_EX);

my @children;
for (1..$NUM_CHILDREN) {
    my $pid = fork();
    if ($pid) {
        # master
        push @children, $pid;
    } else {
        close $flockExH;
        inChild($_);
    }
}

my $dbh = getDBH;
$dbh->prepare('
DROP TABLE IF EXISTS `transactiontest`;
')->execute()
    or die "Couldn't drop table";

$dbh->prepare('
CREATE TABLE `transactiontest` (
    `value` INT NOT NULL
) ENGINE = Innodb;
')->execute()
    or die "Couldn't create table";

printf "Running %d children...\n", $NUM_CHILDREN;
flock($flockExH, LOCK_UN);
close $flockExH;

foreach my $pid (@children) {
    waitpid $pid, 0;
}

printf "%d Children Finished...\n", $NUM_CHILDREN;