diff options
author | Eric Wong <e@80x24.org> | 2021-09-16 20:56:40 -0500 |
---|---|---|
committer | Eric Wong <e@80x24.org> | 2021-09-17 04:41:04 +0000 |
commit | 1d874aad484aaa81a40e51b551924b441d560b06 (patch) | |
tree | 54530b6b7cef9b22b4407630b0664f4a5f021080 /lib/PublicInbox/LeiMailSync.pm | |
parent | 1323196d1df67db5cb47a171a60236049c1e5ce5 (diff) | |
download | public-inbox-1d874aad484aaa81a40e51b551924b441d560b06.tar.gz |
This can cause readers and writers to conflict since the implicit transaction from SELECT in a LeiRefreshMailSync worker would block the LeiStore process.
Diffstat (limited to 'lib/PublicInbox/LeiMailSync.pm')
-rw-r--r-- | lib/PublicInbox/LeiMailSync.pm | 41 |
1 files 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; } } |