OK, so I executed query: sqlite> SELECT * FROM ordercallback cb WHERE sessionuuid='42bda93e-a3a3-4fca-a5c8-dc3dcf2a663e' 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);
And it got 'stuck' after displaying a couple hundred records or so. And the stack trace is then: #0 0x00000034f020ec50 in __lseek_nocancel () from /lib64/libpthread.so.0 #1 0x000000000041a42c in seekAndRead () #2 0x000000000041a5f9 in unixRead () #3 0x0000000000410a86 in sqlite3OsRead () #4 0x0000000000422433 in readDbPage () #5 0x0000000000424e88 in sqlite3PagerAcquire () #6 0x000000000042c514 in btreeGetPage () #7 0x000000000042c60a in getAndInitPage () #8 0x000000000042fc25 in moveToChild () #9 0x0000000000430871 in sqlite3BtreeMovetoUnpacked () #10 0x000000000043d65f in sqlite3VdbeCursorMoveto () #11 0x0000000000444e7d in sqlite3VdbeExec () #12 0x0000000000440163 in sqlite3Step () #13 0x000000000044034d in sqlite3_step () #14 0x0000000000405406 in shell_exec () #15 0x000000000040c4aa in process_input () #16 0x000000000040d573 in main () On 05/27/2015 05:34 PM, gunnar wrote: > Tomorrow I'll also try to make a stack trace of sqlite_shell with > pstack at the moment its stuck. > > > > Gunnar Harms > > > HiQ Invest > Rembrandt Tower ? 9th floor > Amstelplein 1 > 1096 HA Amsterdam > > > On 05/27/2015 05:23 PM, gunnar wrote: >> Hello Simon, >> >> I'm not 100% sure that I understand what you mean. I remember I >> tested that the sub query is the one that gets 'stuck' but I can't >> remember how I found out (I'm already trying to remember how I found >> it out and let you know once I recall it) >> >> I also found a very bad workaround: >> >> The original query that gets stuck once the process that inserts into >> ordercallback table also runs (but doesn't get stuck when the other >> process doesn't run): >> >> -- get the count of open orders >> SELECT count(*) FROM ordercallback cb WHERE sessionuuid=(SELECT uuid >> FROM session WHERE date=(SELECT max(date) FROM session)) 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); >> >> >> The following always work: >> >> create table aap as select * from ordercallback where >> sessionuuid=(select uuid from session where date=(select max(date) >> from session)) and endstate=0 and working=1; >> create table boe as select server_order_id, max(cb_seq_num) as >> max_cb_seq_num from ordercallback where sessionuuid=(select uuid from >> session where date=(select max(date) from session)) and working=1 >> group by server_order_id; >> >> -- get the count of open orders >> >> select count(*) from aap, boe where >> aap.server_order_id=boe.server_order_id and >> aap.cb_seq_num=boe.max_cb_seq_num; >> >> >> But of course that is not what I want. I want it isolated, one query, >> as it should be. >> >> >> >> >> We have several servers and each has its own sqlite database with an >> ordercallback table in which they insert records. With only one of >> them we have this issue, but that is also the one that inserts much >> and much more records into its ordercallback table than the others. >> >> >> >> Regards, >> Gunnar >> >> >> >> On 05/27/2015 04:48 PM, Simon Slavin wrote: >>> On 27 May 2015, at 11:00am, gunnar <gharms at hiqinvest.nl> wrote: >>> >>>> 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); >>> As Dr Hipp has pointed out, SQLite locks the entire database during >>> the whole of your SELECT *, so any other process should not be >>> interrupting your query once it has got started. >>> >>> Purely for research and not as a permanent solution, have you tried >>> splitting your query up into two ? First do the subSELECT, returning >>> what I think should be a single value, then do the main SELECT. You >>> might be able to figure out which one is being interrupted. >>> >>> Simon. >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >
