Flutter使用sqflite遇数据库锁定警告，应用运行卡顿求解决方案

问题根源 #

你遇到的database has been locked警告和应用卡顿，核心原因如下：

• 直接访问_db静态实例而非通过await db获取，可能导致数据库未初始化完成就执行操作，或多线程竞争连接
• 数据库操作未用事务包裹，独立的查询、插入操作互相阻塞
• 异步代码嵌套过多（多层then），异步流程混乱，导致数据库连接未及时释放
• itemExists方法用字符串拼接SQL，存在注入风险，且逻辑有漏洞

修复方案 #

1. 重构DBHelper类 #

统一通过await db获取数据库实例，替换所有直接访问_db的代码，同时使用参数化查询，添加事务支持：

class DBHelper {
  static Database? _db;
  Future<Database?> get db async {
    if (_db != null) return _db;
    _db = await initDatabase();
    return _db;
  }  

  Future<Database> initDatabase() async {
    io.Directory documentDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentDirectory.path,"cart.db");
    var db = await openDatabase(path, version: 1, onCreate: _onCreate);
    return db;
  }

  Future<void> _onCreate(Database db, int version) async {
    await db.execute(
      'CREATE TABLE cart (id INTEGER PRIMARY KEY,user_id INTEGER ,item_id INTEGER,main_item_id INTEGER,name TEXT,price REAL, qty INTEGER, type TEXT, image TEXT, created_at TEXT, updated_at TEXT )'
    );
  }

  // 插入购物车项（带事务，先检查再插入）
  Future<getMyCart> insertWithCheck(getMyCart cart) async {
    var dbClient = await db;
    return await dbClient!.transaction((txn) async {
      // 参数化查询，避免SQL注入
      final result = await txn.rawQuery(
        'SELECT EXISTS(SELECT * FROM cart WHERE item_id = ?)',
        [cart.itemId]
      );
      int? exists = Sqflite.firstIntValue(result);
      if (exists != 1) {
        await txn.insert('cart', cart.toJson());
      }
      return cart;
    });
  }

  // 获取购物车列表
  Future<List<getMyCart>> getCartList() async {
    var dbClient = await db;
    final List<Map<String, Object?>> queryResult =
        await dbClient!.query('cart');
    return queryResult.map((e) => getMyCart.fromJson(e)).toList();
  }

  // 根据ID删除购物车项
  Future<int> delete(int? id) async {
    var dbClient = await db;
    return await dbClient!.delete('cart', where: "id = ?", whereArgs: [id]);
  }

  // 删除所有购物车项
  Future<void> deleteAll() async {
    var dbClient = await db;
    await dbClient!.delete('cart');
  }

  // 更新购物车项数量
  Future<int> updateQuantity(getMyCart cart) async {
    var dbClient = await db;
    return await dbClient!
        .update('cart', cart.toJson(), where: "id = ?", whereArgs: [cart.id]);
  }

  // 检查商品是否存在（统一用await db，参数化查询）
  Future<bool> itemExists(int? id) async {
    var dbClient = await db;
    final result = await dbClient!.rawQuery(
      'SELECT EXISTS(SELECT * FROM cart WHERE item_id = ?)',
      [id]
    );
    int? exists = Sqflite.firstIntValue(result);
    return exists == 1;
  }
}

2. 简化按钮点击的异步逻辑 #

用async/await替代嵌套then，减少异步流程混乱，同时使用重构后的DBHelper方法：

GestureDetector(
  onTap: () async {
    final targetItem = getcategoryList[selectedCategoryIndex]
        .subCategory![selectedSubcategoryIndex]
        .item![index];

    if (targetItem.addonAdded == 1) {
      itemCountMap.putIfAbsent(targetItem.id, () => 1);
      final onVal = await showBottomSheet(
        context,
        targetItem,
        itemCountMap[targetItem.id] == 0 ? 1 : itemCountMap[targetItem.id],
        false
      );

      if (onVal != null) {
        final cartItemList = await cart.getData();
        if (cartItemList.isNotEmpty) {
          setState(() {
            myCartList = cartItemList;
            itemCountMap[targetItem.id] = onVal;
            updateCount();
            isbuynowclicked = selecteditemindex;
            selecteditemindex = index;
          });
        }
      }

      if (itemCountMap[targetItem.id] == 0) {
        itemCountMap[targetItem.id] = 1;
      }
      buynowClicked[targetItem.id] = true;
      selecteditemindex = index;
      isbuynowclicked = selecteditemindex;
    } else {
      bool isItemExist = await dbHelper.itemExists(targetItem.id);
      if (!isiteminCart && !isItemExist) {
        final cartItem = my_cart_model.getMyCart(
          id: targetItem.id,
          userId: globalCurrentUserData.id,
          itemId: targetItem.id,
          name: targetItem.name,
          price: targetItem.offerPrice,
          qty: 1,
          type: targetItem.type,
          image: targetItem.image,
          createdAt: DateTime.now().toString(),
          updatedAt: DateTime.now().toString()
        );

        await dbHelper.insertWithCheck(cartItem);
        final cartItemList = await cart.getData();
        
        if (cartItemList.isNotEmpty) {
          setState(() {
            myCartList = cartItemList;
            final matchedItem = cartItemList.firstWhere(
              (item) => item.itemId == targetItem.id,
              orElse: () => cartItem
            );
            buynowClicked[targetItem.id] = true;
            selecteditemindex = index;
            itemCountMap[targetItem.id] = matchedItem.qty!;
            isbuynowclicked = selecteditemindex;
            updateCount();
          });
        }
      }
    }
  },
  child: (cartobj.itemId == targetItem.id)
      ? Padding(
          padding: const EdgeInsets.only(right: 5),
          child: counterContainer(
              index, targetItem, cartobj, cart),
        )
      : Container(
          width: 100.w,
          height: 40.h,
          decoration: BoxDecoration(
            color: Colors.white,
            borderRadius: BorderRadius.circular(10),
            border: Border.all(
              width: 1,
              color: const Color.fromRGBO(49, 164, 113, 1),
            ),
          ),
          child: Center(
            child: Text(
              buynowBtnText,
              style: GoogleFonts.metrophobic(
                fontSize: 13,
                fontWeight: FontWeight.w600,
                color: const Color.fromRGBO(49, 164, 113, 1),
              ),
            ),
          ),
        ),
)

关键优化点 #

• 所有数据库操作统一通过await db获取实例，避免直接访问_db导致的未初始化问题
• 把"检查存在+插入"的逻辑放到事务中，确保原子性，避免中间被其他操作打断导致锁
• 用async/await替换嵌套then，让异步流程更清晰，减少并发冲突
• 所有SQL查询使用参数化，避免注入风险，同时保证查询稳定性

内容的提问来源于stack exchange，提问作者Ganesh