jcc type 4 driver locking issue

[interz12]

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

[Mark A]

On Feb 9, 10:00 am, [e-mail] wrote:

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

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.

[interz12]

Hi,

auto commit is turned OFF on both connection methods I described

On Feb 9, 10:12 am, "Mark A" <[e-mail]:

On Feb 9, 10:00 am, [e-mail] wrote:

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

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.

[joe.weinstein]

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