Avoiding sqlite3 database locked

Question

I have a multithreaded application that uses sqlite (3.7.3)

I'm hitting the database locked error that seems to be quite prevalent. I'm wondering how to avoid it in my case.

Let me describe what I'm building. Sorry, no code it's too large and complex.

I have around 8 threads that simultaneously access the database. Any one of those threads can either read or write at the same time.

Each row in a table in the database has a file path that points to a resource + other attributes related to that resource.

3 fields of note are readers, status and del.

Readers is incremented each time a thread reads from the resource, but only if status > 0 and del = 0.

So I have some SQL that does

UPDATE resource set readers=readers+1 where id=? AND del=0 AND status>0

After that, I check the number of rows updated. It should only be 1. After that I try to read the row back with a select. I do that even if it failed to update because I need to know the reason that it failed.

I tried wrapping both the update and the select in in a transaction but that didn't help. I've checked that I'm calling finalize on my statements too.

Now, I thought that sqlite serializes by default. I've tried a couple of open modes but I still get the same error.

And before you ask, no I'm not intending to go to mysql. I absolutely need zero config.

Can someone provide some pointers on how to avoid this type of problem? should I move the readers lock out of the DB? If I do that what mechanism should I replace it with? I'm using Linux under C++ and with the boost library available.

EDIT: Interestingly, adding COMMIT after my updated call improved things dramatically.

Answer 1

When you open the db, you should configure the 'busy timeout'

int sqlite3_busy_timeout(sqlite3*, int ms);

http://www.sqlite.org/c3ref/busy_timeout.html

Answer 2

First question: are you trying to use one connection with all eight threads? If so, make sure each thread has their own connection. I don't know of any database that likes that.

Also check out the FAQ: http://www.sqlite.org/faq.html

Apparently SQLite has to be compiled with a SQLITE_THREADSAFE preprocessor option set to 1. They do have a method to determine if that's your problem.

Another issue is that writes can only happen from one process safely.