3 ms·

Yeah, I agree. It seems like the implementation simplicity comes at the cost of making it unreliable in the sense that depending on your schema design / data pa

Yeah, I agree. It seems like the implementation simplicity comes at the cost of making it unreliable in the sense that depending on your schema design / data patterns, you could end up with much worse performance instead of much better performance. Also, if you do achieve better performance by really spreading out what pages you hit, you're decreasing your SSD lifespan through write amplification. [1] On the bright side, the interface is simple enough, and maybe they could just swap out the implementation underneath for something more sophisticated later. [1] Or on HDD, you really end up with lots of seeks for reading as well as writing. But that's probably unfair to consider: if you're using HDD, probably wanting more concurrency isn't your biggest opportunity to improve.

paulddraper 3y ago
How would you get worse performance? Currently, the lock granularity is the entire database. This makes it smaller. The reason this isn't transactions by default is correctness concerns for programs.
- scottlamb 3y ago
  > How would you get worse performance? It's optimistic concurrency. If there's a conflict, the client gets SQLITE_BUSY_SNAPSHOT and has to roll back and try again. There's no guarantee the second try will succeed either. They might need to back off (as in, sleep between attempts) and/or entirely stop using "BEGIN CONCURRENT" after n tries. edit: originally also wrote above: In fact, without some way of ensuring transactions write to pages in a consistent order, I don't think there's any guarantee any transaction will make progress. It could degenerate to livelock. ...but I think I was wrong about this part. I think SQLITE_BUSY_SNAPSHOT means another transaction actually committed touching these pages; so progress was made in the system as a whole.
  - paulddraper 3y ago
    > It's optimistic concurrency. Ah.

paulddraper 3y ago

How would you get worse performance? Currently, the lock granularity is the entire database. This makes it smaller. The reason this isn't transactions by default is correctness concerns for programs.

scottlamb 3y ago
> How would you get worse performance? It's optimistic concurrency. If there's a conflict, the client gets SQLITE_BUSY_SNAPSHOT and has to roll back and try again. There's no guarantee the second try will succeed either. They might need to back off (as in, sleep between attempts) and/or entirely stop using "BEGIN CONCURRENT" after n tries. edit: originally also wrote above: In fact, without some way of ensuring transactions write to pages in a consistent order, I don't think there's any guarantee any transaction will make progress. It could degenerate to livelock. ...but I think I was wrong about this part. I think SQLITE_BUSY_SNAPSHOT means another transaction actually committed touching these pages; so progress was made in the system as a whole.
- paulddraper 3y ago
  > It's optimistic concurrency. Ah.