0

I have written a .dll that uses sqlite3. It has an insert function that performs inserts on the database.

I also have a test executable that uses the dll to perform inserts. Right now I am testing sqlite and its ability to manage multiple connections. The test program inserts in an infinite loop, the pseudo code looks like this:

    while(1) {
      openDb();
      doInsert();
      closeDb();
     }

Basically I have set it up so that I run 4 versions of my test app, and each version inserts a letter (a, b, c or d) into the database.

Right now, this doesn't work because the database is just about always locked. It works fine with one or two but it doesn't if I run all 4 programs its like I create one big race condition.

I have looked at the documentation, and I have come accross things like SQLITE_OPEN_FULLMUTEX as specified here: http://www.sqlite.org/c3ref/open.html

Does sqlite3 provide a mechanism to deal with multiple exe's trying to write to he same DB at once? Or am I simply pushing sqlite too hard in my load-testing? Do I need to write my own connection pool?

Any help on this matter would be great. Thanks.

ddoor
  • 5,819
  • 9
  • 34
  • 41
  • What's the actual problem? You say "it doesn't run", but presumably it's still adding entries to the database -- and that's all it's supposed to do. So what's the actual problem? Even if you get `SQLITE_BUSY`, the database is still making forward progress as fast as it possibly can. If you're not happy that your code is doing what you coded it to do, change it! – David Schwartz Aug 21 '12 at 01:40
  • No, the problem is that I don't get the distribution of a, b, c and d that you would expect. If the lock is released after insert you would expect to see a roughly equal amount of each letter in the database, the issue is that I am getting 0 entries inserted. I have read the link below, and I think I have figured it out. I was just asking a bit too much of sqlite, now I just sleep for a few seconds after a lock is encountered and try again. – ddoor Aug 21 '12 at 01:44

2 Answers2

2

If you care what distribution you get, then you need to write code that won't add a billion a's without adding any b's. You are writing code that doesn't do what you want. That's not sqlite's fault. If you don't want to write 1,000 a's and no b's, then don't do that.

All you are guaranteed is that the database will make forward progress. It's up to your code not to ask the db to do things you absolutely don't want it to do.

Sqlite is making as much forward progress as it can as fast as it can. That's its job and 99.99% of the time, that's what everyone wants. Process switching is expensive, so sqlite is minimizing it. It's your job not to ask it to do things you don't want it to do. Write your code so that the db is only asked to do things you want it to do. Then when the db runs as fast as it possibly can, you'll get what you want.

David Schwartz
  • 179,497
  • 17
  • 214
  • 278
  • Perfect, thats just what I needed to know. I needed to gauge how much responsibility my .dll needs to have :) - very impressed with sqlite3 - thanks for your answer. – ddoor Aug 21 '12 at 01:50
0

I think I have found the solution in the documentation specified here:

http://www.sqlite.org/faq.html#q5

I will leave this question open in case someone has anything to add or experiences that same problem :)

ddoor
  • 5,819
  • 9
  • 34
  • 41