3 ms·
If you start using locking, you'll encounter many more serialization failures. Similarly for higher isolation levels. This means that you need to retry transact
by fweimer 3y ago
If you start using locking, you'll encounter many more serialization failures. Similarly for higher isolation levels. This means that you need to retry transactions, and doing that can be quite hard. Many years ago, I wrote some helper code to retry transactions on transient failures for PostgreSQL (which was unnecessarily hard at the time because the error codes that are eligible for transaction retry were not documented clearly). But even with that taken care of, you still had to think carefully about non-transactional side effects (such as sending mail) when writing application logic. A neat side effect was that you could restart the PostgreSQL server without impacting running applications (and today, you could probably even kexec a new kernel before the application timeout kicks in).
I suspect with the current preferences to avoid exceptions, writing for automated retry becomes quite a bit harder. And I couldn't really get automated transaction retry to work for SQLite while still caching statement handles, treating them as prepared statements.
- senderista 3y agoThat's why automated retry on concurrency failures should be a built-in feature of the database (or at least of the DB client). It should be easy to register your own commit (for side effects that can be deferred until success) or abort (for side effects that can't be deferred but can be undone) handlers for a given transaction.
- fweimer 3y agoI don't think you can have automated retry because if the application logic lives outside the database and is not subject to its transaction processing (which is the common programming pattern today, I think), the database has to arrange for a re-run of that logic if any of the observed database state changes (such as the followers_count returned from the SELECT statement in the example). This means that the code implementing the application code has to be ready to execute multiple times without ill effects, and application programmers need to be aware of that.
- senderista 3y agoThat's true, that's why the client API should allow you to submit a callable object of some sort (e.g. a C++ lambda) that is automatically wrapped in a DB transaction and allows you to register commit and abort handlers as I described. Here's such an API that I'm currently working on: https://senderista.github.io/atomik-website/ https://senderista.github.io/atomik-website/.