Multi-threaded SQLite without the OperationalErrors (2017)

Re:Multi-threaded SQLite without the OperationalErrors (2017) by kijin on Thursday May 30, 2019 @12:10AM (#20048301)

> By default, pysqlite opens a transaction when you issue your first write query (it does this by parsing every query you execute), and then committing that transaction when you issue any other query that is not a SELECT, INSERT, UPDATE or DELETE, e.g. CREATE TABLE or PRAGMA.

What's the rationale for this default? Performance? Consistency? Even without the problems mentioned in the article, I'm not sure if I would want my database to commit multi-query transactions without an explicit command to do so.

[ Parent | on HN ]

Re:Multi-threaded SQLite without the OperationalErrors (2017) by ebg13 on Thursday May 30, 2019 @12:17AM (#20048345)

Definitely performance. Writes without a transaction are slow. SQLite's 99.999% use-case is for single thread single access, so you lose little and gain a lot by deferring writes until you actually need them done.

> I'm not sure if I would want my database to commit multi-query transactions without an explicit command to do so.

You can easily disable it by setting the isolation level per https://docs.python.org/3/library/sqlite3.html#controlling-t...

[ Parent | on HN ]