many TempRootShare, one TempRootExcl freeze get new JDBC connections Tomcat

[Former Member]

Hi,

today i get a freeze on system, and when i run the command:

x_cons DBNAME sh ac 1

i get many TempRootShare and one TempRootExcl.

i found a explain that TempRootShare occurs when a task tries to set a shared lock on the root page of a temporary B* tree and has to wait because of a lock conflict, and that TempRootExcl occurs when a task tries to set an exclusive lock on the root page of a temporary B* tree and has to wait because of a lock conflict.

i tried to get what command does that, using command monitor, but cant find any, maybe the lock occurs before i activated the command monitor. Now i activated analyser to try find something, but anyone have a better idea to find the reason for this type of error?

note: with this behavior tomcat cant pool any new connection, because i get many blocked threads waiting for maybe some of one these tasks. the workaround was to use a script to KILL all user sessions.

MaxDB version is 7.6.03.07 running on Linux 64bit.

full x_cons output its at:

xcons20090804.txt

ps: i put in a link because code tag for that type of content isnt working on forum.

linux bash script that kill all user sessions

#!/bin/sh
x_cons DBNAME SH TASK US | grep ^T | cut -d' ' -f1 | cut -d'T' -f2 |  while read TASK_ID; do
    x_cons DBNAME KILL $TASK_ID;
done

best regards

Clóvis

[lbreddemann · Accepted solution]

Hi Clovis,

the most interesting questions now would be:

What is the single task doing that is still running? ( a x_cons <SID> debugtask 488 should have printed the stack back trace to the knldiag file)

Why do all the tasks want to access the same TEMP root? Usually a temporary table is used within a transaction let alone by several sessions...

What is this TEMP table about anyhow? Who made it?

(Check the ROOTS or FILES table for the root 59552 as it was in your x_cons output.

Be aware that the root will likely be a different one next time.)

regards,

Lars

-

---

Marketing: if you're not already booked for October 27-29 this year and you happen to stay in vienna and you keep asking yourself what to do ... then get your ticket for [SAP TechED 2009|http://www.sapteched.com/emea/] and make sure to attend my [MaxDB session|http://www.sapteched.com/emea/edu_sessions/session.htm?id=237]