On Tuesday, 22 January, 2013 19:14 MST, Ward Willats <sqlite-us...@wardco.com> wrote: > On Jan 22, 2013, at 5:54 PM, Keith Medcalf <kmedc...@dessus.com> wrote: >> 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. > Thanks for this thoughtful response (you too, Simon). The page-cache > argument is compelling. >> In my case, I only have one writer (I think!) during the big transaction, >> so a long-lived, singleton connection or WAL should work for me. I guess I >> would lean toward the former (KISS).
One other consideration is transaction visibility on a single connection. I presume that you are using some kind of input-driven or event driven application which may get a request to process a query "in the middle" of your update transaction. When you run the SELECT on the same connection that has the database transaction in progress, that SELECT will "see" the database as it exists at that moment in the transaction (ie, such SELECTs will read uncommitted data). If the transaction is subsequently rolled back, those results may have been inaccurate (ie, the same select processed after the rollback may see a different result). One of the advantages of WAL and using a separate reader and writer connection is that the reader connection will not see "uncommitted" data from the "writer". In fact, if you do transactions on the connection processing the SELECT (especially if some requests process multiple SELECTs to answer a single request) they can be made to see a consistent view of the database, even if the update commits during the SELECT processing (if that is even possible in your particular application flow). One way you might do this is to modify your wrappers slightly to take as input whether connection it wants is the "reader" or "writer". Don't change anything in the case of the writer, just return the long-lived writer connection -- your existing BEGIN/COMMIT/ROLLBACK should not need changing. For the reader though add a little bit of additional logic: keep a "usage" count -- increment the count when your open wrapper is returning the long-lived reader connection handle and decrement it whenever the reader connection handle is returned to your close wrapper function. When the count transition 0 -> 1 execute a "BEGIN" on the connection before returning the handle. When the count transitions 1 -> 0 execute a "ROLLBACK" or "COMMIT" on the connection. Of course, if the count goes <0 or never returns to 0, you have a problem ... (Note that if you do not do explicit BEGIN/COMMIT on the reader connection in WAL mode, your read-consistent view will depend on when the connection autocommits -- even read-only SELECTs are processed inside a transaction which begins and ends automatically). With the database in WAL mode, this will ensure that the "reader" sees THE SAME consistent view of the database even when an update is in progress. The committed transactions will only become visible when "all" concurrently in use readers have returned (closed) their connection (all the same reader connection) even if the update commits "in the middle" of a multi-step query -- in other words a guaranteed "all or nothing" with respect to the updates. Of course, whether any of this matters or is even possible depends on your application -- there may be no need for the additional complication at all. On the other hand though, it may be something to consider especially if the application flow may permit inconsistencies to be visible to the marketing folks (or whomever) expecting to see "all or nothing" ... and it may be better to think about such possibilities before they notice it and claim your application is "broken" ... or even worse is acting "mysteriously" in their eyes. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
