Comment by groue | Hacker News Reader

Comment by groue | Hacker News Reader

Parent Full thread

It is always safe, and by "safe" I mean "safe for data". You won't have to deal with data corruption. Precisely, see "How To Corrupt An SQLite Database File": https://www.sqlite.org/howtocorrupt.html

Now concurrent accesses from different processes/connections can lead to runtime errors (SQLITE_BUSY), because the database happens to be locked by one connection.

Those errors are greatly reduced by the WAL mode (https://sqlite.org/wal.html) which provides ultra-robust single-writer/multiple-readers semantics:

- Writes can not happen concurrently (SQLITE_BUSY).

- One can reduce the occurrences of such SQLITE_BUSY errors by using a built-in timeout (https://www.sqlite.org/c3ref/busy_timeout.html).

- Several reads can happen concurrently, including with writers.

- A writer connection can enter the "Serializable" isolation level.

- A reader connection can enter the "Snapshot Isolation" level.

For more details, see https://www.sqlite.org/isolation.html

During all the years I've been developing the GRDB library (https://github.com/groue/GRDB.swift), I could never see SQLite fail its documented guarantees. This made it possible to build one of the most concurrency-focused SQLite toolkit for Swift, and I'm pretty happy with it (https://github.com/groue/GRDB.swift/blob/master/Documentatio...).

Oh excellent, I wasn't sure the "snapshot isolation" level existed (the WAL kind of implied it, but I wasn't sure). SQLITE_BUSY only happens if a write lock is kept for longer than the readers' configured timeout, right? It shouldn't happen for short writes?

What's cool with WAL mode is that SQLITE_BUSY won't happen for readers (except very rare scenarios: https://www.sqlite.org/wal.html#sometimes_queries_return_sql...)

One should only expect SQLITE_BUSY for writes (if a writer is already holding the lock, and the busy timeout expires before the other writer releases the lock). So yes, prefer short writes, or adjust your timeout. Generally speaking, SQLITE_BUSY can not be 100% prevented for writes.

Yeah, that makes perfect sense. For most apps, it's fairly easy to keep writes short, so SQLite is a great fit.

Yes. Now, many short writes look exactly as one very long write from the point of view of an enqueued write that is waiting for its turn :-) I don't quite remember how fair is SQLite scheduling, in practice.

Hm, right. At that point, I guess it's time to increase throughput, but it's a fair observation.

I don’t think there is any scheduling. Each connection polls to see if the write lock is available up to the max busy timeout setting.

The connection polls at these intervals: static const u8 delays[] = { 1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100 };

So, if you are using the default 5 second timeout, and you are trying to acquire a lock while an exclusive lock is held, you will wait 1 second, then 2 seconds, then 5 seconds, and timeout. I’m not sure if you timeout after 3 total seconds have elapsed, or sometimes after the 2 and sometimes after the 5.

If you have a thread running many fast queries in a loop you can deny access to another thread that needs a lock. The other thread may get lucky and poll for the lock at the exact moment in between locks from the other thread, but it might not.

Reply on news.ycombinator.com