On Tue, 2005-09-06 at 16:07 -0400, D. Richard Hipp wrote: > On Tue, 2005-09-06 at 15:49 -0400, Mark Drago wrote: > > > 2. I could continue to write to the database in the single thread, but > > if the write fails, add the data to a queue and continue. Then, when > > another piece of data has to be logged, and it succeeds, empty the queue > > and write all of the data from it into the sqlite DB. > > This is what I would do. Except I would make the queue a > separate SQLite database which was connected to the primary > database using ATTACH. > > Suppose the "queue" database is named "pending.db". Then > when you open the primary database always immediately do > this: > > ATTACH DATABASE 'pending.db' AS pending; > > Then when you want to make a log entry do the following > statements: > > INSERT INTO pending.log VALUES(...); > INSERT INTO main.log SELECT * FROM pending.log; > DELETE FROM pending.log; > > When doing the above, abort after the first failure. > If the database is locked then the second statement will > fail, the DELETE will never occur and information will > accumulate in the "pending" database. If the second > statement succeeds, then the information is subsequently > deleted from the pending database. > > If you really want to make sure that the transfer from > pending to main is atomic, enclose the last two statements > in a transaction.
Thanks a bunch for this idea. I've implemented this and I'm getting an odd error. The scenario is thus: I insert the log into the attached database: INSERT INTO pending.log VALUES(...); I then begin a transaction: BEGIN TRANSACTION; If this does not return a SQLITE_OK, I just leave the log in the queue and hopefully the next log attempt will be able to work it out. Once in the transaction, I run the INSERT INTO log SELECT * FROM ...: INSERT INTO main.log SELECT * FROM pending.log; If this does not return a SQLITE_OK, I run 'ROLLBACK TRANSACTION;' and leave that log data in the pending db. Provided the move from the queue to the main log succeeds, I delete the entries from the pending log: DELETE FROM pending.log; If this does not return a SQLITE_OK, I run 'ROLLBACK TRANSACTION;' and leave that log data in the pending db. Provided the delete succeeded, I run 'COMMIT TRANSACTION;' and all should be well. The problem I'm seeing is that (I'm half guessing) after I run a rollback, when I try and run 'begin transaction' I get: 'cannot start a transaction within a transaction'. As far as I can tell there is no way to get out of this part of my code without running either a rollback or a commit. I've executed 'pragma synchronous=off' on this database as I'll be writing to it very often and the data isn't life-or-death important. I don't think this should impact anything, but I figured I would share. Any ideas? Mark.
signature.asc
Description: This is a digitally signed message part
