So, if I understand correct, you create a file and then start to add a schema etc. Before the schema is committed (or before the exclusive transaction is started) you assume that other threads/processes /might/ be opening the file and trying to write some data to it (because reading would be harmless), and you further assume, because you have now found corruptions, that this might be the root of such corruptions?
I don't see a reason to believe it definitely is, but you could, easily eliminate the options. If an SQLite DB file gets opened (for reading) by another sqlite engine, the result is harmless and should end in errors (Zero possibility of corruption). If such a file gets opened for writing to a schema that does not exist, again, only errors should be thrown, no corruption should be happening. If such a file gets opened, and found to NOT have a schema, then try to create the schema (all within the period between opening the file and adding a schema by the first connection), then the first connection should find the file to be locked, or at least, transaction-locked, and throw the error. I cannot believe there is a plausible and possible way of encountering the described race condition, much less it causing corruption. This doesn't mean it isn't possible (just that I find it hard to believe), but it's very easy to prove programmatically. Have you instrumented the code? Have you checked EVERY return code from every SQLite call? Are you certain the file system isn't of the networking variety? Can you construct a small program that even causes the race condition and causes wrong data to end up in the DB without throwing an error of any sort? (Never-mind corruption, even just proving the wrong data can end up there is enough to make me believe it's possible). This is the first time in forever that anyone has described such a race condition to be, not only possible, but also file-damaging on this forum. If this is possible it should be of great concern to us all, so we want to know the detail very much. The best way to avoid it, is to create the file as temp or just a different name, then rename it after the schema is added - but this advice is superfluous in my opinion - I don't think the race condition is what is going on - which means something else might be wrong that you are unaware of yet, so best is to make sure first that the hypothesis is true. Cheers, Ryan
