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 >