sqlite and "database is locked" errors.

[Matthew Newville]

First, I apologize in advance for the vague question and thank you in advance for the great SQLAlchemy library, and any help you might be able to give.   I have a GUI application (wxPython) that uses SQLAlchemy and sqlite3 to store state information.  The application connects to network resources, using a wrapped C library that internally makes heavy use of threads.

This application worked (and still works) fine with sqlalchemy 0.6.8, but gives "database is locked" errors on "session.commit()" with 0.7.2 and 0.7.3.   I get the same behavior on both linux (python 2.6) and windows (python 2.6 and 2.7).  

This app definitely needs to communicate with the network resources, and the interaction with that library needs to be wrapped with wx.CallAfter()  in order to isolate network communication from the GUI threads.   I've tried to be careful about separating this from calls to sqlalchemy/sqlite, and am not finding any obvious errors.

Are there any ideas about what changed between 0.6.8 and 0.7.2 that might trigger this change?  Are there any general suggestions on how to resolve this?  It seems the previous questions about "database is locked" are answered with "don't use sqlite".   This application really needs a single, no-server datastore, so that would mean either staying with 0.6.8 indefinitely or not using SQLAlchemy, neither of which seems like a good choice to me.

Again, thanks for any insight.

--Matt

[Michael Bayer]

Yes, the default pool implementation for a file-based database changes to NullPool in 0.7 - such that each call to connect() on an engine returns a brand new SQLite connection.  Not sure why this leads to "database is locked" issues though whereas you didn't have them earlier - ideally you should ensure things are using a single connection all the way through within a given thread.

You can pass pool_threadlocal=True to create_engine() which will cause the NullPool to return the same connection for all calls to connect() or execute() within a given thread and would act pretty much like the defaults in 0.6 - or you could use SingletonThreadPool specifically which is what 0.6 used.

Again, thanks for any insight.

--Matt

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/T3WBK-ZU6fIJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Matthew Newville]

Michael,

Thanks!  Using create_engine(..., poolclass=SingletonThreadPool) works perfectly, though  create_engine(..., pool_threadlocal=True) still showed similar database is locked on 'session commit'

FWIW, I do have a single engine, connection, and session in the application, as with (now):

    self.engine = create_engine('sqlite:///%s' % dbname,
                                poolclass=SingletonThreadPool)
    self.conn   = self.engine.connect()
    self.session = sessionmaker(bind=self.engine)()
    self.metadata =  MetaData(self.engine)
    self.metadata.reflect()

I haven't been rigorous in checking that only one wxPython thread is using the connection, as the wx code has lots of wx.CallAfter() to allow callbacks from the networking library to call into wx code, which then might want to write into the database.   My suspicion is that this is the root cause of the issue.

Anyway, I very much appreciate the quick, helpful response and fantastic library.

--Matt