"Failed to connect: The database file is locked database is locked" during "Deleting unwanted files …" never terminates

[ts678]

@gpatel-fr

That’s a very baffling long time.

Although it was a milder too-many-blocks situation, I think we’ve fought fileset deletion code before.

Reducing Time Spent Deleting Blocks whose parent topic also had us increasing the SQLite cache.

The current investigation has done less at the SQL level, but some testing there would seem useful.
There was a suggestion for manual test for part of the slow query, but profiling log may also show it.

@danblakemore

Aug 30, 2023 7:15 AM: Starting - ExecuteNonQuery: INSERT INTO "DeletedBlock" ("Hash", "Size", "VolumeID") SELECT "Hash", "Size", "VolumeID" FROM "Block" WHERE "ID" NOT IN (SELECT DISTINCT "BlockID" AS "BlockID" FROM "BlocksetEntry" UNION SELECT DISTINCT "ID" FROM "Block", "BlocklistHash" WHERE "Block"."Hash" = "BlocklistHash"."Hash")

presumably finished eventually, but we don’t know how long, or which parts of that were slow spots.

@tkohhh

It’s worth noting here that the existing code is running the same expensive query twice… once to insert the deleted blocks into the DeletedBlock table, and then a second time for the actual delete operation.

@ts678

//We save the block info for the remote files, before we delete it

          cmd.ExecuteNonQuery(@"INSERT INTO ""DeletedBlock"" (""Hash"", ""Size"", ""VolumeID"") SELECT ""Hash"", ""Size"", ""VolumeID"" FROM ""Block"" WHERE ""ID"" NOT IN (SELECT DISTINCT ""BlockID"" AS ""BlockID"" FROM ""BlocksetEntry"" UNION SELECT DISTINCT ""ID"" FROM ""Block"", ""BlocklistHash"" WHERE ""Block"".""Hash"" = ""BlocklistHash"".""Hash"") ");

          cmd.ExecuteNonQuery(@"DELETE FROM ""Block"" WHERE ""ID"" NOT IN (SELECT DISTINCT ""BlockID"" FROM ""BlocksetEntry"" UNION SELECT DISTINCT ""ID"" FROM ""Block"", ""BlocklistHash"" WHERE ""Block"".""Hash"" = ""BlocklistHash"".""Hash"") ");