Database is locked. What is the reason? What is the possible solution?

[Pentium100]

@littleskunk

My short term solution to that problem is ZFS with a SSD write cache but that is a different topic for another thread

I am using that and I sometimes get the db lock error, though as far as I know only on the orders db. I guess moving the database to nvme would help.

@littleskunk

On my storage node I have seen the error while the used space calculation or garbage collection was running.

Some time ago I noticed that the garbage collection was doing the equivalent of cp old new && rm old instead of mv old new. I have not checked it since, but what was the reason and is it still doing that?

@littleskunk

The SQLite DB alone shouldn’t get locked.

As far as I know, SQLite locks the entire database, but something like MySQL (InnoDB) locks only the relevant rows (and can still allow selects of them). That would probably help somewhat, since the serial number table would mostly see inserts and selects (not updates) and no fields are autoincrement.

@littleskunk

We are currently researching sparse merkle trees. That sounds like a better long term solution.

Hopefully that works better than now.

Also, something like redis should work here - if I understand correctly, losing part of the serials database would not result in lost customer data - it would just make it possible for a malicious uplink to repeat its downloads from my node (probably once, since after that, the serial number would be back in the db).