Feature request, PRAGMA for setting default transaction mode
Hello SQLite maintainers,
I am building a middleware application that uses SQLite as a database engine. I am using the WAL mode and setting a busy handler which help a lot with concurrency. My main issue though is that deferred transactions result in many database locked errors. I am trying to overcome that by injecting "immediate" whenever my code sees a transaction coming from the client code. It would be very helpful and more resilient if I could globally change the default transaction mode on the connection level. Something like:
PRAGMA default_transaction_mode = immediate;
Would something like this be useful to other SQLite users? would the maintainers be interested in implementing this?
(2) By Richard Damon (RichardDamon) on 2021-06-20 18:47:23 in reply to 1 [link] [source]
Unless you really do have an unusual application that almost all transactions will promote themselves to a write transaction, I know of few better ways to kill your throughput then to make all transactions BEGIN IMMEDIATE;
This basically says that no transactions will be able to overlap as the BEGIN IMMEDIATE; will wait for any previous BEGIN IMMEDIATE; transaction to finish and will prevent the next one from starting until it is done.
You will basically get ZERO concurrencies.
Not very unusual I would say, that patterns I am seeing are most transactions being read/write or pure write, while pure reads are one offs. This is in part due to web frameworks auto-wrapping writes in transactions and not doing the same for reads.
I understand the effect on concurrency, but what concerns me more is bubbling up the locked database errors to the client code and letting it deal with exceptions not related to the business logic. Trying to solve by running after queries and trying to convert begin to being immediate is not optimal and error prone.
But regardless of my use case, I believe that controlling the default behavior is something desirable on its own, not all use cases are born equal and this particular aspect of the SQLite library is a very important lever when one is balancing concurrency vs simplicity.
And let me throw that in for good measure: make sure the begin_concurrent branch supports adding "concurrent" as a default transaction option. Something I myself will also benefit from in a different context, so as others might do.
Notwithstanding this sort of thing being a good idea or not, the web framework developers should be addressing their defective usage of transactions and fixing them.
That is to say that the solution to the problem of the water coming out of your hot water tap being "not hot enough" is not to "install a great big heater in the lake from which the water is drawn" but rather to fix your own water heater and plumbing.
While the default transaction type for unadorned BEGIN commands is DEFERRED, this has been the case for almost half a century. Children implementing "web frameworks" have no excuse for not knowing this state of affairs that has existed since they were merely a gleam in their daddy's eye any more than they have an excuse for not knowing that water is wet.