"Warning database has locked“warning with SQFlite with code stops。为什么不能查询表？

Question

在Flutter中查询SQFlite db表时遇到问题。我多次收到以下警告：

I/chatty  (32047): uid=10160(com.example.SQFLite_test) 1.ui identical 18498 lines 2
I/flutter (32047): Warning database has been locked for 0:00:10.000000. Make sure you always use the transaction 
object for database operations during a transaction

在调用getClients()方法从Client表中获取所有客户端时，我收到警告。不过，主要的问题是它似乎也冻结了代码。即使我只尝试选择前100个，它仍然会给我警告，它会冻结，不会前进。我的db类帮助我初始化和管理数据库：

class LogServiceTwo {
  LogServiceTwo._();

  static final LogServiceTwo logRepo = LogServiceTwo._();

  static Database _database;

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

    var db = await openDb();
    // if (db == null) {
    //   _database = await initDB();
    //   return _database;
    // }

    var hasClientTableB = await hasClientTable(db);
    if (hasClientTableB) {
      _database = db;
      return _database;
    }

    // var backup = await restoreBackup(db);
    // if (backup != null) {
    //   _database = backup;
    //   return _database;
    // }

    await createClientTable(db);
    _database = db;
    return _database;
  }

  Future createClientTable(Database db) async {
    await db.execute("CREATE TABLE Client ("
        "id INTEGER PRIMARY KEY,"
        "first_name TEXT,"
        "last_name TEXT,"
        "blocked BIT"
        ")");
  }

  Future<bool> hasClientTable(Database db) async {
    try {
      var table = await db.query("Client");
      return table != null;
    } catch (e) {
      return false;
    }
  }

  Future<Database> openDb() async {
    try {
      var path = await getPersistentDbPath();
      var db = await openDatabase(path, version: 1);
      return db;
    } catch (e) {
      return null;
    }
  }

  Future initDB() async {
    var path = await getPersistentDbPath();
    return await openDatabase(path, version: 1, onOpen: (db) {}, onCreate: (Database db, int version) async {
      await db.execute("CREATE TABLE Client ("
          "id INTEGER PRIMARY KEY,"
          "first_name TEXT,"
          "last_name TEXT,"
          "blocked BIT"
          ")");
    });
  }

  Future newClient(Client newClient) async {
    final db = await database;
    var res = await db.insert("Client", newClient.toMap());
    return res;
  }

  Future newClients(List<Client> clients) async {
    var clientMaps = clients.map((client) => client.toMap()).toList();
    final db = await database;
    clientMaps.forEach((clientMap) async {
      await db.insert("Client", clientMap);
    });
  }

  Future<Client> getClient(int id) async {
    final db = await database;
    var res = await db.query("Client", where: "id = ?", whereArgs: [id]);
    return res.isNotEmpty ? Client.fromMap(res.first) : Null;
  }

  Future<List<Client>> getAllClients() async {
    final db = await database;
    var res = await db.query("Client");
    List<Client> list = res.isNotEmpty ? res.map((c) => Client.fromMap(c)).toList() : [];
    return list;
  }

  Future<List<Client>> getBlockedClients() async {
    final db = await logRepo.database;
    var res = await db.rawQuery("SELECT * FROM Client WHERE blocked=1");
    List<Client> list = res.isNotEmpty ? res.toList().map((c) => Client.fromMap(c)) : null;
    return list;
  }

  Future<List<String>> getTables() async {
    var db = await logRepo.database;
    var tableNames = (await db.query('sqlite_master', where: 'type = ?', whereArgs: ['table'])).map((row) => row['name'] as String).toList(growable: false);
    return tableNames;
  }

  Future<String> getPersistentDbPath() async {
    return await createPersistentDbDirecotry();
  }

  Future createPersistentDbDirecotry() async {
    var externalDirectoryPath = await ExtStorage.getExternalStorageDirectory();
    var persistentDirectory = "$externalDirectoryPath/db_persistent";
    await createDirectory(persistentDirectory);
    return "$persistentDirectory/persistent.db";
  }

  Future createDirectory(String path) async {
    await (new Directory(path).create());
  }

  Future<bool> askForWritePermission() async {
    var status = await Permission.storage.status;
    if (!status.isGranted) {
      status = await Permission.storage.request();
      return status.isGranted;
    }
    return status.isGranted;
  }

  Future mockData() async {
    var clients = ClientMocker.createClients();
    await newClients(clients);
  }

  Future deleteAll() async {
    var db = await database;
    await db.rawDelete("DELETE FROM Client");
  }

  // Get all clients, throws warnings and stops proceeding in the code.
  Future getClients() async {
    try {
      var db = await database;
      return await db.rawQuery("SELECT * FROM Client");
    } catch (e) {
      print(e);
    }
  }
}

出于测试目的，我的主类调用数据库服务：

void main() {
  runApp(MyApp());
}

class MyApp extends StatelessWidget {
  // This widget is the root of your application.
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Demo',
      theme: ThemeData(
        // This is the theme of your application.
        //
        // Try running your application with "flutter run". You'll see the
        // application has a blue toolbar. Then, without quitting the app, try
        // changing the primarySwatch below to Colors.green and then invoke
        // "hot reload" (press "r" in the console where you ran "flutter run",
        // or simply save your changes to "hot reload" in a Flutter IDE).
        // Notice that the counter didn't reset back to zero; the application
        // is not restarted.
        primarySwatch: Colors.blue,
        // This makes the visual density adapt to the platform that you run
        // the app on. For desktop platforms, the controls will be smaller and
        // closer together (more dense) than on mobile platforms.
        visualDensity: VisualDensity.adaptivePlatformDensity,
      ),
      home: MyHomePage(title: 'Flutter Demo Home Page'),
    );
  }
}

class MyHomePage extends StatefulWidget {
  MyHomePage({Key key, this.title}) : super(key: key);

  // This widget is the home page of your application. It is stateful, meaning
  // that it has a State object (defined below) that contains fields that affect
  // how it looks.

  // This class is the configuration for the state. It holds the values (in this
  // case the title) provided by the parent (in this case the App widget) and
  // used by the build method of the State. Fields in a Widget subclass are
  // always marked "final".

  final String title;

  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  @override
  void initState() {
    // firstTest();
    // secondTest();
    thirdTest();
    testText = "";
    super.initState();
  }

  String testText;

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text(widget.title),
      ),
      body: Center(
        // Center is a layout widget. It takes a single child and positions it
        // in the middle of the parent.
        child: Column(
          children: [
            Container(
              width: MediaQuery.of(context).size.width,
              height: MediaQuery.of(context).size.height,
              child: Text(testText),
            )
          ],
        ),
      ),
    );
  }

  Future firstTest() async {
    testText = "";
    // var client = new Client(blocked: false, firstName: "Tobias", lastName: "Eliasson", id: null);
    // await LogRepository.logRepo.newClient(client);
    // await LogRepository.logRepo.newClient(client);
    // await LogRepository.logRepo.newClient(client);
    var clients = await LogRepository.logRepo.getAllClients();
    clients.forEach((c) {
      setState(() {
        testText += "\n${c.toMap()}";
      });
      print(c.toMap());
    });
    setState(() {
      testText += "Length of found clients: ${clients.length.toString()}";
    });

    var success = await LogRepository.logRepo.saveBackup();
    print(success);
  }

  Future secondTest() async {
    try {
      await LogRepository.logRepo.deleteAll();
      await LogRepository.logRepo.mockData();
      var a = DateTime.now();
      print("Saving backup $a");
      var backupSuccess = await LogRepository.logRepo.saveBackup();
      print("Backup success: $backupSuccess");
      var b = DateTime.now();
      print("Saved backup:${a.difference(b)}");
    } catch (e) {
      print("Error!!!");
      print(e);
    }
  }

  Future thirdTest() async {
    await LogServiceTwo.logRepo.database;
    await LogServiceTwo.logRepo.mockData();
    var clients = await LogServiceTwo.logRepo.getClients();
    print(clients.length);
  }
}

据我所知，我等待所有的db操作，并且只使用on db object来访问它，所以应该不会有任何奇怪的并行访问。也许你会发现我遗漏了什么地方的错误。如果你想知道我为什么在外部存储器中创建数据库，是因为在卸载或更新应用程序时，数据库需要持久化和保存。

谢谢!

Answer 1

我发现了问题并吸取了教训。简而言之，在访问数据库时遵循约定和最佳实践。问题是我一次在方法中插入了一个客户端：

  Future newClients(List<Client> clients) async {
    var clientMaps = clients.map((client) => client.toMap()).toList();
    final db = await database;
    clientMaps.forEach((clientMap) async {
      await db.insert("Client", clientMap);
    });
  }

当同时插入或执行多个数据库操作时，请使用batch和commit，如下所示：

 Future newClients(List<Client> clients) async {
    var clientMaps = clients.map((client) => client.toMap()).toList();
    final db = await database;
    var batch = db.batch();
    clientMaps.forEach((clientMap) async {
      batch.insert("Client", clientMap);
    });
    await batch.commit(noResult: true);
 }

使用批处理和提交解决方案，我可以在第一次尝试时插入90000个客户端，而不会出现错误，并且可以在每次尝试后都进行查询。