Database lock held during diskio upon client PGN submission

[AndyGrant]

Seems to be a large issue for people with weak setups +- sqlite3 instead of mysql or otherwise.

Gave Matt issues, as well as Gedas, IIRC. Offer both local solutions that resolved the lock, but left a tiny chance for stray PGN files on the disk, in Media as .bz2, but without a row in the PGN table.

Proposed the following general solution:

The error case here is that the file gets saved to disk, but the PGN entry never gets created into the database.
The PGN watcher thus never collects the stray file, and never puts it in the archive.
This will be increasingly unlikely to happen now that the database lock is minimally held in this context.

However, someone must be playing cleanup. And there is some trickery as to how one would accomplish that.
Due to the nature of sync/async stuff.

Suppose you click the download button. It passes all of the various checks to download.
Test is finished; all (known) PGNs are processed; No workers on the test for X minutes;

You do a final check to list out the files in the Media dir, and see if the file names match your test.
You find that some of them do. And thus we have stray PGNs.
You loop all those, and create new PGN entries into the database.
This code exits and gives you an API error, saying that stray files are still being processed.

Now its possible that many people tried to download at once. And so we get duplicates into the PGN table.
So the PGNWatcher must be aware of this, and if there is no file to match the entry, consider it done.

[AndyGrant]

This understanding is wrong; the lock is Row-level only for the newly created PGN.