Closed GoogleCodeExporter closed 8 years ago
GoogleCodeExporter
commented
8 years ago Hi,
What version of H2 do you use? A deadlock problem was solved in version 1.3.158
(2011-07-17) (see the change log).
Could you post the complete thread dump? The stack trace you posted doesn't
show anything special (anyway at least 2 threads are required for a deadlock).
Regards,
ThomasOriginal comment by thomas.t...@gmail.com on 22 Sep 2011 at 4:33
GoogleCodeExporter
commented
8 years ago I'm using 1.3.159. I **was** suspecting some sort of custom non-reentrant
locking which could deadlock a single thread. But after having some patience I
watched the thread for 30 minutes and the query did finish (however the H2 web
console never got the results, probably timed-out). I sampled the thread while
it was running and have attached the stack traces.
So now my question turns to why does the the aggregate query take so long when
running the two individual queries take milliseconds.
// Sub query 1: get search hits from child table
select R.* from PARENT R where R.CHILD_ID in (select M.ID from
FT_SEARCH_DATA('999', 0, 0) S, CHILD M where S.TABLE='CHILD' and M.ID=S.KEYS[0])
(164 rows, 384 ms)
// Sub query 2: get search hits from parent table
select R.* from PARENT R where R.ID in (select T.ID from FT_SEARCH_DATA('999',
0, 0) S, PARENT T where S.TABLE='PARENT' and T.ID=S.KEYS[0])
(203 rows, 449 ms)
// Aggregate query: get search hits from child table AND parent table
select R.* from PARENT R where R.CHILD_ID in (select M.ID from
FT_SEARCH_DATA('999', 0, 0) S, CHILD M where S.TABLE='CHILD' and
M.ID=S.KEYS[0]) or R.ID in (select T.ID from FT_SEARCH_DATA('999', 0, 0) S,
PARENT T where S.TABLE='PARENT' and T.ID=S.KEYS[0])
(~30mins)
Thanks for looking into this,
Andrew
Original comment by kroh.and...@gmail.com on 22 Sep 2011 at 6:02
Attachments:
GoogleCodeExporter
commented
8 years ago Hi,
Is it possible to use SELECT ... UNION SELECT ... instead of OR? H2 doesn't
currently convert OR to a UNION, and probably scans all rows in this case, plus
for each row re-runs the inner query.
Regards,
ThomasOriginal comment by thomas.t...@gmail.com on 22 Sep 2011 at 5:49
GoogleCodeExporter
commented
8 years ago Thanks, Thomas. Using UNION SELECT in the query is the solution to my problem.
My SQL naivety is showing.
select R.* from PARENT R where R.CHILD_ID in (select M.ID from
FT_SEARCH_DATA('999', 0, 0) S, CHILD M where S.TABLE='CHILD' and
M.ID=S.KEYS[0]) UNION SELECT R.* from PARENT R where R.ID in (select T.ID from
FT_SEARCH_DATA('999', 0, 0) S, PARENT T where S.TABLE='PARENT' and
T.ID=S.KEYS[0])
(327 rows, 941 ms)Original comment by kroh.and...@gmail.com on 22 Sep 2011 at 6:38
GoogleCodeExporter
commented
8 years ago > My SQL naivety is showing.
The problem isn't your knowledge of SQL, the problem is that H2 is still quite
dumb in this area.Original comment by thomas.t...@gmail.com on 22 Sep 2011 at 7:43
GoogleCodeExporter
commented
8 years ago So it wasn't actually a deadlock, but just slow.Original comment by thomas.t...@gmail.com on 12 Oct 2011 at 5:23
GoogleCodeExporter
commented
8 years ago There is already a feature request for "Optimize A=? OR B=? to UNION if the
cost is lower.", I will increase the priority.Original comment by thomas.t...@gmail.com on 12 Oct 2011 at 5:24
Original issue reported on code.google.com by
kroh.and...@gmail.comon 16 Sep 2011 at 6:05