Efcore: Microsoft.Data.Sqlite: BackupDatabase() should yield

[bricelam]

Currently we call sqlite3_backup_step(backup, -1), but this will lock the database for the duration of the copy.

One of the main purposes of this API is to allow users to continue uninterrupted while a backup of the database is made.

To allow the backup to be made on a background thread while giving other threads opportunities to read, we should update it to step one page at a time and yield in between.

Unfortunately, this opens up the possibility of getting SQLITE_BUSY when stepping. This means we'll need to retry. The backup can even be automatically restarted. Which means it could take a very long time to complete. We'll need to think about timeouts and cancellation when we do this.

[bricelam]

What we currently have is useful when you want the copy done as quickly as possible. This is useful on UWP to export the database, or to save/load an in-memory database. But I don't think these scenarios would be degraded by yielding since there typically isn't concurrent access with them.

[bricelam]

Triage: We decided to keep the existing method as is (no yielding), but to add additional methods/overloads for timeout, cancellation, and progress that yield.

[0xced]

Official SQLite documentation for reference: Using the SQLite Online Backup API.

[0xced]

I’m working on it, I hope to submit a pull request next week.

[0xced]

Haha, make it next year. 🤣