Multithreaded JDBC ORACLE apprlication problem

From: Bonny Rais <bonny.rais_at_siemens.com.au>
Date: 28 Oct 2002 15:43:51 -0800
Message-ID: <bd7f7b28.0210281543.384c349b_at_posting.google.com>


We have a problem with an application server using multiple database connection, each in its own thread. The problem is described below. I would appreciate any assistance here.

Setup:
Oracle 8.0.6/8.1.7 running on Linux or Windows 2000 JDK 1.4.0_01,
JDBC driver ojdbc14.jar from Oracle

Application setup:
an application server with multiple connection threads (typical configuration has 4 such threads) all waiting for requests to be dispatched.

Background info:
Threads start up OK, each opens a new connection to Oracle, which is confirmed using TOAD and looking at the currently open connections. Each connection is allocated its own connection id. Every connection uses the serializable transaction isolation level.

Every request issued to the application server, which uses one of these connections starts a transaction, performs whatever database operation then issues commit or rollback, depending on logic.

Symptoms:
Commit is not made visible to other database connections or the same database connection for subsequent database operation until a particular one of the connections happens to issue a commit. In particular, the following scenario describes the operations taken and the results:

1 conn1 issues an insert into a table and creates a new record. transaction committed
2 conn2 issues a select on the affected table but does not see the new record. conn2 commits
3 conn1 issues a select on the particular record but cannot see it. conn1 commits
4 conn4 issues a database operation on unrelated tables and commits. The new record is visible to all connections.

More over, commit count in oracle does not increase for each of the first three operations,only
on the fourth and increments by 1, not 4! Received on Tue Oct 29 2002 - 00:43:51 CET

Original text of this message