[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.python

sqlite3, memory db and multithreading

królewna

3/18/2010 12:59:00 PM

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
11 Answers

3Jane

3/18/2010 2:49:00 PM

0

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

aahz

3/18/2010 10:06:00 PM

0

In article <hnt81m$fsi$1@news.task.gda.pl>,
=?UTF-8?B?a3LDs2xld25h?= <królewna@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@pythoncraft.com) <*> http://www.python...

"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

John Nagle

3/19/2010 4:34:00 AM

0

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

Expo

3/19/2010 7:21:00 AM

0

On Mar 18, 1:58 pm, królewna <kr le...@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.

królewna

3/19/2010 10:56:00 AM

0

W dniu 18.03.2010 15:49, DreiJane pisze:

> 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.

It's my fault not saying what am I using to connect to sqlite db. It's
sqlite3 module. So there is no open() function etc. As for now I'm not
interested in rewriting my program to use apsw so is there any possible
way of working this out with sqlite3?

--
best regards
princess

królewna

3/19/2010 10:57:00 AM

0

W dniu 18.03.2010 23:06, Aahz pisze:
>
> You probably need to serialize access to the database through one thread.

sqlite3 objects are not pickable so it's not proper way.

--
best regards
princess

królewna

3/19/2010 11:04:00 AM

0

W dniu 19.03.2010 08:20, Expo pisze:
>
> 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.

I've tried this out but doesnt work. Still gives an error like:

ProgrammingError: SQLite objects created in a thread can only be used in
that same thread.The object was created in thread id -1216280896 and
this is thread id -1217107088

--
best regards
princess

Tim Golden

3/19/2010 11:10:00 AM

0

On 19/03/2010 10:56, królewna wrote:
> W dniu 18.03.2010 23:06, Aahz pisze:
>>
>> You probably need to serialize access to the database through one thread.
>
> sqlite3 objects are not pickable so it's not proper way.

Is it possible you've misunderstood the meaning of the word "serialize"
here? What's being suggested isn't serialising (ie marshalling, pickling)
the data; rather, serialising the *access*, ie pushing all db requests into
a queue which is read by one thread which manages the only db connection.

TJG

królewna

3/19/2010 11:24:00 AM

0

W dniu 19.03.2010 12:10, Tim Golden pisze:
> Is it possible you've misunderstood the meaning of the word "serialize"
> here?
It's not possible, it just happened :)

> What's being suggested isn't serialising (ie marshalling, pickling)
> the data; rather, serialising the *access*, ie pushing all db requests into
> a queue which is read by one thread which manages the only db connection.

That would make structure of program much more complicated. I would have
to create queue for putting there queries and some other extra
variables/structure to receive output from db and some more for
controlling the execution flow of awaiting threads.
--
best regards
princess

królewna

3/19/2010 1:58:00 PM

0

I have found the solution. I dont know why in python documentation there
is not a single word about this option. I've found it in pysqlite doc
site. So we have to add a new keyword argument to connection function
and we will be able to create cursors out of it in different thread. So use:

sqlite.connect(":memory:", check_same_thread = False)

works out perfectly for me. Of course from now on me need to take care
of safe multithreading access to the db. Anyway thx all for trying to help.

--
best regards
princess