3 ms·

I found the transactional aspect surprisingly difficult, especially with concurrent file access. SQLITE_BUSY handling was quite hard at the time. I know that se

I found the transactional aspect surprisingly difficult, especially with concurrent file access. SQLITE_BUSY handling was quite hard at the time. I know that serialization failures are expected in transaction processing, but for SQLite it was very difficult to tell persistent failures (say, due to self-deadlock) apart from transient concurrent update problems. For transient failure, you can re-execute the closure defining the transactional operation, but for persistent failure, that's of course pointless. Part of the problem is that sqlite3_stmt combines aspects of both prepared statements and result sets. There is a tendency to keep them around to cache the compiled bytecode (prepared statement), but your might code might stop mid-iteration (result set), maybe holding a lock at this point. This can lead to surprising lock-upgrade failures. In the end, I wrote extensive error reporting using sqlite3_next_stmt, sqlite3_stmt_busy, sqlite3_sql, just to weed out those issues. The entire transaction retry code I wrote is full of optional logging and many comments, even though it was just for my own personal use. Before that, I wrote transaction retry logic for PostgreSQL, and that was so much easier (but it was before fully SERIALIZABLE transactions arrived). The other surprise is that “ A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash.” (https://sqlite.org/pragma.html#pragma_synchronous https://sqlite.org/pragma.html#pragma_synchronous), but that wasn't relevant to my application.

HelloNurse 3y ago
If you retry your write several times and it doesn't succeed you can tell the user it is a persistent failure without agonizing too much over the diagnosis: it is persistent enough to be a significant problem, even without proof that it is an application bug. Who would attempt to make concurrent writes to an application document format? And how wouldn't such an attempt be a user mistake? Failing to write is the solution, not the problem.
- fweimer 3y ago
  These concurrency failures in transaction processing can be quite rare, but you have to fix them if you want 24/7 unattended operation. SQLite only has timeout-based conflict detection, so you basically have to decide whether you want to wait 60 seconds (or so, depends on how glitchy your storage is) before reporting a potential self-deadlock, which isn't great for development, or risk failing unnecessarily when actually running the job. I had no idea what the right timeout was, which is why I wrote some of the custom self-deadlock detection logic. I think I got to run it completely reliable in the end (no false aborts even under load), but as I said, it was surprisingly hard. By the way, concurrent read/writes on locally stored documents happen, even on single-user machines. If the reading process uses the SQLite structure (say a document indexer that knows about the format), it has to take some locks and may also need to flush data from the WAL log (depending on implementation details). At this point you have to deal with concurrency issues in the application, too. Unless you rewrite the entire document from scratch on every save and put it in place with an atomic rename (which I ended up doing for a different application, not the transaction-processing one). But that loses some of the advantages of SQLite.
  - HelloNurse 3y ago
    You are clearly discussing a shared database for concurrent transaction processing ("you want 24/7 unattended operation"), not people editing application document files. Setting aside technological details, multiple clients operating on independent rows of the same table can only, at worst, waste time by retrying a transaction, while multiple concurrent users attempting to modify the same document are asking for trouble, and if they succeed they probably succeed at corrupting the document. Even without lock contention the aggregate document state can be incoherent (for example, Alice and Bruno edit a text, but they accidentally modify the same section and the latest save prevails and nobody notices).

HelloNurse 3y ago

If you retry your write several times and it doesn't succeed you can tell the user it is a persistent failure without agonizing too much over the diagnosis: it is persistent enough to be a significant problem, even without proof that it is an application bug. Who would attempt to make concurrent writes to an application document format? And how wouldn't such an attempt be a user mistake? Failing to write is the solution, not the problem.

fweimer 3y ago
These concurrency failures in transaction processing can be quite rare, but you have to fix them if you want 24/7 unattended operation. SQLite only has timeout-based conflict detection, so you basically have to decide whether you want to wait 60 seconds (or so, depends on how glitchy your storage is) before reporting a potential self-deadlock, which isn't great for development, or risk failing unnecessarily when actually running the job. I had no idea what the right timeout was, which is why I wrote some of the custom self-deadlock detection logic. I think I got to run it completely reliable in the end (no false aborts even under load), but as I said, it was surprisingly hard. By the way, concurrent read/writes on locally stored documents happen, even on single-user machines. If the reading process uses the SQLite structure (say a document indexer that knows about the format), it has to take some locks and may also need to flush data from the WAL log (depending on implementation details). At this point you have to deal with concurrency issues in the application, too. Unless you rewrite the entire document from scratch on every save and put it in place with an atomic rename (which I ended up doing for a different application, not the transaction-processing one). But that loses some of the advantages of SQLite.
- HelloNurse 3y ago
  You are clearly discussing a shared database for concurrent transaction processing ("you want 24/7 unattended operation"), not people editing application document files. Setting aside technological details, multiple clients operating on independent rows of the same table can only, at worst, waste time by retrying a transaction, while multiple concurrent users attempting to modify the same document are asking for trouble, and if they succeed they probably succeed at corrupting the document. Even without lock contention the aggregate document state can be incoherent (for example, Alice and Bruno edit a text, but they accidentally modify the same section and the latest save prevails and nobody notices).