From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on dcvr.yhbt.net X-Spam-Level: X-Spam-ASN: X-Spam-Status: No, score=-4.0 required=3.0 tests=ALL_TRUSTED,BAYES_00 shortcircuit=no autolearn=ham autolearn_force=no version=3.4.2 Received: from localhost (dcvr.yhbt.net [127.0.0.1]) by dcvr.yhbt.net (Postfix) with ESMTP id A842F1F9F3 for ; Fri, 17 Sep 2021 01:56:44 +0000 (UTC) From: Eric Wong To: meta@public-inbox.org Subject: [PATCH v2 2/6] lei_mail_sync: don't hold statement handle into callback Date: Thu, 16 Sep 2021 20:56:40 -0500 Message-Id: <20210917015644.32746-3-e@80x24.org> In-Reply-To: <20210917015644.32746-1-e@80x24.org> References: <20210917015644.32746-1-e@80x24.org> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit List-Id: This can cause readers and writers to conflict since the implicit transaction from SELECT in a LeiRefreshMailSync worker would block the LeiStore process. --- lib/PublicInbox/LeiMailSync.pm | 41 ++++++++++++++++++++++++++-------- 1 file changed, 32 insertions(+), 9 deletions(-) diff --git a/lib/PublicInbox/LeiMailSync.pm b/lib/PublicInbox/LeiMailSync.pm index 5a10c127..8f584ccb 100644 --- a/lib/PublicInbox/LeiMailSync.pm +++ b/lib/PublicInbox/LeiMailSync.pm @@ -169,22 +169,45 @@ INSERT OR IGNORE INTO blob2name (oidbin, fid, name) VALUES (?, ?, ?) sub each_src { my ($self, $folder, $cb, @args) = @_; my $dbh = $self->{dbh} //= dbh_new($self); - my ($fid, $sth); + my $fid; if (ref($folder) eq 'HASH') { $fid = $folder->{fid} // die "BUG: no `fid'"; } else { $fid = $self->{fmap}->{$folder} //= fid_for($self, $folder) // return; } - $sth = $dbh->prepare('SELECT oidbin,uid FROM blob2num WHERE fid = ?'); - $sth->execute($fid); - while (my ($oidbin, $id) = $sth->fetchrow_array) { - $cb->($oidbin, $id, @args); + + # minimize implicit txn time to avoid blocking writers by + # batching SELECTs. This looks wonky but is necessary since + # $cb-> may access the DB on its own. + my $ary = $dbh->selectall_arrayref(<<'', undef, $fid); +SELECT _rowid_,oidbin,uid FROM blob2num WHERE fid = ? +ORDER BY _rowid_ ASC LIMIT 1000 + + my $min = @$ary ? $ary->[-1]->[0] : undef; + while (defined $min) { + for my $row (@$ary) { $cb->($row->[1], $row->[2], @args) } + + $ary = $dbh->selectall_arrayref(<<'', undef, $fid, $min); +SELECT _rowid_,oidbin,uid FROM blob2num WHERE fid = ? AND _rowid_ > ? +ORDER BY _rowid_ ASC LIMIT 1000 + + $min = @$ary ? $ary->[-1]->[0] : undef; } - $sth = $dbh->prepare('SELECT oidbin,name FROM blob2name WHERE fid = ?'); - $sth->execute($fid); - while (my ($oidbin, $id) = $sth->fetchrow_array) { - $cb->($oidbin, \$id, @args); + + $ary = $dbh->selectall_arrayref(<<'', undef, $fid); +SELECT _rowid_,oidbin,name FROM blob2name WHERE fid = ? +ORDER BY _rowid_ ASC LIMIT 1000 + + $min = @$ary ? $ary->[-1]->[0] : undef; + while (defined $min) { + for my $row (@$ary) { $cb->($row->[1], \($row->[2]), @args) } + + $ary = $dbh->selectall_arrayref(<<'', undef, $fid, $min); +SELECT _rowid_,oidbin,name FROM blob2name WHERE fid = ? AND _rowid_ > ? +ORDER BY _rowid_ ASC LIMIT 1000 + + $min = @$ary ? $ary->[-1]->[0] : undef; } }