MySQL Bugs: #32436: KILL QUERY completely deadlocks mysqld

Bug #32436	KILL QUERY completely deadlocks mysqld
Submitted:	16 Nov 2007 14:10	Modified:	12 Dec 2007 19:58
Reporter:	Ralf Neubauer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.32, 5.0.44, 5.1.16, 5.1.22	OS:	Any (Linux and Windows)
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any

Description:
Repeatedly using KILL QUERY against the same connection brings mysqld into a totally unresponsive state where every connection hangs. The daemon can only be revived by killing with -KILL or via the Task-Manager and then restarting; simply restarting the service does not work. This was detected in a real application and reduced to the attached script.

This works with 5.1.16 and 5.1.22 on a dual core Windows XP workstation, with 5.0.44 on a 4 core Windows 2003 Server server and with the prepackaged mysql-server from Debian etch (MySQL version 5.0.32) in a single-core Linux environment inside VMware.

On the Windows SMP systems the daemon hangs after about 1000 iterations and even with $N = 1. In my test on the single CPU Linux system it only hung with $N = 3 and millions of iterations.

A sample output of the attached script looks like this:

waiting for target...
killed 0
killed 256
killed 512
tick 1
tick 2
tick 3
tick 4
tick 5
Terminating on signal SIGINT(2)

(there are no 'killed' lines after the first tick, because the server hung even before the first tick)

How to repeat:
#! /usr/bin/perl

use warnings;
use strict;

use DBI;
use threads;
use threads::shared;

my $N = 3;
my $T = 300;

my ($host, $db, $port, $user, $password) =
  qw(localhost db 3306 user password);

my $dsn = "dbi:mysql:host=${host};database=${db}:port=${port}";

my @thread;
my $target_id :shared;

push @thread, async {
  my $dbh = DBI->connect($dsn, $user, $password) or die $DBI::errstr;
  $SIG{'KILL'} = sub { $dbh->disconnect; exit };
  {
    lock $target_id;
    ($target_id) = @{$dbh->selectcol_arrayref(qq{SELECT CONNECTION_ID()})};
    cond_signal $target_id;
  }
  my $count = 0;
  while (1) {
    $dbh->do(qq{SELECT SLEEP(1000)});
    $count & 0xff or print STDERR "killed $count\n";
    $count++;
  }
};

{
  lock $target_id;
  while (!defined $target_id) {
    print "waiting for target...\n";
    cond_wait $target_id;
  }
}

for (1..$N) {
  push @thread, async {
    my $dbh = DBI->connect($dsn, $user, $password) or die $DBI::errstr;
    $SIG{'KILL'} = sub { $dbh->disconnect; exit };
    while (1) {
      $dbh->do(qq{KILL QUERY ?}, {}, $target_id);
    }
  }
}

for (1..$T) {
  sleep 1;
  print STDERR "tick $_\n";
}

$_->kill('KILL')->join for @thread;

Thank you for the bug report. Verified on Windows Vista 64-bit.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38516

ChangeSet@1.2537, 2007-11-26 15:23:46+01:00, thek@adventure.(none) +1 -0
  Bug #32436 KILL QUERY completely deadlocks mysqld
  
  The lock order in Item_func_sleep was wrong and this could lead to a dead lock
  when several threads set KILL QUERY repeatadly during on a SELECT SLEEP().
  
  This patch solves the issue by resolving the lock order properly.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38521

ChangeSet@1.2537, 2007-11-26 15:44:05+01:00, thek@adventure.(none) +1 -0
  Bug #32436 KILL QUERY completely deadlocks mysqld
  
  Sending several "KILL QUERY" statements to target a connection running
  "SELECT SLEEP" could freeze the server.
  
  The locking order in Item_func_sleep was wrong and this could lead to a
  dead lock.
  
  This patch solves the issue by resolving the locking order properly.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.