skip to content

A Flutter app using sqflite hangs on writes or throws 'database is locked' on Android; what usually causes it, and how should database access be structured?

level: seniorimportance: should knowfreq 30%

answer

  1. open once, keep it open
  2. singleInstance defaults to true
  3. cache the open Future, not the Database
  4. db inside a transaction
  5. background isolates and close()

basics

~20 s

Usually the same file is opened several times with singleInstance: false, db is used instead of txn inside a transaction, or another isolate holds or closes the connection. Open one Database for the app lifetime through a cached Future and keep transactions short.

solid answer

~50 s

sqflite expects one connection per file for the app's lifetime. `openDatabase` defaults to `singleInstance: true`, returning the same instance for a path, but opening with `singleInstance: false` gives separate connections that contend for the file lock — the source of `SQLiteDatabaseLockedException: database is locked` on Android. A hang, by contrast, is typically a `db` call inside a `transaction` callback, which waits on the transaction's own lock; sqflite prints a warning after 10 seconds. I expose one lazily opened database through a cached `Future<Database>` — `_db ??= openLedger()` — so every early caller shares one open and any setup after it runs once, never close it in a widget's `dispose`, keep transactions free of network calls, and treat access from a background isolate as a special case: follow the package's advice for that path and never close the database from there.

code

dart · 25 lines
dart
import 'package:sqflite/sqflite.dart';

Future<Database> openLedger() => openDatabase('ledger.db', version: 2);

class LedgerDb {
  LedgerDb._();
  static final instance = LedgerDb._();

  Future<Database>? _db;

  /// Every caller awaits the same open, migrations included.
  Future<Database> get database => _db ??= openLedger();
}

Future<void> rename(String from, String to) async {
  final db = await LedgerDb.instance.database;
  await db.transaction((txn) async {
    await txn.update(
      'expenses',
      {'category': to},
      where: 'category = ?',
      whereArgs: [from],
    ); // txn inside, never db
  });
}

go deeper

for a junior

Know that a sqflite database should be opened once and reused, not opened and closed per screen.

for a middle

Explain singleInstance, the cached-Future open pattern, and why db inside a transaction deadlocks.

for a senior

Diagnose lock warnings and locked exceptions to their cause, and make background-isolate access safe or avoid it.

for a principal

Own the data-access boundary: one service for the database, clear rules for isolates, and reviews that catch stray connections.

## The model sqflite expects The package's own recommendations are to **open the database once** on first use and keep it open, much like a single global connection. SQLite uses file locks; sqflite adds its own lock around transactions. Problems appear when code breaks that model. ## Cause 1: several connections to one file `openDatabase(path, singleInstance: true)` is the default: a second call with the same path returns the **same** `Database` and ignores the second call's callbacks. With `singleInstance: false`, each call opens a new connection. Two connections writing at once is what produces, at least on Android: ``` android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5) ``` On the web implementation, a second connection to a persistent file with `singleInstance: false` is refused with an `ArgumentError`, because there is no locking between connections. ## Cause 2: the open race A helper that caches the `Database` only after awaiting the open — - `if (_db == null) _db = await openDatabase(path);` — lets two callers that arrive before the first open completes both go down the open path. sqflite serialises opens per path, and with the default `singleInstance: true` the second call gets the same instance, so migrations still run once; but any setup the helper does after opening runs twice, and with `singleInstance: false` the race creates two connections. The package's recommended fix is to cache the **`Future`**: `_db ??= openLedger();`, so every caller awaits one open and one setup. ## Cause 3: the transaction self-deadlock Inside `db.transaction((txn) async { ... })`, any call on `db` instead of `txn` waits for the lock the transaction itself holds. Nothing throws; the app just stops writing. sqflite prints "Warning database has been locked for 0:00:10.000000. Make sure you always use the transaction object for database operations during a transaction" after its default 10-second threshold. Long transactions that await network calls produce the same symptom for everyone else. ## Cause 4: isolates and closing sqflite's native work already runs off the UI thread, and the package recommends using it from the main isolate; its transaction mechanism is not cross-isolate safe. When a background isolate — a push handler or scheduled task — must touch the database: - follow the package's usage notes for multi-isolate access (they suggest `singleInstance: false` in both isolates and never closing); - **never close the database from the background isolate**, which can close it for every isolate; - keep that access to small, short writes. Closing in a widget's `dispose` is a related mistake: another screen may still hold the same single instance. ## A structure that avoids all four 1. One repository or service owns the database, created at app start or on first use. 2. It exposes `Future<Database> get database => _db ??= openLedger();`. 3. Every multi-statement write is a short `transaction` using `txn`, or a `batch`. 4. No `close()` except on app shutdown or in tests. 5. Hot restart during a transaction can leave a lock behind in debug; the `rollbackActiveTransactionOnOpen` option exists for that and defaults to on in debug builds only. ## How to diagnose | Symptom | Likely cause | |---|---| | Hang plus a lock warning after 10 s | `db` used inside a transaction, or a very long transaction | | `database is locked (code 5)` on Android | multiple connections via `singleInstance: false` | | Post-open setup (seeding, pragmas) running twice | the `Database` cached only after `await` | | `DatabaseException` saying the database is closed | a widget or isolate closed the shared instance | For the expense ledger, a single `LedgerDb` service opened lazily through a cached future, with transactions around each multi-table write, removes every row of that table.

  • Why cache Future<Database> instead of Database?
    Opening is asynchronous. If you store the `Database` only after awaiting, two callers arriving early both run the open path: sqflite serialises opens per path and returns the same instance by default, but setup your helper runs after opening executes twice, and with `singleInstance: false` you get two connections. Storing the `Future` at once means one open, one setup.
  • What does singleInstance: true do with the callbacks of a second openDatabase call?
    It returns the already-open instance for that path and discards the second call's parameters, callbacks included. A second call with a different version or onUpgrade does nothing, which is another reason to open in one place only.

saying these in an interview costs you the question

  • Open the database in each screen and close it in dispose.
  • Pass singleInstance: false everywhere to allow more concurrency.
  • A hang inside a transaction means SQLite itself is too slow.
  • Caching the Database after await guarantees post-open setup runs once.
  • Closing the database from a background isolate only affects that isolate.