> All is well, EXCEPT, I have ONE big, long transaction that collects and > stores a lot of data from some sensors. If this data is big enough, it > will eventually cause that connection to obtain an exclusive lock on the > DB. Now if the data collection code subsequently calls any subroutine that > instances up a quick and dirty DB object on the stack and tries to use it, > a "database is locked" (or "database busy" on write) error will be > returned.
> My app is single threaded. > I am have a couple of options: > 1. Pass the DB object containing the connection with the open data > collection transaction to the subroutines that need a DB. (Which is what I > have been doing.) This runs the additional operations within the outstanding transaction. This should never block since there is only ever one transaction in progress. > 2. Secretly keep a single, global connection open and let all the > instances of the wrapper object use it. Does the same as (1) but invisibly. > 3. Switch from rollback to WAL (or something) to keep the connections out > of each other's way. That seems kind of a big deal since I then have to > arrange the COMMITs. WAL will solve the problem for Readers -- that is the writer will never block readers nor will readers block a writer. It will still not permit multiple writers though. You should not have to change any transaction processing though for it to work. Of course, you can still only have a single write transaction in progress at any given time. > Generally, I've had a "get in, get out" philosophy, but I can see the > advantages of a single DB connection that exists for the life of the app. > (Still, that seems like it might be more fragile than actually closing the > DB file, dumping caches, and updating the directory.) Both are equivalent except for the overhead associated with opening and closing the database -- bad programming practices such as not finalizing statements etc aside. > So what do all you hot-shots think is best practice? In/out, long-lived or > something else? I prefer the long-lived approach. Continuously re-initialization of the connection on open, the need to re-read pages into the page cache repetitively, and the subsequent discard of a nicely loaded page-cache on connection close usually incurs non-trivial overhead. Provided that you are committing all transactions properly, closing the connection does not achieve any additional benefit -- it only increases cost. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
