Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (asf.osuosl.org: local policy)
Message-ID: <20051107191236.yoqo6fp3kcccwog0@207.142.133.157>
Date: Mon, 07 Nov 2005 19:12:36 -0500
From: ibatis@rpkb.com
To: ibatis help <user-java@ibatis.apache.org>
Subject: iBatis with JNDI and WebSphere and MS-SQL Server
MIME-Version: 1.0
Content-Type: text/plain;
	charset=ISO-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
User-Agent: Internet Messaging Program (IMP) H3 (4.0.3)

All,

I'm having a really odd problem that I would greatly appreciate help with. =
 My
company is trying to use JNDI with WebSphere targeting MS-SQL Server.  In
short, when the web session times out, a read to an authentication database
causes that database to be locked to such an extent that no other sessions,
users, or clients can even do a read on those database tables.  Due to the
trace log messages, IBM is convinced that 'my' code is not closing the data=
base
connections, and says that I should correct my code.  Well, I'm using iBati=
s,
and it's pretty clear to me after looking at the source that those connecti=
ons
should be closing.  IBM has suggested a work-around which will cause
uncommitted transactions to commit rather than rollback.  I have managed to
convince them, I think, that a rollback should not do what it is doing
regardless of my actions.  This may =91fix=91 my problem, but they remain a=
damant
that I am not properly closing my database connections and that I should
correct my code.

Below are some of the specific, but condensed, details about what I am tryi=
ng to
do.  As I have been working with IBM on a PMR for about 10 weeks, copious
details are also available upon request including a sample war file and tra=
ce
logs from WebSphere.

Background:
=95=09Target production server is WebSphere 5.1.1.4 on iSeries, but problem=
 was
found on WS5.1.1.4 for Windows
=95=09User authentication is through Windows domain
=95=09Group has been using iBatis for approximately 2 years now
=95=09MS-SQL Server 2000
=95=09Application is =91intranet=92 only

Application
=95=09First, Java web application forwards to a specifically created MS-IIS
application.  The Windows user name is then written to a =91GateKeeper=92 d=
atabase
using the jsession id as the key.  IIS application then forwards back to th=
e
java web application. (That way, no security information is passed directly
back to the java web application.)
=95=09Java web application reads the security information associated with t=
he user
based upon the jsession id.  Security string (xml) is stored in the session
data.
=95=09User may then perform queries against the =91pubs=92 database.  (MS-S=
QL standard
testing database, deployed by default with every MS-SQL server.)
=95=09When the web session times out, if a user attempts to perform another=
 query,
they are forwarded to a notification page.  The user may then click to be
re-authenticated, and it all starts over again.


Problem details:
=95=09If the web session times out, and the user clicks to follow the link =
to
re-authenticate, then the database tables are locked.  This is very
duplicatable; it happens every single time.
=95=09Locks are for the entire table, and prevent any query action whatsoev=
er
=95=09Problem has been on WebSphere 5.1.1.4 and WebSphere 5.1.1.6 on Window=
s.
=95=09Problem occurs with both MS JDBC drivers and WebSphere embedded drive=
rs
=95=09Problem only occurs using JNDI.
=95=09Using JDBC with iBatis without JNDI does not exhibit any problems.
=95=09Problem will only occur if read of pubs database occurs
=95=09=93Kill spid=94 command from SQL Server will terminate the connection=
 and free all
users.  Otherwise, only stopping the entire server will kill the connection=
.
=95=09In trace log, IBM notes the following:  WebAppTransac E WTRN0043I:
LocalTransaction rolled-back due to setRollbackOnly    IBM has a tech note =
at
http://www-1.ibm.com/support/docview.wss?uid=3Dswg21141640  which they cont=
end
will fix the problem.  Unfortunately, it will also cause an automatic commi=
t of
a transaction, if the developer was careless and an exception occurs.
=95=09No other exceptions or anomalous events occur

Questions
=95=09Under what circumstances will iBatis not =91close=92 database connect=
ions, i.e.,
return them to the pool?
=95=09Am I missing something that I need to do in iBatis that wasn=92t requ=
ired when
not using JNDI?
=95=09How do I set up logging for iBatis under WebSphere?  Can this be done=
 as part
of the trace file so that it is all packaged together?

Thanks in advance for anyone who has the patience and time to look at this.=
  I
realize that this is rather lengthy, but I also realize that it may not be
nearly enough information to determine what is actually going on.

Thanks!
Rich