Monotouch data sync - why does my code sometimes cause sqlite errors?

Question

Hi

I have the following calls (actually a few more than this - it's the overall method that's in question here):

ThreadPool.QueueUserWorkItem(Database.Instance.RefreshEventData);
ThreadPool.QueueUserWorkItem(Database.Instance.RefreshLocationData);
ThreadPool.QueueUserWorkItem(Database.Instance.RefreshActData);

1st point is - is it OK to call methods that call WCF services like this? I tried daisy chaining them and it was a mess.

An example of one of the refresh methods being called above is (they all follow the same pattern, just call different services and populate different tables):

public void RefreshEventData (object state)
        {
            Console.WriteLine ("in RefreshEventData");
            var eservices = new AppServicesClient (new BasicHttpBinding (), new EndpointAddress (this.ServciceUrl));

            //default the delta to an old date so that if this is first run we get everything
            var eventsLastUpdated = DateTime.Now.AddDays (-100);

            try {
                eventsLastUpdated = (from s in GuideStar.Data.Database.Main.Table<GuideStar.Data.Event> ()
                    orderby s.DateUpdated descending
                    select s).ToList ().FirstOrDefault ().DateUpdated;

            } catch (Exception ex1) {
                Console.WriteLine (ex1.Message);
            }

            try {
                eservices.GetAuthorisedEventsWithExtendedDataAsync (this.User.Id, this.User.Password, eventsLastUpdated);
            } catch (Exception ex) {
                Console.WriteLine ("error updating events: " + ex.Message);
            }

            eservices.GetAuthorisedEventsWithExtendedDataCompleted += delegate(object sender, GetAuthorisedEventsWithExtendedDataCompletedEventArgs e) {

                try {

                    List<Event> newEvents = e.Result.ToList ();

                    GuideStar.Data.Database.Main.EventsAdded = e.Result.Count ();

                    lock (GuideStar.Data.Database.Main) {
                        GuideStar.Data.Database.Main.Execute ("BEGIN");

                        foreach (var s in newEvents) {

                            GuideStar.Data.Database.Main.InsertOrUpdateEvent (new GuideStar.Data.Event { 
                                Name = s.Name, 
                                DateAdded = s.DateAdded, 
                                DateUpdated = s.DateUpdated, 
                                Deleted = s.Deleted, 
                                StartDate = s.StartDate,
                                Id = s.Id, 
                                Lat = s.Lat, 
                                Long = s.Long   
                            });

                        }

                        GuideStar.Data.Database.Main.Execute ("COMMIT");
                        LocationsCount = 0;
                    }
                } catch (Exception ex) {
                    Console.WriteLine("error InsertOrUpdateEvent " + ex.Message);
                } finally {
                    OnDatabaseUpdateStepCompleted (EventArgs.Empty);
                }

            };
        }

OnDatabaseUpdateStepCompleted - just iterates an updateComplete counter when it's called and when it knows that all of the services have come back ok it removes the waiting spinner and the app carries on.

This works OK 1st time 'round - but then sometimes it doesn't with one of these: http://monobin.com/__m6c83107d

I think the 1st question is - is all this OK? I'm not used to using threading and locks so I am wandering into new ground for me. Is using QueueUserWorkItem like this ok? Should I even be using lock before doing the bulk insert/update? An example of which:

public void InsertOrUpdateEvent(Event festival){

            try {
                if (!festival.Deleted) {
                    Main.Insert(festival, "OR REPLACE");
                }else{
                    Main.Delete<Event>(festival);
                }
            } catch (Exception ex) {
                Console.WriteLine("InsertOrUpdateEvent failed: " + ex.Message);
            }

        }

Then the next question is - what am I doing wrong that is causing these sqlite issues?

w://

Answer 1

Sorry, no specific answers, but some thoughts:

Is SqlLite even threadsafe? I'm not sure - it may be that it's not (to the wrapper isn't). Can you lock on a more global object, so no two threads are inserting at the same time?

It's possible that the MT GC is getting a little overenthusiastic, and releasing your string before it's been used. Maybe keep a local reference to it around during the insert? I've had this happen with view controllers, where I had them in an array (tabcontrollers, specificially), but if I didn't keep an member variable around with the reference, they got GC'ed.

Could you get the data in a threaded manner, then queue everything up and insert them in a single thread? Atleast as a test anyway.

Answer 2

Sqlite is not thread safe.

If you want to access Sqlite from more than one thread, you must take a lock before you access any SQLite related structures.

Like this:

lock (db){
      // Do your query or insert here
}