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?
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.
Richard Boulton staterd earlier:
The change in 3.0.4 means that when using a busy handler (e.g.
sqlite3_busy_timeout) the threads trying to get RESERVED locks will not
retry, but instead will return immediately with SQLITE_BUSY. If these
transactions are rolled back the thread with the PENDING lock is free to
proceed when the busy handler retries the lock.
I would assume that every thread/process has its own journal, right? Or that there is a way of knowing which set of operations has a thread/process performed. If this is so, a thread/process could receive SQLITE_BUSY signals while an EXCLUSIVE lock is in place, then have SQLite re-execute the series of statements collected in the journal, that is, an auto-retry. If an error occurs, then the app can attempt to solve the issue. Is this something that can be done, or are there other impediments?
Best regards,
-- Tito
On Aug 12, 2004, at 00:48, Dave Hayden wrote:
On Aug 11, 2004, at 6:49 AM, D. Richard Hipp wrote:
Oops. The db1 should do a ROLLBACK, not a COMMIT. Or db2 can do an END TRANSACTION (since it never made any changes) and allow db1 to complete instead. The point is that when two threads or processes are trying to write at the same time, one of the two must back off, abandon their transaction (using ROLLBACK) and let the other proceed.
Wow. That adds a whole lot of complexity to my code. Every transaction would be inside a loop that checks for a busy return from any statement within. And most of the places I'm using a transaction, I'm doing a few hundred inserts or updates from a number of different functions.
This really is something I'd expect to run under the hood. Since only one of the competing threads will have completed a write (right?), can't the others "postpone" their transactions somehow until they can get a write lock?
For now, I've solved the problem by adding my own locks to exclude simultaneous transactions on the same database file. I'm only using transactions for writes (is there any reason for a read-only transaction?) so if there's no way to resolve two opened write transactions, you shouldn't be able to open two in the first place.
Please let me know if there's something I'm missing here..
Thanks, -Dave
