On 11 Jan 2016 9:06 PM, Rowan Worth wrote: > 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. >> > > * if it returns SQLITE_OK and at least one row, the schema has been created > and it can proceed
Agreed. > * if it returns SQLITE_OK and zero rows, the schema hasn't been created yet Sure; however, by the time you do the next action it?s possible that something else will be creating the schema. This is the condition that I?m trying to avoid. > * 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) Agreed. > > 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). I suppose we could have each connection: BEGIN EXCLUSIVE LOCK Check for schema: if OK, then ROLLBACK; else, create and COMMIT. Just seems a bit funny to do a LOCK at the begin of each and every connection, but I suppose not too bad since every write does an exclusive lock in the first place. > 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. Yeah, we had quite a few corruptions. I think only from the DBs that we create this way, but it was odd that none of our folks could reproduce the failure. > 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)? Same code, just different processes. We?ve just noted over the years with race conditions that that ?if it can fail, it will?, so we try to be as bulletproof as we can. -FG
