4 ms·

In WAL mode you can write from multiple threads without any problem, but you need to keep serializability in mind. Specifically, no concurrent write transaction

In WAL mode you can write from multiple threads without any problem, but you need to keep serializability in mind. Specifically, no concurrent write transactions are allowed. In other words, you can't have a transaction start at X, have another transaction start at X+1, issue DML and commit, and expect your original transaction from X being able to commit - it will never be able to. Some SQLite wrappers (notably, Python's) mess with the start and rollbacks of transactions and make this appear buggy.

oefrha 7y ago
> Some SQLite wrappers (notably, Python's) mess with the start and rollbacks of transactions and make this appear buggy. @coleifer (author of peewee) has a pretty informative blog post exploring — in part — transactional semantics, which might be of value to people who want to know more about this. https://charlesleifer.com/blog/going-fast-with-sqlite-and-python/ https://charlesleifer.com/blog/going-fast-with-sqlite-and-py...
- mox1 7y ago
  +1 for @coleifer, peewee is an awesome ORM, he knows his DB stuff very well.
platz 7y ago
Even in WAL mode, you can still get a SQLITE_BUSY error even when just trying to open a new connection. The application I worked on could not tolerate these busy exceptions so we had to acquire an exclusive global mutex before attempting to open new connections to sqlite and make sure to release it at soon as the operation was done.
- liuliu 7y ago
  WAL mode doesn't really have "read-only" connection (if you try to open a read-only connection by marking as READ_ONLY with sqlite_open_v2, it will error out for WAL mode). A "supposedly" read-only connection can end up doing writes for example, consolidating WAL log into the main DB file. That can cause SQLITE_BUSY errors. You pretty much have to set the sqlite_busy_timeout in any cases.
stephen82 7y ago
They are working on it; please check for 'begin-concurrent' and 'wal2' branch names here https://www.sqlite.org/cgi/src/brlist https://www.sqlite.org/cgi/src/brlist
kccqzy 7y ago
Python's wrapper is one of the worst. It attempts to parse the SQL you are trying to execute and automatically send BEGIN or COMMIT to the database. Setting the "isolation_level" property (badly named) to None makes things better.

oefrha 7y ago

> Some SQLite wrappers (notably, Python's) mess with the start and rollbacks of transactions and make this appear buggy. @coleifer (author of peewee) has a pretty informative blog post exploring — in part — transactional semantics, which might be of value to people who want to know more about this. https://charlesleifer.com/blog/going-fast-with-sqlite-and-python/ https://charlesleifer.com/blog/going-fast-with-sqlite-and-py...

mox1 7y ago
+1 for @coleifer, peewee is an awesome ORM, he knows his DB stuff very well.

platz 7y ago

Even in WAL mode, you can still get a SQLITE_BUSY error even when just trying to open a new connection. The application I worked on could not tolerate these busy exceptions so we had to acquire an exclusive global mutex before attempting to open new connections to sqlite and make sure to release it at soon as the operation was done.

liuliu 7y ago
WAL mode doesn't really have "read-only" connection (if you try to open a read-only connection by marking as READ_ONLY with sqlite_open_v2, it will error out for WAL mode). A "supposedly" read-only connection can end up doing writes for example, consolidating WAL log into the main DB file. That can cause SQLITE_BUSY errors. You pretty much have to set the sqlite_busy_timeout in any cases.

stephen82 7y ago

They are working on it; please check for 'begin-concurrent' and 'wal2' branch names here https://www.sqlite.org/cgi/src/brlist https://www.sqlite.org/cgi/src/brlist

kccqzy 7y ago

Python's wrapper is one of the worst. It attempts to parse the SQL you are trying to execute and automatically send BEGIN or COMMIT to the database. Setting the "isolation_level" property (badly named) to None makes things better.