Why is PRAGMA busy_timeout per default 0?
(1) By Adrian (adrian.s) on 2024-01-22 16:14:39 [link] [source]
I feel like it should retry per default (at least for a short amount). Instead of immediately giving up when the database is busy, just try again for a short time. A good value might be something like 1s or 2s.
Especially for WAL databases since there is a chance of it being a problem even if you only do reads: https://www.sqlite.org/wal.html#sometimes_queries_return_sqlite_busy_in_wal_mode
Is there any good reason to not have a default value > 0?
(2) By Larry Brasfield (larrybr) on 2024-01-22 16:19:47 in reply to 1 [link] [source]
Is there any good reason to not have a default value > 0?
Yes. A great many applications using the SQLite library may be relying on that documented default. It's part of the API now.
(3) By Stephan Beal (stephan) on 2024-01-22 16:22:15 in reply to 1 [link] [source]
A good value might be something like 1s or 2s.
That's the crux of the problem: "a good value" is inherently dependent on an application's needs and/or a developer's considered opinion.
For the library, the most objectively correct behavior is to default to 0 and let those applications' developers apply a different value if they need it.
(4) By Adrian (adrian.s) on 2024-01-22 16:30:07 in reply to 2 [link] [source]
That's a good reason to keep the value, but not a reason to have it in the first place.
(5) By Adrian (adrian.s) on 2024-01-22 16:32:50 in reply to 3 [source]
But 0 only makes sense for debugging, as far as I can tell. Shouldn't the default be a value that is appropriate for usage?
(6) By Larry Brasfield (larrybr) on 2024-01-22 16:37:30 in reply to 5 [link] [source]
A zero retry strategy can make sense for some applications.
Depending upon whether the application is interacting with a human or acting as an automaton with high throughput, a wide range of timeouts would be appropriate.
The whole issue is pretty academic now that the behavior is part of the public API and has been for years.
(7) By Stephan Beal (stephan) on 2024-01-22 16:39:35 in reply to 5 [link] [source]
But 0 only makes sense for debugging
Exactly my point - that's your considered opinion as the developer of your particular applications, but it's not the case for all applications.
Shouldn't the default be a value that is appropriate for usage?
The default should be the lesser of all potential evils, and the least-evil default is arguably 0 because it makes no assumptions about the client-side requirements and treats all clients equally. A value of 1s or 2s or 10s would be fine for some clients and not others. Some would request a tweak to that default to fit their app, which would in turn trigger grief in other projects. Given this library's scope and wide applicability, zero is objectively the least-problematic and fairest default for the busy-timeout.
In any case, the opportunity to change that default slipped by some 20 years ago - it's long-since been set in stone due to this project's particularly strong backwards-compatibility constraints.
(8) By Nuno Cruces (ncruces) on 2024-01-23 09:28:44 in reply to 3 [link] [source]
Exactly, e.g. for one particular application I have, the good default would arguably be infinity, as cancelation is achieved through other methods (the progress callback and interrupts).
(9) By Rowan Worth (sqweek) on 2024-01-24 07:21:20 in reply to 4 [link] [source]
Also worth noting that in the past sqlite required special compilation flags on certain platforms to achieve reasonable resolution on sleep() calls, and if you forgot (or didn't know about) these flags the library would only sleep in one second intervals¹.
In such a landscape it makes extra sense to push the decision to the application developer as to whether sleeping is an appropriate response to a locked DB.