On 10/29/07, Richard Klein <[EMAIL PROTECTED]> wrote: > Perhaps the best solution is to follow these rules: > > IF THE TRANSACTION IS A WRITER (i.e. will eventually write to the db): > ---------------------------------------------------------------------- > (1) Begin the transaction with 'BEGIN IMMEDIATE'. If you get SQLITE_ > BUSY, it means that another writer is already accessing the db. Just > sleep awhile and retry, as many times as necessary. Once you get > SQLITE_OK, you're the only writer accessing the db. > > (2) If you get SQLITE_BUSY later in the transaction when you want to > write your cache to disk, it can only be due to one or more readers > holding SHARED locks (because you're the only writer). They will > eventually exit without trying to acquire any stronger locks (again, > because you're the only writer). Therefore, no deadlock is possible. > Just sleep awhile and retry, as many times as necessary. > > IF THE TRANSACTION IS A READER (i.e. will only read from the db): > ----------------------------------------------------------------- > (1) Begin the transaction with a simple 'BEGIN'. This starts the > transaction in the UNLOCKED state, so you *cannot* get a SQLITE_ > BUSY at this point. > > (2) If you get SQLITE_BUSY later in the transaction, it can only be > due to your trying to acquire a SHARED lock while some other process > holds a PENDING or EXCLUSIVE lock. That process will eventually > finish its writing and exit. Therefore, no deadlock is possible. > Just sleep awhile and retry, as many times as necessary. > > > If all transactions follow the above rules, then explicit rollbacks > should never be necessary.
For avoiding deadlocks, yes. It can also be abstracted so the rest of your code works on just "read-only" and "read/write" transactions, which is convenient. I would still perform rollbacks for any errors other than the above expected SQLITE_BUSY cases, of course, since they indicate something else went wrong (such as running out of disk space). I think it's safe to say those are all unusual cases though. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
