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.
It seems you missunderstood the purpose of a DEFERRED transaction. You should use DEFERRED transactions when you plan to READ only from the database, or you will eventually get BUSY errors during the transaction (which may or may not call the busy handler). >From what i could understand (and seems to work for me) you should use IMMEDIATE transactions when you plan to write to a database. When there is an IMMEDIATE transaction active, you can still read from the database from other processes (and database handles if im correct - sqlite3_open), but if you try to start another IMMEDIATE transaction (from other process) it will return BUSY error and will not start the transaction. While you have control over an IMMEDIATE transaction you can update the database at will and allow other processes to read from the database. When you commit from an IMMEDIATE transaction it will return BUSY untill all other active transactions are open (readers) and other readers that are trying to start a transaction will also return BUSY (the price to pay for concurrency). When BUSY is returned, it will call the busy callback if it was set. If you find this confusing or incorrect just let me know. Tiago
