File locks in SQLite
Question: I'm writing my first SQLAlchemy (0.6.8)/Python (2.7.1) program, sitting on top
of SQLite (3.7.6.3, I think), running on Windows Vista.

In order to perform unit-testing, I am pointing SQLite to a test database, and
my unit-test scripts routinely delete the database file, so I am continuously
working with a known initial state.

Sometimes my (single-threaded) unit-tests fail to remove the file:

    
    
    WindowsError: [Error 32] The process cannot access the file because it is being used by another process
    

The only process that uses the file is the unit-test harness. Clearly, some
lock is not being released by one of my completed unit-tests, preventing the
next unit-test in the same process from deleting the file.

I have searched all the places I have created a session and confirmed there is
a corresponding session.commit() or session.rollback().

I have searched for all session.commit() and session.rollback() calls in my
code, and added a session.close() call immediately afterwards, in an attempt
to explicitly release any transactional locks, but it hasn't helped.

Are there any secrets to ensuring the remaining locks are removed at the end
of a transaction to permit the file to be deleted?


Answer: Someone had a similar problem: <http://www.mail-
archive.com/sqlalchemy@googlegroups.com/msg20724.html>

You should use a NullPool at the connection establishement to ensure that no
active connection stay after `session.close()`

    
    
    from sqlalchemy import create_engine
    from sqlalchemy.pool import NullPool
    
    to_engine = create_engine('sqlite:///%s' % temp_file_name, poolclass=NullPool)
    

Reference:
<http://www.sqlalchemy.org/docs/06/core/pooling.html?highlight=pool#sqlalchemy.pool>

This is only required in SQLAlchemy prior to 0.7.0. After 0.7.0, this became
the default behaviour for SQLite. Reference:
<http://www.sqlalchemy.org/docs/core/pooling.html?highlight=pool#sqlalchemy.pool>

