database is locked (help me)

[wph144]

I don't have any clue.
I found that #648, but I guess it is not my case
It happens randomly on just normal update (just one row or more)
Any idea? please help me!

[simolus3]

Do you only get that on production or can you reproduce this in development too? Are you opening multiple databases at the same time?

[wph144]

I cannot reproduce it in development. I send log to my dev/real server. But I dont have any logs in dev server by now.
If user logout and log in with another ID, then multiple databases opening is possible. But It seems like he don't logout. My app is logging exercise (workout). It is suddenly happen while he just repeatedly complete his exercise set (check interval is about 2~4 minutes.

By the way, I am using android home widget and android WorkManager (for timer). So I execute dart code with isolate on background. (https://medium.com/stuart-engineering/keep-flutter-running-background-on-android-6ffc85be0234).
As I know, on isolate we should open database again since this isolate is not share anything with main isolate(?).
Is it problematic?
Can you reproduce 'database is locked' situation?

[simolus3]

If there are multiple databases open at the same time, the locking issue can happen (especially if one of them has a long-running transaction or many writes).

You might be able to fix that by just retrying when a database is locked, but I'm not sure if that might have other downsides as well.

[wph144]

Hi, @simolus3. Long time no see. I am still struggling with this issue.

Case 1. debug(hot restart) + Android
*reproducible step

database.transaction(() async {
  await myRepo.update([xxxEntity]);           // db write, it takes short time
  await Future.delayed(const Duration(seconds: 20));
});

Transaction takes 20 seconds and 'yourdbfile-journal' will be made. (my PRAMGA journal_mode is 'delete', default value)
Before 20 seconds ends, do hot restart
Journal file will not be deleted and new Flutter instance that started by hot restart will show message 'database is locked'
In this case, MainActivity is same on before and after hot restart. On the other hand, MainApp(flutter) is different
But I think Case 1 is not critical.

Case 2. real (or profile) + Android + unexpected MainActivity onDestroy
*reproducible step

Go to Setting > Developer Options.

1. Don't keep Activities: ON
2. Background process limit: 0

Make transaction takes 20 seconds like Case 1.
Start transaction and send app to background.
MainActivity.onDestroy() will be called.
Strat app again and access db then will show database is locked.

*My workaround on Case 2.
I did override MainActivity.onDestroy

override fun onDestroy() {
  MyMethodChannel().invokeMethod("onDestroyMainActivity", true)     // method channel message from Android to Flutter 
  Thread.sleep(1000)          // ugly. I tried callback but failed. 
  super.onDestroy()
}

In flutter, receive 'onDestroyMainActivity' and close database.

This workaround has downsides.

1. should use Thread.sleep
2. Uncertainty about Android always call MainActivity.onDestroy() ?

Any other good idea?

I think there are more case but I need more time

[ycherniavskyi]

What about using AppLifecycleState.detached (received from didChangeAppLifecycleState method of WidgetsBindingObserver object added with WidgetsBinding.instance.addObserver) to close the database connection? In my case, it seems to work.