On 4/28/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hi! SQLite 3.2.1 on win32. > > I'm having trouble wrapping my mind around where I have to be > paranoid about locking issues. In particular, the behavior when > readers and writers overlap, or two writers overlap, is not 100% > clear to me from an obsessive reading and rereading of the locking > documentation, google, the wiki and the mailing list archives. > So here is what I believe; if anything I say is inaccurate, I > would have undying appreciation for a correction.
Any number of simultaneous readers at the same time. Writing totally excludes all other operations. I.E.You may not read using a multi-row select and update while the select is still open. > 1. the 'reader' operation (happens asynchronously, often) > open db > prepare the following sql statement > select e.name, d.name from employee e, department d, emp_dep ed > where e.id = ed.emp_id > and d.id = ed.dep_id > and e.name = '?' > for each employee I'm interested in (1..many) > execute > scroll through results > close the query > end for > close sql prepare > close db looks fine. close query (sqlite_finalize()?) should be outside the loop. > > 2. the 'writer' operation (happens asynchronously, more rarely) > open db > prepare the following sql statements > A: insert into employee(name, phone) values (?, ?) > B: insert into department(name) values (?, ?) > C: insert into emp_dep(emp_id, dep_id) values (?, ?) > begin deferred transaction > for each employee I'm interested in (1..many) > execute A > save last autoincrement > execute B > save last autoincrement > execute C (using the two autoincrements) > end for > commit > close sql prepares > close db > > Here is the reader operation again, this time marked up. ALL CAPS > means that something else could have a lock preventing the operation > from successfully completing. > > OPEN DB -- a writer could be in the middle of writing > prepare the following sql statement -- only unsafe if schema changes > for each employee I'm interested in (1..many) > EXECUTE -- a writer could have jumped in > scroll (slowly) through the results -- uninterruptible > close the query > end for > close sql prepare > close db Your writer operation might cause BUSY to the reader operations (if they try to read while a write is in progress).. They should have a loop that will retry them if they get a busy return code. > > Here is the writer operation, marked up with ALL CAPS for ops > that could fail due to some other writer or reader. > > OPEN DB -- a writer could be operating > prepare the following sql statements -- only unsafe if schema changes > BEGIN DEFERRED TRANSACTION -- a writer or reader could cause BUSY > for each employee I'm interested in (1..many) > EXECUTE A -- the transaction could spill into EXCLUSIVE & BUSY > -- thus conflicting with an extant SHARED > SAVE LAST AUTOINCREMENT -- as above > EXECUTE B -- as above > SAVE LAST AUTOINCREMENT -- as above > EXECUTE C -- as above > end for > COMMIT -- writer or reader could cause busy > close sql prepares > close db > > In the comment for 'execute A' above, it seems to me that the pager could > conceivably try to force the transaction into EXCLUSIVE mode at any time > (dependent on memory settings etc. outside the control of the API). > So every call to execute a SQL statement could get the BUSY treatment, > and thus must be explicitly wrapped in retry handling code. You might get busy in the writer operation if another reader or writer are using the database. It should also wait on busy. > > This further suggests that a naive approach to executing sql -- putting > multiple sql statements in one string and passing it to exec -- is > unsafe at any speed; consider > > begin deferred transaction; > update...; > update...; > update...; > commit; I think that's correct. Exec() is also deprecated. I did this for my writer operations: begin immediate; if busy keep retrying for a reasonable period, if still no lock then error out. // lock is now established // the following should never fail because I have an exclusive lock // unless the disk fills or something catastrophic happens! do updates. commit I have very small and quick operations so delays will be minimal. This is very much cooperative multitasking. -- --- You a Gamer? If you're near Kansas City: Conquest 36 https://events.reddawn.net The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
