On Wed, 2005-09-07 at 17:20 -0400, Mark Drago wrote: > 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.
> 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? Alright, I've solved this problem. It makes perfect sense in hindsight. Basically, the only part of this thing that will likely fail because the database is busy is the 'commit transaction' statement. However, I was never checking to see if that failed. Now, if the commit fails I run a 'rollback' (which shouldn't fail b/c the DB is busy) and the queue seems to work as expected. However, it seems that for every rollback that I do there is a file left in the directory with the databases. I have 30-something files named like the following: 'ame_log.db-mj0E2E1262'. ame_log.db is the filename of the main log database. The contents of the file are the full path to the main log database's journal and the full path to the attached queue database's journal. Should something be getting rid of these files? Has anyone else seen this? Mark.
signature.asc
Description: This is a digitally signed message part
