Good questions, I'll answer them below. Thanks! Q: Which SQLite version? A: We compile the amalgamation with gcc 4.4.7
SQLite version: #define SQLITE_VERSION "3.8.5" #define SQLITE_VERSION_NUMBER 3008005 #define SQLITE_SOURCE_ID "2014-06-04 14:06:34 b1ed4f2a34ba66c29b130f8d13e9092758019212" GCC version: [hiqbuild at jiraserver ~]$ gcc -v Using built-in specs. Target: x86_64-redhat-linux Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-bugurl=http://bugzilla.redhat.com/bugzilla --enable-bootstrap --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-gnu-unique-object --enable-languages=c,c++,objc,obj-c++,java,fortran,ada --enable-java-awt=gtk --disable-dssi --with-java-home=/usr/lib/jvm/java-1.5.0-gcj-1.5.0.0/jre --enable-libgcj-multifile --enable-java-maintainer-mode --with-ecj-jar=/usr/share/java/eclipse-ecj.jar --disable-libjava-multilib --with-ppl --with-cloog --with-tune=generic --with-arch_32=i686 --build=x86_64-redhat-linux Thread model: posix gcc version 4.4.7 20120313 (Red Hat 4.4.7-4) (GCC) Q: Which operating system? A: Linux, CentOS 6. Q: Where is the database file located? Local storage or network drive? A: Local storage. Q: WAL mode or journal mode? A: See code snippets below (with some extra info on how the db is configured) Q: What does "pragma integrity_check;" return? A: ok Q: Is there a busy handler involved (in either process)? A: My process (that inserts records) doesn't install a busy handler. I'm not sure about the sqlite command shell. Q: Is there an interconnection between the two processes (e.g. process 1 reads data and sends it off to process 2 that tries to modify stuff)? A: No. Q: How are you binding values to the SESSIONUUID variable using the sqlite shell alone? A: I use an actual value in the query eg. sessionuuid='fa307103-373d-4d86-b410-8a6486b252e1' Code snippets: bool OrderCallbackStorage::configureDatabase() { // Tips & tricks to make it faster are welcome! // to do: try with memory mapped I/O (PRAGMA mmap_size=N, with N something like 256MB) return execQuery("PRAGMA synchronous=OFF", NULL, NULL, "configureDatabase-synchronous") && execQuery("PRAGMA foreign_keys=ON", NULL, NULL, "configureDatabase-foreign_keys") && execQuery("PRAGMA cache_size=10000", NULL, NULL, "configureDatabase-cache_size") && execQuery("PRAGMA journal_mode=WAL", NULL, NULL, "configureDatabase-journal_mode"); } bool OrderCallbackStorage::execQuery( const char* query, int (*callback)(void*, int, char**, char**), OrderCallbackStorage* storage, const char* method) { char* errMsg; if (sqlite3_exec(Mdb, query, callback, (void*)storage, &errMsg) != SQLITE_OK) { fprintf(stderr, "OrderCallbackStorage::%s: SQL error: %s\n[failed query:%s]\n", method, errMsg, query); sqlite3_free(errMsg); return false; } fprintf(stdout, "[query:%s][changes:%d][total changes:%d]\n", query, sqlite3_changes(Mdb), sqlite3_total_changes(Mdb)); return true; } Gunnar Harms T +31 (0)20 53 53 487 F +31 (0)20 42 08 852 I www.hiqinvest.nl HiQ Invest Rembrandt Tower ? 9th floor Amstelplein 1 1096 HA Amsterdam On 05/27/2015 01:36 PM, Hick Gunter wrote: > Which SQLite version? > Which operating system? > Where is the database file located? Local storage or network drive? > WAL mode or journal mode? > What does "pragma integrity_check;" return? > Is there a busy handler involved (in either process)? > Is there an interconnection between the two processes (e.g. process 1 reads > data and sends it off to process 2 that tries to modify stuff)? > How are you binding values to the SESSIONUUID variable using the sqlite shell > alone? > > -----Urspr?ngliche Nachricht----- > Von: gunnar [mailto:gharms at hiqinvest.nl] > Gesendet: Mittwoch, 27. Mai 2015 12:01 > An: General Discussion of SQLite Database > Betreff: [sqlite] Query containing correlated subquery gets "stuck" > > Hi, > > Sometimes when I execute (from the sqlite_shell program) a select-query that > contains a correlated subquery that query gets 'stuck'. It outputs part of > the expected results and then doesn't come back to command prompt and also > doesn't output any more records. > > I suspect it is because another process is inserting records in the table > from which I try to select during the time that my query runs. At least my > query never gets stuck when that other process is not doing anything on the > table. > > What I would like to know is if my assumption is right, and most importantly > if it is really the case that the query will never get stuck if no other > process is manipulating the same table. > > The query is: > > SELECT * > FROM ordercallback cb > WHERE sessionuuid=@SESSIONUUID > AND endstate=0 > AND working=1 > AND cb_seq_num=( > SELECT max(cb_seq_num) > FROM ordercallback > WHERE server_order_id=cb.server_order_id > AND sessionuuid=cb.sessionuuid AND working=1); > > > > > Regards, > Gunnar > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___________________________________________ > Gunter Hick > Software Engineer > Scientific Games International GmbH > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: hick at scigames.at > > This communication (including any attachments) is intended for the use of the > intended recipient(s) only and may contain information that is confidential, > privileged or legally protected. Any unauthorized use or dissemination of > this communication is strictly prohibited. If you have received this > communication in error, please immediately notify the sender by return e-mail > message and delete all copies of the original communication. Thank you for > your cooperation. > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users