the right value for JDBC connectiontimeout parameter on a gigabit ethernet?

[Former Member]

Hi,

for years i'm getting some stuck threads, with JDBC pool, that blocks all my system and the only way to recover is reboot the system or stops tomcat,

now i found a connectiontimeout parameter to use with JDBC connections, is right to set a value like 10 ms or less for a gigabit ethernet? there is a way to discover why those connections never comes? if a timeout occurs there is way to log in JDBC side, and in database side?

the app server and db server are connected using a cross over cable.

my db server and JDBC version is: 7.6.03.07 running on Linux 64bit with

here is a sample thread that causes my system to block many other threads, note that is where the connections to database are created.

"http-80-5" daemon prio=10 tid=0x0000000044b3e000 nid=0x3dbd runnable

java.lang.Thread.State: RUNNABLE

at java.net.SocketInputStream.socketRead0(Native Method)

at java.net.SocketInputStream.read(SocketInputStream.java:129)

at com.sap.dbtech.rte.comm.BasicSocketComm.receiveConnect(BasicSocketComm.java:707)

at com.sap.dbtech.rte.comm.BasicSocketComm.dbConnectExchange(BasicSocketComm.java:789)

at com.sap.dbtech.rte.comm.BasicSocketComm.connectDB(BasicSocketComm.java:233)

at com.sap.dbtech.rte.comm.SocketComm$1.open(SocketComm.java:38)

at com.sap.dbtech.jdbc.DriverSapDB.openConnection(DriverSapDB.java:966)

at com.sap.dbtech.jdbc.DriverSapDB.openByURL(DriverSapDB.java:891)

at com.sap.dbtech.jdbc.DriverSapDB.connect(DriverSapDB.java:208)

- locked <0x00002aaaba9903c8> (a com.sap.dbtech.jdbc.DriverSapDB)
at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)
- locked <0x00002aaaba990530> (a org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory)
at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:974)
at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
at org.exolab.castor.jdo.engine.DatabaseRegistry.createConnection(DatabaseRegistry.java:399)
at org.exolab.castor.jdo.engine.TransactionContextImpl.getConnection(TransactionContextImpl.java:203)
at org.exolab.castor.persist.TransactionContext.query(TransactionContext.java:644)
- locked <0x00002aaac72093f0> (a org.exolab.castor.jdo.engine.TransactionContextImpl)

at org.exolab.castor.jdo.engine.OQLQueryImpl.execute(OQLQueryImpl.java:458)

at org.exolab.castor.jdo.engine.OQLQueryImpl.execute(OQLQueryImpl.java:414)

at com.supridatta.bean.DataPersist.consulta(DataPersist.java:500)

best regards

Clóvis

[Former Member]

Clovis,<br>

Did you find a solution to this problem? I'm seeing similar thread hangs.<br>

<br>

Thread 'HTTP Worker [11]', process 'server0', index '13'
"XI SOAP[Retail1_SA_XTR_AAE/Retail1_Sender_XTR_AAE/]_19878" Id=23 is RUNNABLE (running in native)
  at java.net.SocketInputStream.socketRead0(Native Method)
  at java.net.SocketInputStream.read(SocketInputStream.java:129)
  at com.sap.dbtech.rte.comm.BasicSocketComm.receiveData(BasicSocketComm.java:725)
  at com.sap.dbtech.rte.comm.BasicSocketComm.receive(BasicSocketComm.java:814)
  at com.sap.dbtech.rte.comm.JdbcCommunication.execute(JdbcCommunication.java:41)
  at com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:540)
  at com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:465)
  at com.sap.dbtech.jdbc.CallableStatementSapDB.handleStreamsForPutval(CallableStatementSapDB.java:1632)
  at com.sap.dbtech.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:483)
  at com.sap.dbtech.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:315)
  at com.sap.dbtech.jdbc.CallableStatementSapDB.executeUpdate(CallableStatementSapDB.java:769)
  at com.sap.dbtech.jdbc.trace.PreparedStatement.executeUpdate(PreparedStatement.java:81)
  at com.sap.sql.jdbc.direct.DirectPreparedStatement.executeUpdate(DirectPreparedStatement.java:365)
  at com.sap.sql.jdbc.common.CommonPreparedStatement.executeUpdate(CommonPreparedStatement.java:339)
  at com.sap.engine.services.dbpool.wrappers.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:279)
  at com.sap.engine.messaging.impl.core.store.MessageStore.insertMessage(MessageStore.java:786)
...
...

[Former Member]

Hi, Jason,

i never reached to state what really happen with that, but what helps me to know what is going on the server when that stuck occurs is the follow sequence of shell scripts, if you use Linux, that is ready to work

checkDbActivity.sh

#!/bin/sh
x_cons YOUR_DB_NAME sh ac 1

taskRunningCommands.sh

#!/bin/bash
#set -x
export PATH=$PATH:/pex/opt/sdb/programs/bin
executa(){
  variaveis
  arqsCmdDB
  executaCMD
}
variaveis(){
  cmd="/home/sdb/scripts/tmp/taskRunningCommand.cmd"
  data=$(date +%y%m%d)
  dat=$(date +%m%y)
  hora=$(date +%H%M%S)
  dbName="YOUR_DB_NAME"
  dbmUser="dbm"
  dbmPwd="YOUR_DBM_PASSWORD"
}
arqsCmdDB(){
  echo "sql_execute select * from running_commands ">$cmd
  echo "sql_execute select * from sessions ">>$cmd
}
executaCMD(){
  dbmcli -d $dbName -u $dbmUser,$dbmPwd -i $cmd
}
executa

the command *select * from running_commands* just returns if you activate command monitor, to activate that i use on dbmcli the follow sequence of commands.

sql_execute diagnose monitor clear
sql_execute DIAGNOSE MONITOR ROWNO 3000
sql_execute diagnose monitor time 2000
sql_execute diagnose monitor data on

more details about this commands you can see in:

https://wiki.sdn.sap.com/wiki/display/MaxDB/Command+Monitor

with this, when you get stuck threads, you can check your database side, to see what task is waiting for, and what SQL command is executing at that time, i discovered, that sometimes, a bigger read transaction with isolation level 1, can block all other threads, and because of this, all my SQL that i use for reporting i append a WITH LOCK ISOLATION LEVEL 0 at end of SQL.

this solved many things here, one thing that i cant solve is when pool is getting new connections in paralel, this causes thread stuck because new connections tries to lock a "catalog" table, maybe something wrong inside JDBC drive that queries this table with isolation level 1, but without sources i cant check if i'm right or no.

to solve this, i configured my pool of connections to always have 5 ready connections, to avoid construction of connections too often.

i hope that this helps, and please share your steps, as this still a obscure subject for me.

best regards.

Clóvis

[Former Member]

unknown response