Tito Ciuro wrote:
I'm definitely not happy about this...
Let me get this right... it seems that you're cruising along fine with
SQLITE_OK's all over the place when suddenly one of your
threads/processes get a SQLITE_BUSY signal in the middle of a
transaction. In order to solve the crisis, one of the transactions
*must* be rolled back:
Questions:
1) Which one? Do I toss a coin?
Rollback the one that returned SQLITE_BUSY
2) At the time when SQLITE_BUSY pops up, the app may very well be too
deep in a transaction. Dr. Hipp suggests retrying the transaction that
was rolled back, a solution I believe should be handled by the engine.
Who is then responsible to keep track of the operations that make up a
currently openened transaction? The app I suppose? It will add an
amazing amount of ugly code testing/retesting/solving a SQLITE_BUSY signal.
The SQLITE_BUSY will be returned the first time you try to modify
the database. Any prior statements in the tranaction will have been
only queries. (Assuming you have a busy_handler registered that handles
conflicts in acquiring a PENDING lock.)
If you start each transaction with database change of some kind (perhaps
an UPDATE that doesn't really update anything) then it will immediately
attempt to acquire a RESERVED lock and either succeed or return
SQLITE_BUSY to let you know that you need to try the whole transaction
again later.
An example of an UPDATE that doesn't really change the database might
be something like this:
UPDATE table1 SET rowid=rowid WHERE rowid<0;
I'll look into adding a PRAGMA that makes BEGIN TRANSACTION acquire
a RESERVED lock immediately. That will reduce the amount of confusion
about this issue, I suppose.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
