3 ms·

Why do you need to manually retry at all? Doesn't it end up in a queue and gets executed as soon as it can? I thought this stuff was handled by the database aut

by Kiro 3y ago

Why do you need to manually retry at all? Doesn't it end up in a queue and gets executed as soon as it can? I thought this stuff was handled by the database automatically.

H8crilA 3y ago
Yeah I would imagine that too. Specifically that the later process would hang on acquiring the lock.
nirimda 3y ago
If it blocked you until it was finished, then you wouldn't be able to handle a timeout because you would be blocked waiting for it to finish, so you'd have no capacity to realise the task needed to timeout (unless you wrote multithreaded code to detect the timeout "out of band"). If it doesn't block you, then you won't know whether it's been committed or not and so now you have to write some kind of loop to continuously test whether the query you just executed has been committed, abandoned or is still in progress. In principle, you could have some kind of configuration that says "try to commit this for so long, but if you can't commit it by then just abort". But you haven't really solved the manual retry problem, just made it somewhat less likely to occur. As far as I know, given the costs of a request, most database servers tend to use the latter approach. But in SQLite requests are very much cheaper, and at least part of SQLite's work happens in your thread, so it goes for the former approach. But from an end programmer's perspective it's only a quantitative difference, not a qualitative one. Also, "the database" is a highly ambiguous term, but in SQLite it can really only refer to a file on disk. There's no database server, it's just a library that you call to manipulate the file. So who even is the database server? Maybe it's the code you're writing. Maybe it's you who should be handling this stuff. And that's SQLite's decision, and fair enough too. SQLite isn't trying to be a database server, it never offered to be a database server, and you shouldn't think it's a database server. You should think of it as a library for executing SQL queries against a file that holds a relational database. If you think that's tantamount to being a database server, then consider what you normally mean by "server". Is a library for manipulating zip files tantamount to an FTP server?
bawolff 3y ago
Sqlite has a gotcha where if your transaction is upgraded to a write transaction half way through it is impossible to safely restart automatically in the dvent of a conflict. Which is why sqlite provides a "begin immediate" keyword to work around it if you know you are making a write transaction.

by Kiro 3y ago

Why do you need to manually retry at all? Doesn't it end up in a queue and gets executed as soon as it can? I thought this stuff was handled by the database automatically.

H8crilA 3y ago

Yeah I would imagine that too. Specifically that the later process would hang on acquiring the lock.

nirimda 3y ago

If it blocked you until it was finished, then you wouldn't be able to handle a timeout because you would be blocked waiting for it to finish, so you'd have no capacity to realise the task needed to timeout (unless you wrote multithreaded code to detect the timeout "out of band"). If it doesn't block you, then you won't know whether it's been committed or not and so now you have to write some kind of loop to continuously test whether the query you just executed has been committed, abandoned or is still in progress. In principle, you could have some kind of configuration that says "try to commit this for so long, but if you can't commit it by then just abort". But you haven't really solved the manual retry problem, just made it somewhat less likely to occur. As far as I know, given the costs of a request, most database servers tend to use the latter approach. But in SQLite requests are very much cheaper, and at least part of SQLite's work happens in your thread, so it goes for the former approach. But from an end programmer's perspective it's only a quantitative difference, not a qualitative one. Also, "the database" is a highly ambiguous term, but in SQLite it can really only refer to a file on disk. There's no database server, it's just a library that you call to manipulate the file. So who even is the database server? Maybe it's the code you're writing. Maybe it's you who should be handling this stuff. And that's SQLite's decision, and fair enough too. SQLite isn't trying to be a database server, it never offered to be a database server, and you shouldn't think it's a database server. You should think of it as a library for executing SQL queries against a file that holds a relational database. If you think that's tantamount to being a database server, then consider what you normally mean by "server". Is a library for manipulating zip files tantamount to an FTP server?

bawolff 3y ago

Sqlite has a gotcha where if your transaction is upgraded to a write transaction half way through it is impossible to safely restart automatically in the dvent of a conflict. Which is why sqlite provides a "begin immediate" keyword to work around it if you know you are making a write transaction.