sqlite3, memory db and multithreading

[John Nagle]

królewna wrote:
> The problem is simple: I have multiple threads within one program. At 
> least 2 threads have to have access to in-memory sqlite database. It is 
> not possible to pass sqlite objects to those threads because an 
> exception is rised:
> 
> ProgrammingError: SQLite objects created in a thread can only be used in 
> that same thread.The object was created in thread id -1219066176 and 
> this is thread id -1224475792
> 
> Is there any EASY way to use this in-memory db in many threads? Creating 
> another connection is not a solution as it creates completely new db 
> instead of connecting to the existing one.

    Recognize that sqlite is for "lite" database work.  If you're running
some massively concurrent database application, you need something heavier,
like MySQL or Postgres.  "sqlite" has a simplistic locking strategy.
Locking is done by file-level locking, and you can have one UPDATE/INSERT
operations, or any numnber of SELECTs, at a time.  Lock conflicts are
handled by wait and retry, which is slow.

    The big databases are much smarter about figuring out which operations
can safely be done in parallel, do much more query optimization, and
handle high transaction volumes much better than sqlite.

    You use sqlite for configuration files, your personal databases, and
other small stuff.  You run your Web 2.0 site on MySQL or Postgres.
You run your Fortune 1000 company on Oracle.

					John Nagle