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.
[sqlalchemy] (SQLite) Outside auto-locking based on SQLAlchemy Events
Hi Michael et al, I am banging my ahead into a (so it seems) trivial problem for days now. Basically, I finally need to lock my SQLite database because multiple threads are writing to it. This does not happen to often, but I have a single thread that is dumping thousands of records into the database while the user interface might concurrently do simple updates like update component set short_description='Foo' where id=10. Originally the sync thread was slow enough that the SQLite side of locking did work. Now that it is optimized a bit it does not keep up anymore. This leads to all other requests going the Database is locked way of failing. 99% of the time all db access is sequential so I figured it would suffice to lock the database before I do anything to it and unlock the database when done. I tried to instrument all code but with the unit of work pattern it is hard to find out where the lock was forgotten... So my current approach is to use the before_execute event to open a lock and when a connection is returned to the pool I unlock it. I attached the code of that mechanism. 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. I can provide more data and example code, but I would first like to know if my approach is completely bogus in your eyes. If it is I am open to better ideas. BTW: Originally I captured before_flush and commit/rollback session events, but this still created locking errors due to read requests going unchecked. 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. # -*- coding: utf-8 -*- import collections import threading import traceback from sqlalchemy import event from sqlalchemy.engine import Connection class DatabaseAutoLocker(object): Verwaltet eine Zugriffssperre auf eine Datenbank, die den exklusiven Zugriff durch einen einzelnen Thread sicherstellt. Die gedachte Verwendung ist, eine Instanz dieser Klasse nach dem Erstellen der Engine anzulegen:: engine = create_engine(sqlite:///test.db) DatabaseAutoLocker(engine) Die DatabaseAutoLocker-Instanz hängt sich dann über Events an die engine und lebt, solange diese engine existiert. Daher braucht man keine Referenz auf den Locker aufzubewahren. def __init__(self, engine, timeout=None): Erstellt einen Autolocker für die in *engine* gegebene Datenbank-Engine. :param engine: Datenbank-Engine als Instanz von sqlalchemy.engine :param timeout: Zeit in Sekunden, die ein Client auf die Datenbankverbindung wartet, bevor eine Exception geworfen wird. None (default) deaktiviert den Timeout und der Client wartet bis zuletzt. self.timeout = timeout #: Schützt den Zugriff auf interne Daten self._mutex = threading.RLock() #: Enthält die Liste der noch auf die Datenbanksperre wartenden Clienten self._pending_requests = collections.deque() #: Aktuell aktive Verbindung (diese hat die Datenbank für sich gesperrt). None, #: wenn keine Verbindung die Sperre hat. self._active_dbapi_connection = None #: Wenn aktiviert liefert dies den Traceback des Aufrufers, der die Datenbank #: gegenwärtig gesperrt hält, sonst immer None. self._active_locker_traceback = None event.listen(engine, before_execute, self.__connection_event_before_execute) event.listen(engine, checkin, self.__pool_event_checkin) def __connection_event_before_execute(self, conn, clauseelement, multiparams, params): Registriert die erste Ausführung eines Kommandos über eine Datenbankverbindung. Hier muss die Datenbank für andere Verbindung gesperrt werden. dbapi_connection = _get_dbapi_connection(conn) request = None with self._mutex: if self._active_dbapi_connection is dbapi_connection: # Nichts zu tun, die Verbindung ist schon im Besitz der Sperre. return locker_traceback = None if self.timeout is not None: locker_traceback = traceback.format_stack() if self._active_dbapi_connection is None:
