Concurrency support

[silverAndroid]

I'd like to help with enabling concurrency support for Sqflite because I know it would benefit a lot from it but I'm having trouble disabling the locks that prevent it from occurring. So far, I've only commented out the return synchronized from rawQuery as a test but for some reason I'm still getting the queries executed sequentially on my Android device even when the queries are surrounded by

List<Future> futures = new List.generate(
      10,
      (int i) => db.query('test$i'),
    );

    await Future.wait(futures, eagerError: true);

as detailed by the logs

12-19 13:02:11.724 23632-23666/com.terkartik.sqflite_example D/Sqflite: [Thread[Sqflite,5,main]] SELECT * FROM test8
12-19 13:02:11.731 23632-23666/com.terkartik.sqflite_example D/Sqflite: {name=item 0, id=1}
12-19 13:02:11.731 23632-23666/com.terkartik.sqflite_example D/Sqflite: {name=item 1, id=2}
12-19 13:02:11.978 23632-23666/com.terkartik.sqflite_example D/Sqflite: {name=item 4999, id=5000}
12-19 13:02:11.978 23632-23666/com.terkartik.sqflite_example D/Sqflite: [Thread[Sqflite,5,main]] SELECT * FROM test9
12-19 13:02:11.986 23632-23666/com.terkartik.sqflite_example D/Sqflite: {name=item 0, id=1}
12-19 13:02:11.986 23632-23666/com.terkartik.sqflite_example D/Sqflite: {name=item 1, id=2}
12-19 13:02:12.173 23632-23666/com.terkartik.sqflite_example D/Sqflite: {name=item 4999, id=5000}

which show that there's a 200ms delay for when the 1st and 2nd query start which shouldn't happen if the queries run concurrently (unless there's something I don't understand about Future.wait.

[alextekartik]

Indeed the synchronized was the first thing to remove. Unfortunately that is not the only issue. I made a lot of experiments with concurrent access on both iOS and Android and at some point I gave up as I could not managed to get it to work on both Platform. As far as I know the flutter plugin mechanism always call native implementation in the same thread (if i remember well the main thread).

SQLite native access is synchronous in the two implementations I chose. SQLite native concurrent access would mean multiple threads, a concept we don't have in dart

To prevent locking this main thread, I already made all the SQLite calls in a separate thread (one per database). To go further, the solution would be to implement a thread pool for each database or a global pool. For write operations the solution is definitely to have transaction. And if we use a pool we need to make sure all calls in a transaction are made in the same thread.

So yes I know part of what needs (or least could) be done to improve concurrency. If you have test data to look easily at numbers, that would help. If you need something right now, I'm afraid I won't be able to do much before the end of the year but i can give a try. Your input is valuable as well, some optimization might require specific native calls.

[silverAndroid]

Could you explain what you mean by test data, like code that you could use to test with?

[alextekartik]

That would even better. But if you have a 10000+ complex database and way to report some logs easily that could be helpful (such as what you provided above)

[silverAndroid]

I have a repository which you can use to test from but I'll need a bit of time to prepare the UI and the models. However if you'd like to take a look, it's at https://github.com/silverAndroid/flutter_transit

[silverAndroid]

Although the database queries are there, the UI is the only part not ready for the concurrent queries. Also, all the setup instructions are on the repo, and to reach the page, search for a stop, and click on any list item.

[alextekartik]

I was wrong in some of my assumptions above: https://www.sqlite.org/isolation.html Currently on Android, as we have only one connection to the database, there is no isolation so any thread can get the data from a transaction in progress even if a transaction is not finished so the existing 'synchronized' solution is a fair enough solution for consistent access and so far the recommendation is to use sqlite within a single thread (https://medium.com/inloop/transactions-and-threads-in-sqlite-on-android-215e46670f2d). The thread pool I propose would require enabling WAL access. I will look into providing this option (to not break the existing), allowing a single writer and multiple readers, using some options, likely after the holidays, on Android first then iOS. In the mean time, if you need better performance, you should try to read multiple records with a single query (using IN clause for example if you read by id...)

[silverAndroid]

Alright, thanks!

[silverAndroid]

On Android, there is a way to have concurrent access to the database. An instance of SQLiteOpenHelper has its own connection to the database, so if you have multiple instances of SQLiteOpenHelper, you have multiple connections to the database

[cw-dev]

Are there any plans to revisit this? At least concurrent reads? Only being able to do one read at a time means we can't take advantage SQLite's efficiency with many small queries (https://sqlite.org/np1queryprob.html)

[alextekartik]

Thanks for bringing this to me again. Feedbacks on sqflite behavior in heavy-usage is welcome since that this is not something easy to experiment.

I’m not sure of of everything I will say here as I’m just a user of SQLite

• Native threading behavior of sqlite itself depends on how it has been built and whether sqlite itself is thread safe (good reading here: https://www.sqlite.org/threadsafe.html and https://www.sqlite.org/isolation.html). In this project we cannot control that as we rely on the native platform implementation,, we can control how it is used assuming the native implementation supports it (mutex, wal,...). I can only make assumption at this point.
• Dart is single threaded so it is always hard to map concurrency and threading
• I’ve heard bad experiments on iOS where reading in other thread than the current transaction thread was not isolated (i.e. it was reading the current unfinished transaction data). And in my own experience, trying reading from a different thread on Android was simply blocked if a transaction (even not exclusive) was in progress. But there are many open options (and PRAGMA) for each platform that I have not tried, I wanted to have a stable behavior. Suggestions here are welcome
• There is currently an option in OpenDatabaseOptions where you can specify singleInstance = false to open a new instance of the database. On the native side it will use a different connection and a different thread. Here it seems that you can have multiple reading thread (one per instance, you have to use the proper instance). In my own experiment, I’ve experienced a delay sometimes between when a transaction was done and when the read was done from another thread. To be confirmed. Adding a transaction should fix it. singleInstance = false could be one solution for you
• Regarding to concurrent reads: here sqflite adds a global lock before each action. That could/should be unnecessary since anyway FMDB on iOS does serialize action in one thread anyway. On Android I do create a background thread (one per instance) where actions are serialized too.
• Regarding to concurrent reads again: On Android I could create multiple reading threads assuming it is properly isolated. FMDB does recommend one thread per database. Since we target multiple devices, testing will be a pain
• Regarding the small queries optimization. Unfortunately here we have a kind of what they call “IPC” in your link. This overhead is non negligeable. On sqflite, batches are a good optimization around this overhead. It avoid this constant ping-pong between the native and dart world (single threaded).
• Regarding your issue. I’m not sure about the issue you have experimented? Have you experienced big latencies? Here as well, most of the latency/stuttering I have experimented came from the flutter world trying to sort big loads of data (200 000+) in memory (sometimes coming from sqflite but sqflite was not the blocking part here). A common error is also to do too many things in a transaction

Regarding your issue. I might think about any optimization that could be done on your side:

• Avoir reading/writing big loads of data. Read by page
• Use batches
• When transaction are needed, make sure you don’t hold the transaction lock for too long and postpone data manipulation
• Use Flutter compute to manipulate big loads of data in the dart world. Personally I use it for some json conversion and Map/List data conversion.
• Use singleInstance = false to allow for mutiple clients

On the sqflite side, Some experiment could be to:

• Remove the global lock per database to use the native mutex system
• Allow opening another thread on Android (on iOS FMDatabase isn't safe to access from multiple threads at the same time)
• Allow for page cursor (instead of offset-limit) to get the content by page. Need to check first whether it is safe to keep the cursor open (and the cost of it) and allow other calls while the cursor is open and whether we need some cleanup.

Since we target multiple devices, testing will be a pain as we might find for whatever reason that this device from this brand and this os with this version has a bug on it. I’d rather make this an experimental feature that can optionally be set during open (similar to FirestoreSettings). If someone can validate that one option is worth trying - even better with proofs - let me know.

Other feedback on that is welcome. I always managed to avoid this issue. SQLite is super fast to use in a single thread if you use it well and that overcomes almost always any other issues.

[msarkrish]

@alextekartik Is it possible to read and write a data in the same table at the same time with facing database lock issue?

[UttamPanchasara]

Is there any work arround or solution so far to handle this? I have very big data source to download and dump in database ( Mostly write operation ). Having separate thread as well to perform all bulky ops. meanwhile from main thread also try to write in database and my database getting locked. Due to write ops at same time from isolate and main thread.

Any solution for this problem? Or Is there a way to check if database in process so to avoid other operation at that time. ?

[alextekartik]

You can try https://pub.dev/packages/sqflite_common_ffi_async based on https://pub.dev/packages/sqlite_async

I don't know about isolate support but it is supposed to handle concurrency a lot better.