Author aymeric.augustin
Recipients Jeremy Banks, Jim.Jewett, Mark.Bucciarelli, Ronny.Pfannschmidt, adamtj, asvetlov, aymeric.augustin, bulb, dholth, flox, ghaering, monsanto, pitrou, r.david.murray, scott.urban, torsten, tshepang, zzzeek
Date 2014-06-14.15:18:35
SpamBayes Score -1.0
Marked as misclassified Yes
Message-id <1402759115.61.0.959403970093.issue10740@psf.upfronthosting.co.za>
In-reply-to
Content
> If this statement is accurate, the what you are proposing is just a different (presumably clearer) spelling for 'isolation_level = None'?

This statement is accurate but it doesn't cover the whole scope of what I'm attempting to fix.

I'm also trying to address the serialization semantics. SQLite always operates at the serializable isolation level. (I'm talking about the SQL concept of transaction isolation levels, not the unrelated isolation_level parameter in sqlite3.) Currently, since sqlite3 doesn't send a BEGIN before SELECT queries, the following scenario is possible:

- Process A reads data -- and the developer who carefully read PEP 249 expects to start a transaction
- Process B writes data
- Process A writes a modified version of what it read and commits

In this scenario A overwrites B, breaking the serialization guarantees expected in that case. A's initial read should have started a transaction (essentially acquiring a shared lock) and B should have been prevented from writing. 

There are two problems here:

- A's code looks like it's safe, but it isn't, because sqlite3 does some magic at odds with PEP 249.
- If you're aware of this and try to enforce the proper guarantees, sqlite3 still gets in the way.

> I also don't understand why we don't just fix the screwy behavior with regards to savepoint.  It's hard to see how fixing that could be a backward compatibility problem (in a feature release), since you can't use savepoints without isolation_level=None as it stands now.

Of course, that would be a good step. But the problem doesn't stop there. What about other SQLite commands? What about "PRAGMA"? I suspect you'll have a hard time defining and maintaining a list of which commands should or shouldn't begin or commit a transaction. That's why I didn't choose this path.

Furthermore, sqlite3 would still enforce a weird mode where it sacrifices serializable isolation under the assumption that you're having a transactional and concurrent workload but you don't care about transactional isolation. There are other use cases that would be better served:

- by favoring serializability over concurrency (eg. the "application file format" case) -- that's what my "standards" mode does
- by favoring concurrency over transactions (eg. the "web app" and the "I don't care just save this data" cases) -- that's what "autocommit" is for
History
Date User Action Args
2014-06-14 15:18:35aymeric.augustinsetrecipients: + aymeric.augustin, ghaering, pitrou, Jeremy Banks, r.david.murray, zzzeek, asvetlov, flox, adamtj, dholth, torsten, monsanto, scott.urban, tshepang, Ronny.Pfannschmidt, Mark.Bucciarelli, Jim.Jewett, bulb
2014-06-14 15:18:35aymeric.augustinsetmessageid: <1402759115.61.0.959403970093.issue10740@psf.upfronthosting.co.za>
2014-06-14 15:18:35aymeric.augustinlinkissue10740 messages
2014-06-14 15:18:35aymeric.augustincreate