I might be misunderstanding something, but doesn’t that mean that only one SQLite library could be loaded per system? That hardly seems like an acceptable compromise for an embedded software.
As a practical matter, you really have to work hard to get an application to use two different copies of SQLite at the same time - all the while avoiding symbol collisions on link. You can do it, but it takes some work. And then on top of that your application has to decide to open two or more connections to the same database file, using different copies of SQLite in each case.
The more common failure mode I've seen is a library bundling and inlining an old version of a database API in a way that means it overrides the shared object I was expecting my code to link to, and alarums and excursions resulting thereby.
(I don't imagine SQLite would -break- in that situation, but I doubt my application code would be any less unhappy than in the cases of that problem I've encountered in the wild)
It's easy: create DLL/.so A with statically linked sqlite. create DLL/.so B with statically linked sqlite. Make a program using both DLLs.