Hi Michael,
On 11/09/2012 11:36 PM, Michael Bayer wrote:
> On Nov 8, 2012, at 5:01 PM, Torsten Landschoff wrote:
>> My first tests with the SQLAlchemy core where promising, but when using
>> the ORM I get a bunch of deadlocks where it seems like the session opens
>> two connections A and B where A locks B out.
> The Session never does this, assuming just one Engine associated with it. It
> acquires one Connection from the Engine, holds onto it and uses just that
> connection, until commit() at which point the connection is released to the
> pool.
Okay, thanks, maybe the error was elsewhere then.
> SQLite supports a "SERIALIZABLE" mode of isolation, in conjunction with a
> workaround for a pysqlite bug
> (http://docs.sqlalchemy.org/en/rel_0_7/dialects/sqlite.html#serializable-transaction-isolation)
> which might be what you're looking for, though I generally try to steer
> users away from any usage of SQLite that depends on high concurrency (see
> "High Concurrency" at http://sqlite.org/whentouse.html).
I do not consider an application that downloads new records once per
hour concurrently to the GUI "high concurrency". And that background
process is not really a problem either, as long as I just lock the
database all the time. This makes the gui freeze for a couple of minutes
though.
Therefore I am looking for a solution that will make background and main
thread cooperate wrt. database access.
BTW: The main issue is not concurrency in itself. SQLite just uses
filesystem locking which are basically spin locks. So as long as the
background thread updates the database it has a high probability to
reacquire the lock after each transaction while the GUI thread will fail
to hit the slots where the db is not locked.
> To diagnose this code, you'd need to make use of the tools available - which
> includes connection pool logging, engine logging, and possibly usage of
> custom pools like sqlalchemy.pool.AssertionPool which ensures that only one
> connection is used at any time.
Thanks for the pointer to AssertionPool. I already use the others.
BTW, I found one offender that breaks running database upgrades with my
locking schemes:
from sqlalchemy import *
from sqlalchemy.pool import *
engine = create_engine("sqlite:////home/torsten/some.db",
poolclass=AssertionPool)
conn = engine.connect()
metadata = MetaData(conn, reflect=True)
This results in the following backtrace here:
$ python demo.py
Traceback (most recent call last):
File "demo.py", line 6, in <module>
metadata = MetaData(conn, reflect=True)
File
"/opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/schema.py",
line 2363, in __init__
self.reflect()
File
"/opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/schema.py",
line 2497, in reflect
connection=conn))
File
"/opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 2504, in table_names
conn = self.contextual_connect()
File
"/opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 2490, in contextual_connect
self.pool.connect(),
File
"/opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/pool.py",
line 224, in connect
return _ConnectionFairy(self).checkout()
File
"/opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/pool.py",
line 387, in __init__
rec = self._connection_record = pool._do_get()
File
"/opt/dynasdk/loco2-precise/lib/python2.7/site-packages/sqlalchemy/pool.py",
line 911, in _do_get
raise AssertionError("connection is already checked out" + suffix)
AssertionError: connection is already checked out at:
File "demo.py", line 5, in <module>
conn = engine.connect()
I would have expected it to reflect using the connection passed to the
MetaData constructor.
Greetings, Torsten
--
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff
Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561
mailto:[email protected]
http://www.dynamore.de
Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
