SQLite.Net.SQLiteException: Busy

I’ve modified the 'Xamarin_Data_Complete' project from XAM160 (old version). Right now I’m testing it with large XML files. If I update, search and display the data I get a

SQLite.Net.SQLiteException: Busy

I can solve this by catching this exception and try again 1 second later. Is this the right approach or should this exception never happen under ‚normal‘ conditions?

In the past I only used CoreData with a Sqlite backend. To persist the data on disk you had to call save explicitly. I didn’t find something similar in this project. Is the save always done automatically and never in control of the user?

The last thing I’m searching a solution for is the Linq expression to search the database.

This is the Linq expression from XAM160

return await Table().Where(s => s.Id == id).FirstOrDefaultAsync().ConfigureAwait(false);

I try to modify it to be able to search for more complex search string like:

string searchThis = „text1 text2 text3“

searchThis should be used for a full text search which means every part of the string should be handled separately.

The search parts are separated by a space and I can do
List searchAllTheseParts = searchThis(new string[] { " " }, StringSplitOptions.None);

Can I use Linq for this approach or is Linq the wrong answer for this kind of search and I have to go back to SQL?

This modification doesn’t work:

return await Table().Where(s => s.MyField.Contains(searchAllTheseParts[0]) && s.MyField.Contains(searchAllTheseParts[1]) && s.MyField.Contains(searchAllTheseParts[2])).ToList().ConfigureAwait(false);

Posts

  • rene_ruppertrene_ruppert DEXamarin Team, University, XamUProfessors Xamurai

    Can you give us access to the project so we would be able to try it?

  • JuergenKollerJuergenKoller DEUniversity ✭✭

    Hello Rene,

    I’ve attached the sample project. Right now I get a database locked exception and it looks like because of the inserts I’m doing.

    https://dropbox.com/l/2I5xap87Y7cZsPm1HhO4ut

    After modifying the xam sample project there are some questions that came across:

    1. Is my solution for a background database update, insert and delete the right approach? Should I just use some try / error statements to react on the ‚database busy‘, ‚database locked‘, … exceptions and just try again?

    2. I’m looking for a performant full text cross platform search function. Right now I’m putting all the content of the tables in one ContentString field and do a Linq search on it.
 But my approach feels really bad right now. If you type in letters the previous search should be canceled first. Is this possible with a Linq expression?
      Second problem is the results come in too slow. I’ve played around with the Take() and Skip() functions to get results faster. It works but I hope you could lead me to some other better approaches?

  • rene_ruppertrene_ruppert DEXamarin Team, University, XamUProfessors Xamurai

    I have just seen your reply here @JuergenK‌ - I will have a look at your solution tomorrow.

    As for fulltext search: I have never tried or implemented this. But it seems to involve quite some work. Here's something that might help you. http://stackoverflow.com/questions/5372409/how-to-use-fts-in-sqlite-with-monotouch-for-ios

    And here's something that is a reply to a question I asked myself long ago. I wanted to use SQLCipher and combine that with fulltext search. The Gist contains the reply. I have never tried or evaluated the code. But if it works with SQLCipher, it should work with Sqlite too: https://gist.github.com/developernotes/5311963

  • rene_ruppertrene_ruppert DEXamarin Team, University, XamUProfessors Xamurai

    @JuergenK‌

    I looked at your project. I see three issues.

    The DollyMaker<T>() - why do you clone the sessions? Just reuse the instances.
    The problem with the locked DB however is caused by

    private async Task DataImportEditUpdateWorker()

    This method runs async. If you search the DB, it might be locked since the method above is still inserting. If you wait until this method has finished, your code will work.

    And last: you are creating ten thousands of session objects in that method. This causes the Java Garbage Collector to freak out on my machine and I presume on a device it would just crash. If I change the for loop to only create 10 instances of 10000 it will still take a while for it to finish but it won't run OOM.

  • JuergenKollerJuergenKoller DEUniversity ✭✭

    The DataImportEditUpdateWorker() is just for a simulation of a background update, insert, ... process - maybe a too lazy one. ;-)
    That means a correct approach would be a global variable for example
    searchRunning = true
    before starting the search and pause the background update process until the search has finished to avoid the DB exceptions?

  • rene_ruppertrene_ruppert DEXamarin Team, University, XamUProfessors Xamurai

    I see what you are doing there...could it be that you are running into this problem: https://github.com/oysteinkrog/SQLite.Net-PCL/issues/42

    Maybe you want to submit an issue to the SQLite-Net PCL project and provide them with your example.

  • JuergenKollerJuergenKoller DEUniversity ✭✭

    Thanks Rene, that's exactly the issue. I try to modify my project to build it more OOM friendly and try the solution they provide!

  • rene_ruppertrene_ruppert DEXamarin Team, University, XamUProfessors Xamurai

    @JuergenK Would you mind sharing your solution when you're done? I presume you will use a singleton for SQLiteConnectionWithLock. I think it will help other users if they find a solution here in the forum.

  • JuergenKollerJuergenKoller DEUniversity ✭✭

    Hello Rene, yes definitely. But it will take some time because I'm a bit busy right now and need to find solutions for some other questions right now.

  • JuergenKollerJuergenKoller DEUniversity ✭✭

    For a quick check I've removed the

    new SQLite.Net.SQLiteConnectionWithLock

    and put it into the constructor. Now it's called only once but the SQLite.Net.SQLiteException: Busy crashes still occur. I've add a record counter to see how many records are in the db right now. On my slow Motorola device the crashes start happening after 1000 records after you keep searching through the database.
    After that I've added a second SQLiteConnectionWithLock in the sample project (DataManager2) and use this for the background db record insert and update (IncreaseDatabaseSize). I was hoping a SQLiteConnectionWithLock that's only used for the search and another that's only used for the background update and insert would solve the problem. But it doesn't.
    If there is a need for deeper investigation for example on GitHub like Rene mentioned

    https://github.com/oysteinkrog/SQLite.Net-PCL/issues/42

    can I use the sample project which is based on the Xamarin University sample to publish it on this public forum?

    The sample project is here:

    https://www.dropbox.com/s/8aqzloxaveudov1/Xamarin_Data_Complete%202.zip?dl=0

Sign In or Register to comment.