I think the issue is that one needs to change the design of SQLlite so that it can co-exist properly with other instances. There must be a way to do this. It may not fix old versions of SQLlite but it would prevent this issue in new versions.
So, if you have two file descriptors open on the same file in the same process, a lock on one file descriptor is unable to control access to the file from the second file descriptor. POSIX locks only work if the two file descriptors are in separate processes.
There is a large of code in SQLite that works around this bug. And that code works well. But that code requies access to global variables.
The problem that Eric describes comes up when you link in two separate copies of SQLite, and thus have two distinct sets of global variables for managing the locks. These two separate copies of SQLite have no why of knowing about each other, and hence have no way of coordinating their lock behavior in order to avoid problems.
This is a clear explanation of the differences https://lwn.net/Articles/586904/ -- TLDR as an app developer is avoid POSIX locks unless you grok the odd semantics. Also, better things are coming -- I believe that the new lock type mentioned in that article is getting merged for Linux 3.15.