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.
>
> 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
