CODEWITHSUNDEEP
pythonsqlitesqlalchemy
Oddthinking
Oddthinking

Reputation: 25272

File locks in SQLite

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?

Upvotes: 5

Views: 1199

Answers (2)

Martijn Pieters
Martijn Pieters

Reputation: 1121256

Do you require shared access to the database during unit tests? If not, use a in-memory SQLite database for those tests. From the SQLAlchemy documentation:

The sqlite :memory: identifier is the default if no filepath is present. Specify sqlite:// and nothing else:

# in-memory database
e = create_engine('sqlite://')

No need to manage temporary files, no locking semantics, guaranteed a clean slate between unit tests, etc.

Upvotes: 2

Kaltezar
Kaltezar

Reputation: 721

Someone had a similar problem: http://www.mail-archive.com/[email protected]/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

Upvotes: 6

Related Questions