BucketStorage.select() uses execute() instead of getAll(), taking writeLock for read-only queries

[friebetill]

Description

BucketStorage.select() in powersync_core calls _internalDb.execute() instead of _internalDb.getAll(). The comment says "Use only for read statements", but execute() routes through SqliteQueries.execute() which acquires a write lock (writeLock()), while getAll() only acquires a read lock (readLock()).

File: packages/powersync_core/lib/src/sync/bucket_storage.dart, lines 30-34

// Use only for read statements
Future<ResultSet> select(String query,
    [List<Object?> parameters = const []]) async {
  return await _internalDb.execute(query, parameters);  // ← writeLock
}

Fix:

return await _internalDb.getAll(query, parameters);  // ← readLock

Impact

On SyncSqliteConnection, both readLock() and writeLock() use the same shared cross-isolate mutex, so this change alone doesn't fix the contention there. However, on the SqliteConnectionPool (used when BucketStorage wraps the main database on web), execute() takes the global write mutex while getAll() uses per-connection read mutexes — so this is a real concurrency bug on web and in tests.

Context

We discovered this while investigating a ~3-minute read blockage on the congratulation screen of our Flutter app. After a learning session, all Drift .watch() queries and one-shot reads are blocked while PowerSync uploads CRUD data and processes the sync stream. The sync isolate holds the shared mutex for extended periods, which blocks all main-isolate reads.

The BucketStorage.select() issue is one contributing factor. The larger issue appears to be that SyncSqliteConnection uses a single shared mutex for both reads and writes, and this mutex is shared cross-isolate with the main database's write connection. During sync processing (both upload coordination and download data ingestion), this mutex is held for long periods, effectively serializing all database access.

Environment

• powersync: 1.18.0
• powersync_core: 1.8.0
• sqlite_async: 0.13.1
• Flutter 3.41
• Platform: macOS (also affects iOS/Android)

[simolus3]

but execute() routes through SqliteQueries.execute() which acquires a write lock

This is true, and I believe it is a bug. But I don't think it's contributing to the 3min query lock here.

1. By default, the Dart SDK uses a sync client implemented in Rust. In this mode, control is the only method on BucketStorage that would actually get used.
2. I looked at invocations of select, and I don't see how any of them could take long to complete. The only expensive method is updateObjectsFromBuckets, which uses a separate write transaction.
3. Even while the write lock is held, independent read queries can progress.

When syncing a lot of data (way above 1 million rows), it's possible for the sync local step (updateObjectsFromBuckets or a control invocation) of the sync client to take a while. 3 minutes is a lot, but not unheard of for very large data sets.

But still, the write lock would only block other writes. Because we enable WAL mode, we allow reads to progress while a write transaction is active. This should include drift queries and streams as well.

The larger issue appears to be that SyncSqliteConnection uses a single shared mutex for both reads and writes

SyncSqliteConnection is part of how we access SQLite databases, but your app would use a SqliteConnectionPool wrapping a write connection and multiple readers by default. That pool allows handing our write and read connections concurrently.

Can you reproduce this delay consistently? Even without the sync client being involved, if any writer can block reads than that is a bug in sqlite_async we should look at.