5

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?

Improve this question

2 Answers 2

Reset to default
6

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Sorry, I had misunderstood the context. I suggested a better answer, but can't test it at the moment.
Thank you. Looks like this was fixed in 0.7.0. I edited the answer to reflect that.
2

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.

Improve this answer

9 Comments

this is an good suggestion. I'm having trouble seeing how I would implement it, though. I am retrofitting SQLAlchemy to an existing application. There is no database context parameter being passed around, so disparate objects are calling create_engine separately for each transaction, even though they are in the same thread (during unit-tests, at least). If they all created separate in-memory databases, the tests would fail. Performance is probably suffering, but it is not a huge issue. Does that count as shared-access?
If you have too many places that all create their own engine then yes, that sounds like using the in-memory database would not be a good idea. The code cannot be refactored to use a utility that provides the engine? There really is no need to create multiple engines unless you have multiple threads.
Ha, when you put it that way, guess what! I do that already! I have a function to create the engine, which checks to see if the thread owns one (thread local storage) else creates a new one. So, yes, I could use your technique (with the proviso, that unit tests remain single-threaded). Thank you.
I have seen various inconsistencies between SQLite in-memory and on file (mainly transaction related because of the locking behavior). So you should always test your production environment, at least on CI (see dev/prod parity).
You run into those differences only in case of wrong usage. E.g. starting a new transaction context in the same thread while another transaction is not yet committed. Works fine in-memory while the second transaction blocks in case of file-based db access. So I see these differences only as a reason not to run tests in-memory.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.