Re: Dead Lock problem with 8.1.3

Kai Hessing wrote:
> Deadlock means it hangs up and doesn't terminate through timeout.
> The system goes into an endless loop. The part ('SELECT sid FROM
> stud_vera WHERE veraid = 2') seems to create a temporary table again and
> again and again ....

It is possible for a query to run for many days, and still finish. This
classifies as slow, not hung. The difference is important in
troubleshooting to determine the cause.

>>Also EXPLAIN output, and information regarding the number of rows in
>>each table, and the number of rows matching veraid = 2 and veraid = 34
>>might help.
>
> Explain produces the same problem. It just takes forever...

Did you try EXPLAIN, or EXPLAIN ANALYZE? The former only does the
planning, the latter actually materializes the result in order to get
actual timings, and will therefore take at least as long as the query
itself.

>>While the query is running, how much CPU is the process consuming, and
>>what does vmstat show for disk and swap I/O?
>
> ~80% CPU power. Disk usage is not noticable.

Can you attach with a debugger and see exactly what's going on? If not
we'd need that self contained test case to reproduce the problem.

Joe