jcc type 4 driver locking issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

interz12@yahoo.com - 09 Feb 2007 17:00 GMT

Hi,

I am seeing different hang/deadlock behavior with 2 different JDBC
connection methods.

METHOD A
-------------------
I am using the com.ibm.db2.jcc.DB2SimpleDataSource from the db2jcc jar
files shipped with version 8.1.11. I am using this datasource to
connect to a 8.1.2.104 FP11 db2 remote database server and connect
with the following code:

DB2SimpleDataSource db2ds = (DB2SimpleDataSource)dsource;
db2ds.setDatabaseName(dbName);
db2ds.setPortNumber(serverPort);
db2ds.setServerName(dbHost);
db2ds.setUser(userName);
db2ds.setPassword(password);
Connection = db2ds.getConnection();

With this connection I have 2 non-commited inserts for table A. With a
different connection I attempt a select from table A, and the select
hangs, then this exception is thrown (deadlock?)

com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -911,
SQLSTATE: 40001, SQLERRMC: 68
    at com.ibm.db2.jcc.a.jf.b(jf.java:3198)
    at com.ibm.db2.jcc.b.kb.i(kb.java:220)
    at com.ibm.db2.jcc.b.kb.a(kb.java:180)
    at com.ibm.db2.jcc.b.kb.a(kb.java:35)
    at com.ibm.db2.jcc.b.x.a(x.java:30)
    at com.ibm.db2.jcc.b.j.Fb(j.java:331)
    at com.ibm.db2.jcc.a.jf.R(jf.java:3041)
    at com.ibm.db2.jcc.b.d.f(d.java:1041)
    at com.ibm.db2.jcc.a.s.a(s.java:189)
    at com.ibm.db2.jcc.a.jf.c(jf.java:311)
    at com.ibm.db2.jcc.a.jf.next(jf.java:283)

METHOD B
---------------------
This time I connect to the SAME database, with the same application
and run through the exact same code sequence that hangs via method A
above, HOWEVER no deadlock occurs. Here I use the
COM.ibm.db2.jdbc.app.DB2Driver driver which connects via the locally
installed DB2 admin client installed on the application server.

What is the difference here with the drivers that would be causing
this??

Via this mehod

Reply to this Message

Mark A - 09 Feb 2007 17:12 GMT

On Feb 9, 10:00 am, inter...@yahoo.com wrote:

> Hi,
>
[quoted text clipped - 46 lines]
>
> Via this mehod

The RC 68 is a locktimeout, not a deadlock. The amount of time that a
statement waits for a lock to be released before getting a -911 RC 68
depends on the LOCKTIMEOUT database parameter. A deadlock would be RC
2.

I suspect that the second connection type has auto-commit turned on by
default.

Reply to this Message

interz12@yahoo.com - 09 Feb 2007 17:28 GMT

Hi,

auto commit is turned OFF on both connection methods I described

> On Feb 9, 10:00 am, inter...@yahoo.com wrote:
>
[quoted text clipped - 56 lines]
> I suspect that the second connection type has auto-commit turned on by
> default.

Reply to this Message

joe.weinstein@gmail.com - 09 Feb 2007 20:07 GMT

Show the JDBC code, including (I suspect) the parameter-setting
and the variable values. Sometimes a driver has data-encoding
defaults that will require a type-conversion by the DBMS, which
stops it from using an index, and must instead do a table search,
which takes longed and might require locks that an index query
would not.
As an experiment, try sending the calls as a complete SQL
string, including the variable values, instead of via setXXX().

Joe Weinstein at BEA Systems

Reply to this Message