Bug 912564: tbpl gets deadlocks setting last_activity_ts

2 files changed, 114 insertions, 8 deletions

diff --git a/extensions/UserProfile/Extension.pm b/extensions/UserProfile/Extension.pm
index c0b22b8c5..22cc91d18 100644
--- a/extensions/UserProfile/Extension.pm
+++ b/extensions/UserProfile/Extension.pm
@@ -119,20 +119,57 @@ sub _bug_touched {
     $dbh->bz_start_transaction();
 
     # update user's last_activity_ts
-    $user->set_last_activity_ts($args->{timestamp});
+    eval {
+        $user->set_last_activity_ts($args->{timestamp});
+        $self->_recalc_remove($user);
+    };
+    if ($@) {
+        warn $@;
+        $self->_recalc_insert($user);
+    }
 
     # clear the last_statistics_ts for assignee/qa-contact to force a recount
     # at the next poll
     if ($assigned_to) {
-        $assigned_to->clear_last_statistics_ts();
+        eval {
+            $assigned_to->clear_last_statistics_ts();
+            $self->_recalc_remove($assigned_to);
+        };
+        if ($@) {
+            warn $@;
+            $self->_recalc_insert($assigned_to);
+        }
     }
     if ($qa_contact) {
-        $qa_contact->clear_last_statistics_ts();
+        eval {
+            $qa_contact->clear_last_statistics_ts();
+            $self->_recalc_remove($qa_contact);
+        };
+        if ($@) {
+            warn $@;
+            $self->_recalc_insert($qa_contact);
+        }
     }
 
     $dbh->bz_commit_transaction();
 }
 
+sub _recalc_insert {
+    my ($self, $user) = @_;
+    Bugzilla->dbh->do(
+        "INSERT IGNORE INTO profiles_statistics_recalc SET user_id=?",
+        undef, $user->id
+    );
+}
+
+sub _recalc_remove {
+    my ($self, $user) = @_;
+    Bugzilla->dbh->do(
+        "DELETE FROM profiles_statistics_recalc WHERE user_id=?",
+        undef, $user->id
+    );
+}
+
 sub object_end_of_create {
     my ($self, $args) = @_;
     $self->_object_touched($args);
@@ -153,8 +190,14 @@ sub _object_touched {
         # if an attachment is created or updated, that counts as user activity
         my $user = Bugzilla->user;
         my $timestamp = Bugzilla->dbh->selectrow_array('SELECT LOCALTIMESTAMP(0)');
-        $user->set_last_activity_ts($timestamp);
-        $user->update();
+        eval {
+            $user->set_last_activity_ts($timestamp);
+            $self->_recalc_remove($user);
+        };
+        if ($@) {
+            warn $@;
+            $self->_recalc_insert($user);
+        }
     }
     elsif ($object->isa('Bugzilla::Product') && exists $args->{changes}->{name}) {
         # if a product is renamed by an admin, rename in the
@@ -440,6 +483,25 @@ sub db_schema_abstract_schema {
             },
         ],
     };
+    $args->{'schema'}->{'profiles_statistics_recalc'} = {
+        FIELDS => [
+            user_id => {
+                TYPE    => 'INT3',
+                NOTNULL => 1,
+                REFERENCES => {
+                    TABLE  => 'profiles',
+                    COLUMN => 'userid',
+                    DELETE => 'CASCADE',
+                }
+            },
+        ],
+        INDEXES => [
+            profiles_statistics_recalc_idx => {
+                FIELDS => [ 'user_id' ],
+                TYPE => 'UNIQUE',
+            },
+        ],
+    };
 }
 
 sub install_update_db {
diff --git a/extensions/UserProfile/bin/update.pl b/extensions/UserProfile/bin/update.pl
index 4cdb08fe7..457585f8d 100755
--- a/extensions/UserProfile/bin/update.pl
+++ b/extensions/UserProfile/bin/update.pl
@@ -18,10 +18,45 @@ BEGIN { Bugzilla->extensions() }
 
 use Bugzilla::Constants;
 use Bugzilla::Extension::UserProfile::Util;
+use Bugzilla::User;
 
 Bugzilla->usage_mode(USAGE_MODE_CMDLINE);
+my $dbh = Bugzilla->dbh;
+my $user_ids;
+my $verbose = grep { $_ eq '-v' } @ARGV;
 
-my $user_ids = Bugzilla->dbh->selectcol_arrayref(
+$user_ids = $dbh->selectcol_arrayref(
+    "SELECT user_id
+       FROM profiles_statistics_recalc
+      ORDER BY user_id",
+    { Slice => {} }
+);
+
+if (@$user_ids) {
+    print "recalculating last_user_activity\n";
+    my ($count, $total) = (0, scalar(@$user_ids));
+    foreach my $user_id (@$user_ids) {
+        if ($verbose) {
+            $count++;
+            my $login = user_id_to_login($user_id);
+            print "$count/$total $login ($user_id)\n";
+        }
+        $dbh->do(
+            "UPDATE profiles
+                SET last_activity_ts = ?,
+                    last_statistics_ts = NULL
+            WHERE userid = ?",
+            undef,
+            last_user_activity($user_id),
+            $user_id
+        );
+    }
+    $dbh->do(
+        "DELETE FROM profiles_statistics_recalc WHERE " . $dbh->sql_in('user_id', $user_ids)
+    );
+}
+
+$user_ids = $dbh->selectcol_arrayref(
     "SELECT userid
        FROM profiles
       WHERE last_activity_ts IS NOT NULL
@@ -31,6 +66,15 @@ my $user_ids = Bugzilla->dbh->selectcol_arrayref(
     { Slice => {} }
 );
 
-foreach my $user_id (@$user_ids) {
-    update_statistics_by_user($user_id);
+if (@$user_ids) {
+    $verbose && print "updating statistics\n";
+    my ($count, $total) = (0, scalar(@$user_ids));
+    foreach my $user_id (@$user_ids) {
+        if ($verbose) {
+            $count++;
+            my $login = user_id_to_login($user_id);
+            print "$count/$total $login ($user_id)\n";
+        }
+        update_statistics_by_user($user_id);
+    }
 }