add a note regarding sqlite file locking

[Varialus]

Flushing data to disk with SQLite locks the whole database until a commit has been performed. Because the autoflush setting is enabled by default, if a flush goes unnoticed, the database will stay locked, blocking all other threads. SQLite is commonly used while evaluating SQLAlchemy, so the your default settings should work seamlessly with it.

Risk Mitigation Ideas

1. If the autoflush value is manually specified while making a session, then if an SQLite engine is being used, autoflush should be set to False by default.

2. Add some bright warnings to your documentation.

3. Don't allow autoflush to be set while using SQLite.

4. Disable autoflush by default regardless of the database.

5. Change the behavior of SQLite to not lock the whole database while performing writes to disk. The optional support for write-ahead logging which was added in SQLite version 3.7.0 may be of interest.

6. Flush to a cache.

7. Demand that SQLite change their default behavior to suit SQLAlchemy.

[Mike Bayer]

Replying to varialus:

Flushing data to disk with SQLite locks the whole database until a commit has been performed. Because the autoflush setting is enabled by default, if a flush goes unnoticed, the database will stay locked, blocking all other threads. SQLite is commonly used while evaluating SQLAlchemy, so the your default settings should work seamlessly with it.

Risk Mitigation Ideas

1. If the autoflush value is manually specified while making a session, then if an SQLite engine is being used, autoflush should be set to False by default.

The SQLAlchemy Session's normal behavior is to work in "transactional" mode, with autoflush. It would lead to great user confusion if we changed these defaults based on which backend is in use, not to mention it's not even possible, a single Session can talk to many databases at once.

2. Add some bright warnings to your documentation.

Perhaps, though my impression here is that you're expecting some concurrency behavior from SQLite that's well known to be not a strong use case for this backend. SQLite uses a file for storage, which it locks while in a transaction. If one understands that the Session works in a transaction by default (which is how the whole ORM tutorial is structured), and that SQLite locks on writes, they'd understand this implication.

We do add some notes to the docs for particular caveats and it wouldn't be controversial to add an extra bullet to http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html.

That said, SQLAlchemy doesn't absolve the developer of knowing how the backend database works - our philosophy is actually the exact opposite of that. The documentation for the sqlite3 driver as well as SQLite itself are pretty clear about this behavior.

http://docs.python.org/library/sqlite3.html#sqlite3.connect

"When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds)."

As well as:

http://www.sqlite.org/whentouse.html

"Situations Where Another RDBMS May Work Better - High Concurrency

SQLite uses reader/writer locks on the entire database file. That means if any process is reading from any part of the database, all other processes are prevented from writing any other part of the database. Similarly, if any one process is writing to the database, all other processes are prevented from reading any other part of the database. For many situations, this is not a problem. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution."

3. Don't allow autoflush to be set while using SQLite.

autoflush is a tremendously useful feature and for the vast majority of SQLite applications that don't require lots of concurrency it would be a pretty serious limitation to artificially disallow this.

4. Disable autoflush by default regardless of the database.

autoflush was made true by default as of around 0.4 or 0.5. The behavior is far more useful on than off for the vast majority of cases.

5. Change the behavior of SQLite to not lock the whole database while performing writes to disk. The optional support for write-ahead logging which was added in SQLite version 3.7.0 may be of interest.

You can pass whatever options sqlite3 allows via the "connect_args", documented at http://docs.sqlalchemy.org/en/latest/core/engines.html#custom-dbapi-args . We already have added some workarounds for sqlite3's quirky support of the underlying database regarding the opposite use case, enabling fully serializable transactions: http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#serializable-transaction-isolation. So if there are further features that sqlite3 itself doesn't support easily but can be supported with workarounds, they can be documented here.

6. Flush to a cache.

if you turn off autoflush and flush() as needed, you're sort of getting this behavior already, though obviously you can't query that cache. It might be a fun project to point your Session at a :memory: database, then write an after_commit() hook that somehow replicates out to a file-based database, but this would be something you'd roll yourself.

7. Demand that SQLite change their default behavior to suit SQLAlchemy.

i think the use cases for SQLite are laid out pretty clearly at http://www.sqlite.org/whentouse.html and concurrency is not one of them.

[Varialus]

SQLite is supposed to be able to handle 1,000,000 website hits per day and locks should last mere milliseconds. Autoflush obliterates the performance of SQLite to the point where I would not trust it with my two threads and a few hundred combined steadily distributed small reads and writes per day. I was absolutely shocked that the default setting would leave the entire database locked from the start of an automatically performed action to the end of a manually performed action. But I was even more shocked that there were no bright bold flashing warnings wherever the documentation mentions threads. It's as if you enjoy watching the havoc of bad alchemy in the faces of poor n00bs who dare utilize your otherwise fantastic library. I really really like SQLAlchemy; and the documentation is the more critical issue, so I'm sincerely glad it's going to be addressed, but I'm not going to lie; I am disappointed that I haven't convinced you to fix what I consider to be a critical design flaw. It's not going to be a problem for you or me or for low level database engineers, but it will be a problem for the ever growing community of Python developers looking for a no nonsense ORM that just works.

[Varialus]

The fact that the whole database gets locked is not a problem so long as it only locks for a short time. The problem is that the database gets locked automatically and then is required to be unlocked manually, which leaves way way way too much room for accidents that cause locks which need only last a few hundredths of a second, to end up accidentally taking many thousands of milliseconds before unexpectedly expiring because the developer understandably expects that a modification without a commit wouldn't lock the whole database for 5,000 whole milliseconds.

So accordingly, another possible solution would be to enable autocommit by default, because after all, a flush already is half an atomic commit. Why let people by default accidentally leave it hanging half way done?

Or you could require a lockWholeDatabase=True argument that must be manually added to any function that automatically flushes to the database. Because although this would be quite ridiculous, it would be less ridiculous than by default automatically locking the database and then requiring that it either be manually unlocked or take thousands of milliseconds to time out.

[Mike Bayer]

So first off, let me note something regarding tone - we try, as hard as we possibly can, to keep SQLAlchemy discussions free of inflammatory and/or personal remarks, and focus just on the software at hand - so if you could refrain from taking guesses as to whether or not I "enjoy watching the havoc of bad alchemy in the faces of poor n00bs" - comments like that only serve to raise our collective blood pressure and pollute the discussion with reactionary issues. I would hope that it's obvious that SQLAlchemy has been an intense labor of love for me and others over the past 6 years, and while an OSS project is going to be continuously challenged by decisions that by necessity will displease some while pleasing others, rest assured the architectural decisions you see, particularly these, were not made overnight, or in a vacuum. The autoflush and autocommit decisions came to their current state after several years and you'll be able to see a detailed description of their rationale in the the soon-to-be-released volume two of "The Architecture of Open Source Applications" http://www.aosabook.org/.

The next point that comes to mind here is the story of SQLAlchemy's early development, and the introduction of our first (and so far only) competitor that came close to challenging our feature set, an ORM called Storm. Storm's creator was originally a contributor to SQLAlchemy, back in the days when SQLAlchemy ''did not have'' an autoflush feature at all, nor the non-autocommit-by-default mode we standardize on now - there was only autocommit, and if you wanted you could say begin()/commit() to actually demarcate a transaction. The objects within the Session were never expired automatically. The rationale for these decisions were a. I didn't know about autoflush, b. I felt that transactions were like an "optional" thing, and c. the focus was on making as few trips to the database as possible. Being focused on web applications, expiring objects just because a transaction ended seemed insane to me.

But as it turned out, this contributor was not satisfied with these decisions. Like you, he felt that these decisions were fundamental design flaws, and when he went out to write Storm, he sought to improve on many things he didn't like about SQLAlchemy, but the one that he mentioned explicitly was its poorly designed transactional model. Storm has since gone on to not become terribly popular; two reasons for this are because I took it on myself to increase our development velocity by leaps and bounds to ensure we stayed ahead of Storm in every way, and the other is because Storm itself has an extremely slow development velocity, which I almost think may have been a response to SQLAlchemy's huge push as a result of this product.

But what's interesting about Storm for this issue specifically ? Three things. One is, it is autoflush by default, always. There is no way to turn it off. Not only that, but it is non-autocommit by default, always. In both of these areas, Storm has exactly the defaults you're saying are "design flaws", but unlike SQLAlchemy, has no way to turn them off - it is more of a purists' tool in that this is the most "correct" way to link the state of a field of objects in memory to a DBAPI connection, which is by its nature a transactional connection. I came to agree with these decisions, noting that not only Storm has this behavior, but so does Hibernate, which is deployed in a vast amount of production applications. So these design decisions are really not SQLAlchemy's idea at all, and are intrinsic to what is probably the most widely used ORM in the world.

But what's the "third" thing I haven't mentioned yet ? It is that today, Storm is not used not nearly as widely as SQLAlchemy, but is in fact used in two high profile projects - GNU Mailman and Zed Shaw's Lamson mail server, both written and maintained by extremely experienced developers who are both much more knowledgeable than I am about things. And both products use Storm with SQLite as their storage engine. The irony here is that Storm is quite popular with SQLite, because it is a smaller and faster ORM than SQLAlchemy, to suit SQLite's small and fast model. And it's autoflush/non-autocommit, always. This works because SQLite's primary use case is a non-concurrent one. It's not to say you can't use SQLite with some concurrency, but if you do, some tuning should be expected. SQLAlchemy at least makes this possible.

So by version 0.5, we had implemented autoflush, implemented the "transaction by default" mode of the Session, and implemented expiration after transactions end. Overnight, a whole class of user confusion disappeared - no longer were people annoyed at having to manually call "flush()" all the time; no longer were people confused by when/where to use transactions - the answer became simple, "always"; no longer was there any ambiguity when the state of the objects in the Session should be expired, and no longer did we get daily emails about "I have stale state in my Session!". We duplicated Storm's model exactly, with the exception that the whole thing can be turned off, changed the documentation to focus primarily on telling just one story, instead of "well it works like this, but you can change this, or that" - trust me, people do not want to hear that stuff. They want "how should I be using this?" One obvious way to do it. These decisions were made after much deliberation with a lot of people and they've gone very well.

So that's background.

In this case, it seems you're focused on this one particular issue you hit, that of SQLite's locking behavior. That's just the way SQLite is. When users evaluate SQLAlchemy, they read the tutorial, which being only a single thread works great. Then they decide to try something for real, like a multithreaded application - and to be honest the first thing they do most of the time is replace "sqlite://" with "mysql://" or "postgresql://"- since that's what people usually use for a concurrent application. They also know that they should be developing with echo=True, seeing the SQL go by, and if they are using SQLite they'd know it's locking a file as soon as it writes. In fact the Python sqlite3 module contains what many of our users have complained is a hacky behavior on their part - it doesn't actually tell the sqlite engine to start a transaction until the first DML, that is INSERT,UPDATE, DELETE is detected. Some of our users have disagreed with this as a default behavior since it's essentially a premature optimization, and you can see the discussion at http://code.google.com/p/pysqlite/issues/detail?id=21 and http://bugs.python.org/issue9924. I can appreciate that you came into the product with a certain perspective, one where you expected that SQLite's poor behavior regarding concurrency would be magically massaged out, but that's just not the philosophy of SQLAlchemy. There are other perspectives, just as valid, where other users are coming from. It's a fact of OSS that a large user base is going to come from a wide variety of perspectives on things, and a project can't always suit them all simultaneously (though nobody comes closer than SQLAlchemy does to appeasing a wide variety of often-conflicting perspectives on usage).

One reason SQLAlchemy has done so well is that it doesn't pander 100% to the so-called "n00b" community - SQLA is really a tool oriented towards people that are well versed in SQL and relational databases already, providing an automation layer to the tasks at hand. This focus is distinctly different from that of systems like the Django ORM and web2py - these projects seek to conceal the details of the database and attempt to just solve whatever issues, targeting the so-called "no-nonsense just-works" spot you mention. SQLAlchemy's philosophy is that this is more or less the pot of gold at the end of a rainbow; you can create the illusion of such a thing, but as the real world becomes apparent during application construction these implicit decisions intended to shield the developer from understanding what's going on, that pot of gold is never found. We've been building on this philosophy for many years now and it's almost a given now that we're considered to be the more advanced tool versus the Django ORM. It's specifically this philosophy, that we aren't engaging in the ultimately impossible task of "hiding" the database's behavior and it's quirks that have led us to slowly claim this position and remain competitive despite our model that demands the developer be responsible for deciding how to deal with various quirks.

Throughout all of this, autoflush is optional, autocommit is available, while the latter option is something we advise against, as it basically renders autoflush and auto-expire useless, it's not going anywhere and you're free to use it for your specific use case.

[Mike Bayer]

Replying to varialus:

So accordingly, another possible solution would be to enable autocommit by default, because after all, a flush already is half an atomic commit. Why let people by default accidentally leave it hanging half way done?

what's the "accident" you refer to here, that they'd open a Session, do some stuff, then just leave it open without rollback or commit ? The reason the Session starts a transaction immediately, and keeps it going the whole way, is so that the full series of operations you're performing are isolated, including whatever locks need to be established as the result of a SELECT. http://code.google.com/p/pysqlite/issues/detail?id=21 for SQLite refers to how sqlite3's behavior of "appeasing" the user by not starting the transaction until DML is more or less wrong as a default, as it breaks SERIALIZABLE isolation.

It's all a question of where you're coming from. Correct SERIALIZABLE behavior, or behavior that's optimized to some particular concurrency scenario on a backend that needs to lock the entire file in order to do things.

[Former user]

While I think that the bug tracker is not the right place for lengthy discussions, I'll add my 2 cents.

I am using SQLAlchemy and SQLite in a GUI application and the limited concurrency is biting me all the time. I actually try to use autoflush=False and autocommit=True, but it turns out that this is not a really good idea.

What varialus reminded me about is the pain I had before finding this "solution" (which is now creating others problems). It was actually similar to the setting described here:

1. In a background job I collect some data, do a length computation and store some results to the database. The preparation only reads from the database.
2. Some rogue attribute access manages to render the session dirty (I think it was related to ordering_list) which causes the next query to begin a transaction.
3. While the computation is going on, the main thread is getting OperationalError accessing the database.

This is all fine and dandy but it is really hard to track down what actually triggered the flush. That was the reason why I proposed blocking writes in the discussion here: http://thread.gmane.org/gmane.comp.python.sqlalchemy.user/24097

What I am wishing for is that actually writing to the database requires some kind of frame in the source code, like

    users = session.query(User).all()
    with session.begin():
        for user in users:
            user.name = user.name.upper()

Unfortunately this is not allowed with autocommit=False, SQLAlchemy will throw

InvalidRequestError: A transaction is already begun. Use subtransactions=True to allow subtransactions.

This whole problem is caused by PEP 249 which assumes full or no transaction support and does not support the grey area implemented by SQLite.

[Mike Bayer]

Replying to Bluehorn:

In a background job I collect some data, do a length computation and store some results to the database. The preparation only reads from the database. Some rogue attribute access manages to render the session dirty (I think it was related to ordering_list) which causes the next query to begin a transaction. While the computation is going on, the main thread is getting OperationalError? accessing the database.

Yeah concurrency is not SQLite's main use case, and requires special steps to be used in such a way. So if you're doing schemes like this then you have to make sure state isn't changing. If ordering_list is creating dirty state plus flushes that don't actually correspond to anything actually changing, that would be a separate issue. If the objects themselves are not mutated at all, then it's a bug in ordering list.

This is all fine and dandy but it is really hard to track down what actually triggered the flush. That was the reason why I proposed blocking writes in the discussion here: http://thread.gmane.org/gmane.comp.python.sqlalchemy.user/24097

We should have adequate event hooks right now to roll this functionality in a straightforward way. You can catch attribute assignments, you can catch session attachment, or you can look to see if the session had begin() called on it within a before_flush() hook and raise an exception if not. Very easy, here's that:

from sqlalchemy import event, create_engine, Integer, Column, String
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base

@event.listens_for(Session, 'before_flush')
def disallow_nontransactional_flush(session, ctx, instances):
    if not session.transaction:
        raise Exception("no flushes allowed outside of transactions")

Base= declarative_base()

class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    data = Column(String)

e = create_engine("sqlite://", echo=True)

Base.metadata.create_all(e)

s = Session(e, autocommit=True)
a1 = A(data="foo")
with s.begin():
    s.add(a1)

# autoflush is fine as long as nothing is dirty
print s.query(A).all()

# but with dirty state...
a1.data = "bar"

# boom !
print s.query(A).all()

What I am wishing for is that actually writing to the database requires some kind of frame in the source code, like

{{{ #!python users = session.query(User).all() with session.begin(): for user in users: user.name = user.name.upper() }}}

Unfortunately this is not allowed with autocommit=False, SQLAlchemy will throw

InvalidRequestError: A transaction is already begun. Use subtransactions=True to allow subtransactions.

So.....just set autocommit=True then! The pattern you're "wishing" for has been there for most of SQLAlchemy's lifetime. This was SQLAlchemy's original usage model, and while we discourage it for general use today as the "always in a transaction" model is definitely easier for people to understand, autocommit=True is fully supported and isn't going away. It of course makes the ORM harder to use, in that you probably are going to turn expire_on_commit and possibly autoflush off (or just use that recipe above), but if you're looking to try to get SQLite to behave somewhat concurrently and don't mind having to deal with object expiry/possibly flush in a somewhat more manual way, it's there for you to use.

This whole problem is caused by PEP 249 which assumes full or no transaction support > and does not support the grey area implemented by SQLite.

I disagree, pep0249 is actually quite vague about what constitutes a "transaction". The sqlite3 driver, as we've noted earlier, has the default behavior of not starting an actual transaction until DML is issued. The driver definitely needs a way to configure this, so that true SERIALIZABLE behavior can be had for those who want it. Right now you can't get it unless you emit a BEGIN manually.

[Former user]

Replying to zzzeek:

Yeah concurrency is not SQLite's main use case, and requires special steps to be used in such a way. So if you're doing schemes like this then you have to make sure state isn't changing. If ordering_list is creating dirty state plus flushes that don't actually correspond to anything actually changing, that would be a separate issue. If the objects themselves are not mutated at all, then it's a bug in ordering list.

I think that problem went away already. Somehow I managed to write the attribute used for ordering even though it did not change. I don't remember the details though.

We should have adequate event hooks right now to roll this functionality in a straightforward way. You can catch attribute assignments, you can catch session attachment, or you can look to see if the session had begin() called on it within a before_flush() hook and raise an exception if not. Very easy, here's that:

Thanks, that's cool!

[Mike Bayer]

06c950c24993a0ee8366db5257e97bf2af283d46 d598f545850d3493b269317a4428c9d822a2da38

[Mike Bayer]

Removing milestone: 0.7.8 (automated comment)