While I'm not sure how long your long running select would take, it seems like SQLite (at least version 3) already works the way you are describing. As soon as you issue a select against a database file a shared lock is obtained. On commit that shared lock is released. While you have a shared commit on a database other connections may also obtain shared locks and can even obtain reserved locks by issuing write commands. As soon as your long running select finishes the write transaction(s) that automatically queue up would go through. The timeout that writes use is configurable in the connection call.
http://www.sqlite.org/lockingv3.html Daniel On Tue, Feb 3, 2009 at 7:12 AM, Brandon, Nicholas (UK) <nicholas.bran...@baesystems.com> wrote: > > > I would like some advice on how best to implement delays in writing to a > SQLite file (in my case using PHP) to maximise concurrent access. > > The web application I'm developing mainly issues quick reads and writes > on a local SQLite file using PHP 5. This works satisfactorily. However > in the future there may occasionally be a relative long running SELECT > statement while performing some reporting analysis. I would like to > reduce the potential contention with the long read blocking and > therefore timing out a write operation. I'm not concerned about the > small delay in the database being updated. > > I'm aware of one technique to create/use temporary tables using a select > statement but I would like something more robust since the complexity of > the long running SELECT statement is indeterminate since it is > modifiable by an authorised user. > > One idea I had was to use a shared flag across the PHP processes. A > potentially long running SELECT statement would set this flag to true. > All write operations would check for this flag and on its value being > true would open a new SQLite file and write the raw SQL strings to act > as queue. Something similar to the Undo example comes to mind > (http://www.sqlite.org/cvstrac/wiki?p=UndoRedo). On completion the long > running SELECT statement would open the new SQLite file and "play" the > SQL strings in order back into the original SQLite file. How would I > write binary safe INSERT/UPDATES SQL statement like that in PHP? > > I'm open to other techniques particularly if they would be simpler to > implement and manage! > > Nick > > ******************************************************************** > This email and any attachments are confidential to the intended > recipient and may also be privileged. If you are not the intended > recipient please delete it from your system and notify the sender. > You should not copy it or use it for any purpose nor disclose or > distribute its contents to any other person. > ******************************************************************** > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
