Are there known issues with using sqlite and file locking on different platforms?

Question

I'm using sqlite to do an index of a proprietary file, and the database will be accessed with multiple threads (using different sqlite handles) for reading and writing.

I know that sqlite locks the file in order to provide concurrency for readers/writers, and depends on the OS file api for locking.

This is all fine on windows and linux, but on solaris and hpux I am less confident regarding how advisory locking works there.

Does anyone have any idea how well sqlite works regarding concurrency on those platforms?

Answer 1

In my experience (although based on somewhat dated SQLite versions, i.e. 3.0.x - 3.2.x) you don't want do rely on SQLite's default locking implementation when there is some kind of contention (even if it's just multiple threads in the same process).

The main problem is that SQLite just sleeps for a little while and then tries again if the file has been locked (until the busy wait timeout expires) - this is likely to result in "database locked" errors in the application. The workaround I have implemented was to use my own locking (using a mutex in a multi-threaded process) in addition to SQLite's own locking.