5 ms·
The comment about concurrency was strange. They have 24 x 5 + 2 x 2 * 60 = 360 writes per minute, or about 6 writes per second. They're not seeing concurrency i
by yuliyp 3y ago
The comment about concurrency was strange. They have 24 x 5 + 2 x 2 * 60 = 360 writes per minute, or about 6 writes per second. They're not seeing concurrency issues because the amount of time that's spent doing writes is like 1%. Naturally retrying a couple milliseconds later should succeed almost all the time.
- joseferben 3y agoTrue, on their own those numbers are not that impressive. To give some more context: That load is average, there are bursts. I use ATOMIC_REQUESTS=True which ties the transaction to the Django POST request. Given that it's quite easy to run into N+1 queries using the ORM, I somehow expected to see "database is locked" issue more often.
- jerrygenser 3y agoWouldnt database is locked only happen on concurrent writes where one is locking a row and timeout exceeded? If there are n+1 reads I don't think it would lead to database is locked.
- joseferben 3y agoIf there is a write in a read transaction (with a n+1 read), the transaction becomes a write transaction.
- Kiro 3y agoWhy 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 agoYeah I would imagine that too. Specifically that the later process would hang on acquiring the lock.
- nirimda 3y agoIf 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 agoSqlite 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.