Testing database deadlock retry logic

I just spent a *really* long time writing a test for automatic deadlock retry in a database api module I maintain. So you or I don't have to figure it out in the future, here's the recipe.

So firstly you can't use transactions to build the deadlock situation. That would be easy, but it defeats our purpose since your auto-deadlock retry logic is not going to be able to retry intra-transaction statements (of course you could implement full transaction replay, but that's another story).

So I did it using cursors. Here is a perl sub that builds Sybase TSQL. It returns two values only because Sybase requires the cursor declaration in a separate batch.

sub deadlock_sql {
    my ($table1, $table1_char_col, $table2, $table2_col) = @_;
    
    "declare the_cursor cursor for select $table1_char_col from $table1 for update",
    sprintf 'declare @col_value varchar(100)
             open the_cursor

             fetch the_cursor into @col_value
             waitfor delay "00:00:04" -- wait 4 seconds
             update %s set %s = %s
             -- only need to go round once to cause deadlock
             ', $table2, $table2_col, $table2_col;
}

So we are taking out a write lock on the first table, waiting 4 seconds, then taking out a write lock on the second table. Do that twice in opposite order and you have yourself a deadlock:

sub cause_a_deadlock {
    
    my $child_pid;
    my $parent_retries;
    
    pipe(FROM_CHILD, TO_PARENT);
    
    if ($child_pid = fork) {
    
        # parent
    	
        close(TO_PARENT);
       
       my $dbh = new_dbh();
       my @sql = deadlock_sql('table1', 'a_char_col_from_table1', 'table2', 'any_col_from_table_2');
       
       sleep 2; # sleep half of sql sleep to ensure overlap
       
       $dbh->exec_sql($sql[0]);
       lives_ok { $dbh->exec_sql($sql[1]) };
       
       $parent_retries = $dbh->deadlock_retry_attempts;
       
   } else {
   
       # child
       
       close(FROM_CHILD);
       
       my $dbh = new_dbh();
       my @sql = deadlock_sql('table2', 'a_char_col_from_table2', 'table1', 'any_col_from_table_1');
       
       $dbh->exec_sql($sql[0]);
       lives_ok { $dbh->exec_sql($sql[1]) };
       
       print TO_PARENT $dbh->deadlock_retry_attempts . "\n";
       
       exit;
   
   }
   
   my $child_retries = ;
   chomp($child_retries);
   close(FROM_CHILD);
   
   waitpid $child_pid, 0;
   
   # we don't know which spid was aborted & retried, but one was
   ok($parent_retries + $child_retries > 0, 'there were deadlock retries');
   is($parent_retries, 0, 'only the child had to retry') if $child_retries;
   is($child_retries, 0, 'only the parent had to retry') if $parent_retries;
}

Of course the exact code will depend on your database and database abstraction layer.