sqlite3, memory db and multithreading
in [Python]
|
Prev: url2lib (windows 7) does not notice when network reconnects (getaddrinfo problem)
Next: Install 2.6.4 as non-admin on Windows
From: królewna on 18 Mar 2010 08:58 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. -- Best regards princess
From: DreiJane on 18 Mar 2010 10:49 Hello, i cannot help you directly with sqlite2 in the Standardlib, since i am used to work with Roger Binns's apsw. After a short experiment with pysqlite leading to data loss - caused by one of the unclearer exception messages of sqlite3 and me having a bad day - i at once turned back to apsw. And so far i haven't done much with threads. Principally sqlite connections (sqlite3 objects in the C-API) can be used over multiple threads - and connections to :memory: make no difference. There are additional parameters to open() giving fine- tuned control. And apsw is promising a true reflection of sqlite's C- API. Regards, Joost
From: Aahz on 18 Mar 2010 18:06 In article <hnt81m$fsi$1(a)news.task.gda.pl>, =?UTF-8?B?a3LDs2xld25h?= <kr�lewna(a)ee.pl> 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. You probably need to serialize access to the database through one thread. -- Aahz (aahz(a)pythoncraft.com) <*> http://www.pythoncraft.com/ "Many customs in this life persist because they ease friction and promote productivity as a result of universal agreement, and whether they are precisely the optimal choices is much less important." --Henry Spencer
From: John Nagle on 19 Mar 2010 00:33 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
From: Expo on 19 Mar 2010 03:20
On Mar 18, 1:58 pm, królewna <kr le...(a)ee.pl> 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. > You can put the SQLite database into a Singleton class and use a semaphore to serialize the access to methods which writes to the database. |