Re: [sqlalchemy] (SQLite) Outside auto-locking based on SQLAlchemy Events
On Nov 12, 2012, at 9:37 PM, Michael Bayer wrote: On Nov 12, 2012, at 5:33 PM, Torsten Landschoff wrote: 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: 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. if so then that's a bug, added http://www.sqlalchemy.org/trac/ticket/2604 to take a look at that. I've no idea how that silly API got in there, but seems a bit late to remove it. I'm going to deprecate it though, for now use this form: m = MetaData() m.reflect(conn) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] (SQLite) Outside auto-locking based on SQLAlchemy Events
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:torsten.landsch...@dynamore.de 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 sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] (SQLite) Outside auto-locking based on SQLAlchemy Events
On Nov 12, 2012, at 5:33 PM, Torsten Landschoff wrote: 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: 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. if so then that's a bug, added http://www.sqlalchemy.org/trac/ticket/2604 to take a look at that. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] (SQLite) Outside auto-locking based on SQLAlchemy Events
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. 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). 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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
