Flutter sqflite Database Lock Error Fix

DodaTech Updated 2026-06-24 4 min read

In this tutorial, you'll learn about Flutter sqflite Database Lock Error Fix. We cover key concepts, practical examples, and best practices.

Your Flutter app crashes with DatabaseException: database is locked or SQL logic error — multiple threads or isolates are trying to write to the SQLite database simultaneously.

Step-by-Step Fix

1. Use a single database instance

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

// Wrong: opening new database connections for each operation
class DatabaseService {
  Future<Database> getDatabase() async {
    return openDatabase(
      join(await getDatabasesPath(), 'app.db'),
    );
  }

  Future<void> saveItem(Item item) async {
    final db = await getDatabase(); // New connection every time
    await db.insert('items', item.toMap());
    await db.close();
  }
}

// Right: use a singleton database instance
class DatabaseService {
  static Database? _database;

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    return openDatabase(
      join(await getDatabasesPath(), 'app.db'),
      onCreate: (db, version) {
        return db.execute(
          'CREATE TABLE items(id INTEGER PRIMARY KEY, name TEXT, value TEXT)',
        );
      },
      version: 1,
    );
  }

  Future<void> saveItem(Item item) async {
    final db = await database; // Reuses the singleton
    await db.insert('items', item.toMap(),
        conflictAlgorithm: ConflictAlgorithm.replace);
  }
}

2. Use transactions for batch operations

// Wrong: multiple sequential inserts (slower, more locking)
Future<void> saveItems(List<Item> items) async {
  final db = await database;
  for (final item in items) {
    await db.insert('items', item.toMap());
  }
}

// Right: wrap in a single transaction
Future<void> saveItems(List<Item> items) async {
  final db = await database;
  await db.transaction((txn) async {
    for (final item in items) {
      await txn.insert('items', item.toMap());
    }
  });
}

3. Handle concurrent access with batch

// Wrong: multiple simultaneous writes from different isolates
Future<void> updateData() async {
  final db = await database;
  await Future.wait([
    db.update('items', {'value': 'a'}, where: 'id = 1'),
    db.update('items', {'value': 'b'}, where: 'id = 2'),
    db.update('items', {'value': 'c'}, where: 'id = 3'),
  ]);
}

// Right: use batch for atomic writes
Future<void> updateData() async {
  final db = await database;
  final batch = db.batch();
  batch.update('items', {'value': 'a'}, where: 'id = 1');
  batch.update('items', {'value': 'b'}, where: 'id = 2');
  batch.update('items', {'value': 'c'}, where: 'id = 3');
  await batch.commit(noResult: true);
}

4. Add database open timeout

Future<Database> _initDatabase() async {
  return openDatabase(
    join(await getDatabasesPath(), 'app.db'),
    onCreate: (db, version) {
      return db.execute(
        'CREATE TABLE items(id INTEGER PRIMARY KEY, name TEXT)',
      );
    },
    version: 1,
    // Increase timeout to handle concurrent access
    singleInstance: true,
  );
}

5. Use composed primary key to avoid conflicts

// Wrong: auto-increment ID can cause unique constraint failures
await db.execute('''
  CREATE TABLE items(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    value TEXT
  )
''');

// Right: use composed key for conflict-free inserts
await db.execute('''
  CREATE TABLE items(
    user_id TEXT NOT NULL,
    item_id TEXT NOT NULL,
    name TEXT,
    value TEXT,
    PRIMARY KEY (user_id, item_id)
  )
''');

6. Close database properly

class DatabaseService {
  static Database? _database;

  Future<void> close() async {
    if (_database != null) {
      await _database!.close();
      _database = null;
    }
  }

  // Call close when the app terminates
  static Future<void> cleanup() async {
    final service = DatabaseService();
    await service.close();
  }
}

// In main.dart
void main() async {
  WidgetsFlutterBinding.ensureInitialized();
  runApp(MyApp());
  // Close database when app is terminated
  AppLifecycleListener(onExit: DatabaseService.cleanup);
}

Prevention

Use a singleton database instance opened once per app lifecycle.
Always wrap batch writes in transactions for atomicity.
Use batch operations for multiple writes to reduce lock contention.
Set singleInstance: true when opening the database.
Close the database when the app is terminated.

Common Mistakes with sqlite error

Using foldl instead of foldl' causing stack overflow on large lists
Forgetting deriving (Show, Eq) on custom data types needed for debugging
Placing the wildcard pattern first in case expressions, making all subsequent patterns unreachable

These mistakes appear frequently in real-world FLUTTER code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ

Why does sqflite throw "database is locked"?

SQLite uses file-level locking for writes. If multiple connections try to write simultaneously, the database file becomes locked. Use a single instance and transactions to avoid this. |||Can I use sqflite with multiple isolates? Yes, but each isolate needs its own database connection. Use sqflite_common_ffi for multi-isolate support. Ensure only one isolate writes at a time to avoid locking. |||What is the difference between batch and transaction? A batch groups operations but does not guarantee atomicity across platforms. A transaction offers full ACID guarantees — all operations succeed or all fail. Use transactions for critical data.

← Previous Flutter pub get Error Fix Next → Flutter State Management Provider Error Fix

Built by the developers of DodaTech

Doda Browser, DodaZIP & Durga Antivirus Pro

Home Browse Quick Fix