Caveats with sqlite in-memory databases + transactions?

[Ben Hearsum]

Hi all,

I've been writing some tests for an application that makes use of
transactions and came across a strange issue: the Transactions in my
tests seemed to be committed before commit() was called. After some
head scratching I reduced the problem to a minimal test case and found
that everything works as I expected it to if I used a real file, but
not if I use an in-memory database.

Based on my new understanding of in-memory databases I _think_ this
makes sense, because there's no real way to "lock" them or have
multiple, separate, connections -- but I would really appreciate
someone confirming this, or otherwise explaining what's going on here.

Here's my test case and the output of it for an in-memory and a real
file database:
[] bhearsum@voot:~/tmp$ cat test2.py
#!/usr/bin/env python

from sqlalchemy import Table, Column, Integer, MetaData, create_engine

import sys
engine = create_engine('sqlite:///%s' % sys.argv[1])
metadata = MetaData(engine)
t = Table('test', metadata, Column('foo', Integer), Column('bar',
Integer))
t2 = Table('test2', metadata, Column('baz', Integer), Column('crap',
Integer))
metadata.create_all()
t.insert().execute(dict(foo=1, bar=100))
t.insert().execute(dict(foo=2, bar=200))

conn = metadata.bind.connect()
trans = conn.begin()
conn.execute(t.update(values=dict(bar=500)).where(t.c.foo==1))
print engine.execute(t.select()).fetchall()
trans.commit()
[] bhearsum@voot:~/tmp$ python test2.py :memory:
[(1, 500), (2, 200)]
[] bhearsum@voot:~/tmp$ python test2.py newdb.db
[(1, 100), (2, 200)]

[Michael Bayer]

you're seeing the behavior of the engine as involves a SQLite :memory: database, which only exists in the state of one DBAPI connection at a time and therefore is impossible to use for testing transactional concurrency.

http://www.sqlalchemy.org/docs/dialects/sqlite.html#threading-pooling-behavior

This is slightly out of date: SingletonThreadPool only applies to :memory: databases , so with file-based you're OK

http://www.sqlalchemy.org/trac/wiki/FAQ#IamusingmultipleconnectionswithaSQLitedatabasetypicallytotesttransactionoperationandmytestprogramisnotworking

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> 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.
>

[Ben Hearsum]

Ahhh, this makes so much more sense now. Thank you for your quick
reply, and the links to documentation!

On Sep 1, 9:48 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
> you're seeing the behavior of the engine as involves a SQLite :memory: database, which only exists in the state of one DBAPI connection at a time and therefore is impossible to use for testing transactional concurrency.
>

> http://www.sqlalchemy.org/docs/dialects/sqlite.html#threading-pooling...

>
> This is slightly out of date: SingletonThreadPool only applies to :memory: databases , so with file-based you're OK
>

> http://www.sqlalchemy.org/trac/wiki/FAQ#Iamusingmultipleconnectionswi...