On 12 January 2016 at 03:00, Felipe Gasper <felipe at felipegasper.com> wrote:
> On 11 Jan 2016 1:45 PM, Scott Hess wrote: > >> >> As far as preventing the other process from using it before the schema >> exists, do "SELECT count(*) FROM sqlite_master", and if the result is 0, >> the schema does not exist. If you create the schema as a transaction, >> that >> will be atomic. >> > > But in order for that SELECT to avert TOCTTOU errors, we?d have to do > BEGIN EXCLUSIVE LOCK at the beginning of every single DB handle creation. > No, only the connection which is creating the schema needs BEGIN EXCLUSIVE. The other connections can determine the schema state based on a normal "SELECT count(*) FROM sqlite_master": * if it returns SQLITE_OK and at least one row, the schema has been created and it can proceed * if it returns SQLITE_OK and zero rows, the schema hasn't been created yet * if it returns SQLITE_BUSY, the schema is in the process of being created (or there's some other EXCLUSIVE transaction in progress, or a transaction is being committed at this very moment, or an in-progress write transaction has spilled sqlite's memory cache) As Scott also hinted at, hard linking DB files is dangerous because connections against each of the links will use different -journal files. In the event that your schema creation process (or the machine its running on) crashes halfway through COMMIT, connecting to the permanent database will cause corruption to be observed (because it's not aware of the other journal file and thus can't rollback the partial transaction). This may also be possible if another process simply connects to the permanent DB at just the wrong time (ie. halfway through the schema creation COMMIT)? Or maybe not, in my experience POSIX locks are maintained across hardlinks but I haven't checked if this is specified by the standard or file-system dependent. Do your different connections actually run different code? Or are you potentially in the situation where two threads are trying to create the same DB at the same time (each via their own .tmp file)? -Rowan
