Comment by simonw
2 years ago
In my experience most SQLite writes take less than 1ms.
Do your writes really need to be concurrent if they run that fast?
Hard to get upset about waiting for the current write to complete before you get your turn when we are talking delays measured in thousandths of a second.
If you have more than 1000 writes per second then maybe this is something to worry about. The solution there is probably to run a slightly more powerful server!
back in the days where we hit this issue (mostly on windows systems) i used to create a little stress tool, you would be surprised how fast you reach the database-locked state.
ive just put it here: https://github.com/abbbi/sqlitestress
maybe its useful for some people to simulate their workloads.
And once its locked do you have to hold all operations for X ms?
Tested it repeatedly on Hetzner CPX11 (2 vCPU, 2GB RAM) instance, got one lock around row 49000, then it resumed until the end.
Sure the actual write might only take 1s but the transaction might lock for 10ms.
Does SQLite support overlapping write transactions with locks on different rows?
Also do you know if WAL2 mode changes anything? https://www.sqlite.org/cgi/src/timeline?r=wal2
*typo, meant 1ms not 1s