----- Original Message ----
> From: Richard Hipp <[email protected]>
> To: General Discussion of SQLite Database <[email protected]>
> Sent: Thu, February 21, 2013 11:39:44 AM
> Subject: Re: [sqlite] Ongoing SELECT sees INSERTed rows.
>
> On Thu, Feb 21, 2013 at 1:32 PM, Peter Aronson <[email protected]> wrote:
>
> > SQLite 3.7.15.2, Solaris and Windows (and presumably others). Journal
> > mode is
> > delete, but WAL appears to behave in the same manner.
> >
> > One of my coworkers ran into an infinite loop when accessing SQLite.
> > Essentially
> > an outer select loop was reading records, and an inner select loop was
> > inserting
> > records based on the records found into the same table. The loop never
> > ended as
> > the records inserted showed up in the select. This is not necessarily an
> > error,
> > of course. But if you take the script and the program below which
> > reproduces
> > the problem, add "order by c3" to the select statement, it doesn't happen
> > (presumably because we're reading the materialized temporary table, not
> > the base
> > table at that point). This sort of inconsistency can be an unpleasant
> > surprise.
> >
> > If you look at SQL 92, it has a concept of INSENSITIVE cursors, which
> > causes the
> > cursor to logically (at least) work on a copy of the data, so that changes
> > to
> > the underlying table are not visible. It would be nice if SQLite provided
> > a
> > method to make a insensitive select ("order by rowid" appears to get
> > silently
> > removed by the optimizer as you'd expect) less expensive than performing an
> > otherwise unnecessary order by.
> >
>
> (1) CREATE TEMP TABLE xyz AS SELECT...;
>
> (2) Query the xyz table while doing whatever updates are needed.
>
> (3) DROP TABLE xyz;
>
> The other thing you can do is open two database connections on the same
> database (in WAL mode) and query from one while updating on the other.
>
>
Thanks! The possibility we were wondering about was having an integer primary
key autoincrement column and just exiting the select loop when encountering a
id
greater than the max id at start time (I assume this approach would not be safe
if the integer primary key column was not autoincrementing).
> >
> > Peter
> >
> > create table base1 (c1 integer,c2 text,c3 integer);
> > insert into base1 (c1,c2,c3) values (1,'andrew',10);
> > insert into base1 (c1,c2,c3) values (1,'jeffrey',15);
> > insert into base1 (c1,c2,c3) values (1,'david',20);
> > insert into base1 (c1,c2,c3) values (1,'Jonathan',25);
> >
> > #include <stdio.h>
> > #include <stdlib.h>
> > #include <string.h>
> > #include "sqlite3.h"
> > int main (int argc,
> > char *argv[])
> > {
> > sqlite3 *hdbc;
> > int s3err,rid,c3;
> > sqlite3_stmt *hupdate,*hselect;
> > char sql[256];
> >
> > /* Open our test database (assumed to be named test.db). */
> >
> > s3err = sqlite3_open_v2 ("test.db",
> > &hdbc,
> > SQLITE_OPEN_READWRITE,
> > NULL);
> > if (SQLITE_OK != s3err) {
> > fprintf (stderr,"Unable to open test.db, err = %d\n",s3err);
> > return EXIT_FAILURE;
> > }
> >
> > /* Start a select on base1. */
> >
> > s3err = sqlite3_prepare_v2 (hdbc,
> > "select rowid,c3 "
> > "from base1 "
> > "where c3 < 15 and c1 = 1",
> > -1,
> > &hselect,
> > NULL);
> > if (SQLITE_OK != s3err) {
> > fprintf (stderr,"Unable to prepare select on base1, err = %d\n",s3err);
> > return EXIT_FAILURE;
> > }
> >
> > while ((s3err = sqlite3_step (hselect)) == SQLITE_ROW) {
> > rid = sqlite3_column_int (hselect,0);
> > c3 = sqlite3_column_int (hselect,1);
> > sprintf (sql,"insert into base1 (c1,c2,c3) values
> > (1,'inactive',%d)",c3);
> >
> > /* Insert into base1. */
> >
> > s3err = sqlite3_prepare_v2 (hdbc,
> > sql,
> > -1,
> > &hupdate,
> > NULL);
> > if (SQLITE_OK != s3err) {
> > fprintf (stderr,"Unable to prepare insert on base1, err =
> > %d\n",s3err);
> > return EXIT_FAILURE;
> > }
> >
> > switch (sqlite3_step (hupdate)) {
> > case SQLITE_DONE:
> > case SQLITE_ROW:
> > printf ("%d:",rid);
> > fflush (stdout);
> > sqlite3_finalize (hupdate);
> > break;
> > default:
> > fprintf (stderr,"Error on sqlite3_step for insert, err =
> > %d\n",s3err);
> > return EXIT_FAILURE;
> > }
> > }
> > sqlite3_finalize (hselect);
> > if (SQLITE_DONE != s3err) {
> > fprintf (stderr,"Error on sqlite3_step for select, err = %d\n",s3err);
> > return EXIT_FAILURE;
> > }
> >
> > (void) sqlite3_close (hdbc);
> >
> > return EXIT_SUCCESS;
> > }
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [email protected]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> [email protected]
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
