Sqflite get locked on bulk insertion even after using transaction object and batch

Question

I am new to flutter and I am doing bulk insertion in sqflite database. I have tried using both transaction and batch objects but my issue still remains there and database gets locked.

Here is what i am doing.

Future<List<ShopsModel>> fetchShops() async{
    int count = 0;
    List<ShopsModel> shopsList = [];
    int id = 0;
    String date = "";
    List<SyncDataModel> syncList = await DatabaseHelper.instance.getSyncDataHistory();
    syncList.forEach((element) {
      id = element.SyncID!;
      date = element.ShopSyncDate == null ? "" : element.ShopSyncDate!;
    });
    //Info.startProgress();
    String response = await ApiServices.getMethodApi("${ApiUrls.IMPORT_SHOPS}?date=$date");

    if(response.isEmpty || response == null){
      return shopsList;
    }

    var shopsApiResponse = shopsApiResponseFromJson(response);

    if(shopsApiResponse.data != null){
      shopsApiResponse.data!.forEach((element) async{
        await insertShops(element);
        count++;
        if(count == 1){
          syncList.length == 0 ? await DatabaseHelper.instance.insertSyncDataHistory(
              SyncDataModel(
                  ShopSyncDate: DateFormat('yyyy-MM-dd HH:mm:ss').format(DateTime.now()),
                  LastSyncDate: DateFormat('yyyy-MM-dd HH:mm:ss').format(DateTime.now())
              )) :
          await DatabaseHelper.instance.updateShopSyncDate(
              DateFormat('yyyy-MM-dd HH:mm:ss').format(DateTime.now()), id);
        }
      });
    }
    return shopsList;
  }

  Future insertShops(ShopsModel row) async{
    var shopsRow = await DatabaseHelper.instance.getShopByShopId(row.shopID!);
    if(shopsRow.IsModify == 0 || shopsRow.IsModify == null) {
      var result = await DatabaseHelper.instance.deleteImportedShops(
          row.shopID!, DateFormat('yyyy-MM-dd HH:mm:ss').format(
          DateTime.parse(row.updatedOn!)));
      if (result > 0) {
        print('Shop has been deleted');
      }
      await DatabaseHelper.instance.insertShops(
          ShopsModel(
              shopID: row.shopID,
              shopName: row.shopName,
              shopCode: row.shopCode,
              contactPerson: row.contactPerson,
              contactNo: row.contactNo,
              nTNNO: row.nTNNO,
              regionID: row.regionID,
              areaID: row.areaID,
              salePersonID: row.salePersonID,
              createdByID: row.createdByID,
              updatedByID: row.updatedByID,
              systemNotes: row.systemNotes,
              remarks: row.remarks,
              description: row.description,
              entryDate: DateFormat('yyyy-MM-dd HH:mm:ss').format(
                  DateTime.parse(row.entryDate!)),
              branchID: row.branchID,
              longitiude: row.longitiude,
              latitiude: row.latitiude,
              googleAddress: row.googleAddress,
              createdOn: DateFormat('yyyy-MM-dd HH:mm:ss').format(
                  DateTime.parse(row.createdOn!)),
              updatedOn: DateFormat('yyyy-MM-dd HH:mm:ss').format(
                  DateTime.parse(row.updatedOn!)),
              tradeChannelID: row.tradeChannelID,
              route: row.route,
              vPO: row.vPO,
              sEO: row.sEO,
              imageUrl: row.imageUrl,
              IsModify: 0
          )
      );
    }
  }

  // Below are my Database methods

  Future<int> deleteImportedShops(int shopID, String updatedDate) async{
    Database db = await instance.database;
    return await db.delete("$shopsTable", where: 'ShopID = ? AND UpdatedOn <= ?', whereArgs: [shopID, updatedDate]);
  }

  Future<void> insertShops(ShopsModel shopsRow) async{
    Database db = await instance.database;
    await db.transaction((txn) async {
      var batch = txn.batch();
      batch.insert("$shopsTable", shopsRow.toJson(), conflictAlgorithm: ConflictAlgorithm.replace);
      await batch.commit();
    });
  }

  Future<void> insertSyncDataHistory(SyncDataModel row) async{
    Database db = await instance.database;
    await db.transaction((txn) async {
      var batch = txn.batch();
      batch.insert("$syncDataTable", row.toJson(), conflictAlgorithm: ConflictAlgorithm.replace);
      await batch.commit();
    });
  }

  Future<void> updateShopSyncDate(String? pDate, int id) async{
    Database db = await instance.database;
    await db.transaction((txn) async {
      var batch = txn.batch();
      batch.rawUpdate("UPDATE SyncDataHistory SET ShopSyncDate = ?, LastSyncDate = ? WHERE SyncID = ?", [pDate, pDate, id]);
      await batch.commit();
    });
  }

Here are the details what I am getting as an output. Warning database has been locked for 0:00:10.000000. Make sure you always use the transaction object for database operations during a transaction

Please help me out. Any help would be appreciated.

Answer 1

I'm investigating on this problem, and I found that the sqflite library have some problems on concurrency (in general):

https://github.com/tekartik/sqflite/issues/988

The issue says that the database is entirely locked when you make a bach.insert(...)

One bad news is that the app can crash, and the other is that the issue is still opened.

Thus, no solution right now!

I'm using :

sqflite: ^2.2.8