Xamarin.Forms Sqlite (sometimes) fetching bad result after update

Question

I have a Xamarin.Forms project which uses a local database file Sqlite (the file is called datas.lite) from sqlite-net-pcl nuget package (version 1.2.0).

I have a table called Item:

public class Item
{
    [PrimaryKey]
    public int ID { get; set; }
    public bool IsActive { get; set; }
}

And a repository ItemRepository with a static connection SQLite, I use to update the Item rows:

public class ItemRepository
{
   private static SQLite.SQLiteConnection _conn;
   private static SQLite.SQLiteConnection Conn
   {
          get
          {
              if (_conn == null)
                  _conn = new SQLite.SQLiteConnection("myPath/datas.lite");
              return _conn
          }
   }
   public ItemRepository()
   {
       Conn.CreateTable<Item>();
   }
   public Item GetById(int id)
   {
       return Conn.Get<Item>(id);
   }
   // Retrieves all items from table Item
   public IEnumerable<Item> GetAll()
   {
        return Conn.Table<Item>();
   }
   
   // Updates the provided item
   public int InsertOrReplace(Item item)
   {
        return Conn.InsertOrReplace(item, typeof(Item));
   }
}

The app modifies the IsActive property for all items every 5 minutes by updating the Item table (The method TimerHelper.StartTimerInNewThread is called once at startup).

TimerHelper.StartTimerInNewThread(TimeSpan.FromSeconds(delais), () 
            =>
            {
                try
                { 
                     // retrieve all items with DB
                     List<Item> items = repo.GetAll();
                     foreach (Item item in items)
                     {
                          item.IsActive = !item.IsActive;
                          if (repo.InsertOrReplace(item) == 1)
                          {
                               Log?.Info($"Item {item} has been updated in DB: IsActive = {repo.GetItem(item).IsActive}.");
                          }
                          else
                          {
                              throw new Exception($"InsertOrReplace() method returned a value != 1.");
                          }
                    }
                }
                catch (Exception ex)
                {
                    // Log exception here
                }
            });



            

And immediately after updating the rows of table Item, I check (I log every IsActive property value for every Item) that the IsActive property of all items actually changed. So far, so good. But if I let the application runs for several hours, sometimes, the check does not reflect the previous update...for instance, the application set the IsActive property for all items to TRUE, but the immediate request to the IsActive property returns FALSE for all items.

If I read via DbBrowser for Sqlite the table Item from the database local file (datas.lite), every item has its IsActive property set to TRUE, which is correct. So why the immediate read request I made after the update returned FALSE for all items, is there any caching that is active with sqlite? Or is it due to the fact that I have a static connection, and it is never closed, (it is moreover the recommanded way of doing according to microsoft documentation: https://docs.microsoft.com/en-us/xamarin/get-started/quickstarts/database?pivots=windows)

Thank you for any help

Answer 1

Here is how to protect timer code from being re-entered while it is still running.

Compare these three ways of running code repeatedly using a timer.

This is "typical" timer code. "seconds" is the time interval at which the work is done. Works fine if the code always finishes before the timer delay fires it again. Problematic if the timer code might take the ENTIRE time interval. (or if some background work, such as GC, takes enough time that your timer code eats the remaining time.) Really problematic if timer code takes so long that timer events start "piling up", starving the rest of your app from getting any time.

TYPICAL "NAIVE" TIMER CODE:

// Works fine if action is shorter than time delay.
// DON'T USE THIS CODE, if action might take longer than time delay.
using Timer = System.Timers.Timer;

    private void StartFixedDelayTimer(float seconds, Action action)
    {
        _timer = new Timer(1000 * seconds);
        _timer.Elapsed += (sender, e) => action();
        // The timer event repeats until explicitly stopped.
        _timer.Start();
    }

---

SKIP-IF-BUSY TIMER CODE:

This is similar, but it protects itself by "skipping" work, if the work is still being done:

    // For long running "action", increase "idleSeconds" to guarantee more time for other background tasks.
    private void StartFixedDelayTimerSkipIfBusy(float seconds, Action action, float idleSeconds = 0.1f)
    {
        _timer = new Timer(1000 * seconds);

        bool entered = false;
        _timer.Elapsed += (sender, e) => {
            if (entered)
                // Timer code already running! Skip this one.
                return;
            entered = true;
            try {
                action();
                // IMPORTANT: This is needed to "see and skip" next timer event,
                // if it happens during "action". Without this, timer events can "pile up",
                // starving other background tasks.
                System.Threading.Thread.Sleep((int)(1000 * idleSeconds));
            } finally {
                entered = false;
            }
        };

        // The timer event repeats until explicitly stopped.
        _timer.Start();
    }

---

VARIABLE-DELAY TIMER CODE:

This is an alternative approach. It doesn't request that the work be done again, until AFTER it finishes the first time. Here "seconds" is the amount of time between FINISHING the work, and the start of the next work. This is useful if you don't need the work done on a rigorous clock schedule. It has the advantage that no matter how long "action" takes, the rest of your app code gets "seconds" of cpu time before this work starts again - won't "starve" your app.

    private void StartDelayBetweenWorkTimer(float seconds, Action action)
    {
        _timer = new Timer(1000 * seconds);
        // Only fire the timer once. (But in Elapsed, we fire it again.)
        _timer.AutoReset = false;
        _timer.Elapsed += (sender, e) => {
            action();
            // Fire the timer again. Work will be done again "seconds" seconds after this statement is called.
            _timer.Start();
        };

        _timer.Start();
    }