![]() This means that if the first attempt fails, the first retry will happen 2 1 (2) milliseconds later (1 millisecond is a really long time for a CPU). There is an amazing library called Polly that will catch an exception, wait a specified period of time, then try to execute the function again.įor this, I created AttemptAndRetry which will execute a Func>, but if it throws a SQLite.SQLiteException it will attempt to execute it again asynchronously.īetween retries, I recommend using an Exponential Backoff to ensure there are 2 n milliseconds in between. When this exception happens, we just need to retry our SQL query again after the database has been unlocked. When using multi-threading and async/await to interact with a SQLite database, sometimes this exception is thrown: SQLite.SQLiteException: database is lockedĪnecdotally, I've noticed this exception gets thrown more often in SQLite-net v1.6.0+ when WAL is enabled. On sqlite-net v1.6.0+, enabling write-ahead logging allows for faster database executionĪwait DatabaseConnection.EnableWriteAheadLoggingAsync().ConfigureAwait(false) protected static async Task GetDatabaseConnection() But be sure to understand both the advantages and disadvantages before enabling WAL in your app. There are also disadvantages to using WAL, most of which however don't impact Xamarin mobile apps. WAL uses many fewer fsync() operations and is thus less vulnerable to problems on systems where the fsync() system call is broken.Disk I/O operations tends to be more sequential using WAL.Reading and writing can proceed concurrently. WAL provides more concurrency as readers do not block writers and a writer does not block readers.WAL is significantly faster in most scenarios.In SQLite-net v1.6.0+, Write Ahead Logging (WAL), is available which brings many performance advantages: If (!(static x => x.MappedType = typeof(T)))Īwait DatabaseConnection.CreateTablesAsync(CreateFlags.None, typeof(T)).ConfigureAwait(false) This method ensures the database table has been created & mapped before returning the database connection. To accomplish both, I created async Task GetDatabaseConnection() where T is the Model's type. CreateTables(CreateFlags createFlags, params Type types).Ensure Tables have been created & mapped.To use the database connection, we must ensure the following: Static SQLiteAsyncConnection DatabaseConnection => _databaseConnectionHolder.Value 4. static readonly Lazy _databaseConnectionHolder = new Lazy(() => new SQLiteAsyncConnection(_databasePath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache)) This avoids creating our database when the app launches, keeping our app's launch time to a minimum. The official Microsoft Docs say it best: Lazy initialization of an object means that its creation is deferred until it is first used.īecause File IO operations and creating a database can be expensive (i.e., it requires many CPU cycles and can take longer than expected), we don't want to initialize our database until we need it. New SQLiteAsyncConnection(_databasePath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache) 3. This can significantly reduce the quantity of memory and IO required by the system" "When a thread establishes multiple connections to the same database, the connections share a single data and schema cache. Allows for multi-threaded database access." is created if it does not already exist" "The database is opened for reading and writing if possible, or reading only if the file is write protected by the operating system" SQLite also offers a few different options when creating a database connection using SQLiteOpenFlags, and here are the ones I recommend: This allows us to use async/await when performing database operations. SQLite-net offers an asynchronous API, SQLiteAsyncConnection. static readonly string _databasePath = Path.Combine(, "SqliteDatabase.db3") 2. Note: Be sure to first add the Xamarin.Essentials NuGet Package and follow its Getting Started Instructions. Locate the App Data Directoryīoth iOS and Android have a specific/unique file directory for each app, and this is where we'll store the. If you'd like to skip to the completed code, jump to Section 9: Put It All Together.Ī completed sample app using this implementation is also available here: 1. Let's explore the most efficient way to use it in our apps! The most popular SQLite ORM for Xamarin is SQLite-net.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |