Mutliple calls to an ASP page which executes an Oracle Stored Procedure. Sessions blending causing errors.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bsc7080mqc@mylxhq.com - 31 May 2005 01:38 GMT

Okay here is an odd one that neither us or Oracle can currently explain. Need some help from the experts. I have an Oracle Stored
package for which I provide a common stored procedure for an external ASP web page to call upon and pass the appropriate parameters
to. This procedure in turn evals the parms and then executes additional procedures and Oracle provide APIs. An external XML
processing engine parses an XML transaction and identifies the appropriate parameters to pass to the stored procedure. This process
works quite well. Problem we are running in to is that when I have multiple XML transactions occuring at the same time, the local
variables begin to bleed over from one transaction to another. My assumption is that each transaction would be unto itself. This is
not holding true. When I execute via SQL*Plus I get a separate Oracle SESSION_ID assigned to each executing SQL*Plus call, even if
performed in separate copies of SQL*Plus. Each call the local variables start out empty, and I would never expect them to bleed over
to another parallel session. BY using a select from dual of the SYS_CONTEXT call to retrieve the SESSION_ID, I can verify and
repeatedly demonstrate that the SESSION_ID is being reused when called from the ASP process vs. the SQL*Plus sessions give me
separate ids.

Oracle concurs that this does not appear to be normal. We have disagreements internally that SESSION_ID should be unique and
separate. One individual says no that it can be the same, however when it is the same we see blending of data. WHen it is not the
same it works fine. So evidence lends me to say no its not supposed to be the same.

One expert on our side indicated that ASP is not a true multi-threaded environment but a Free Threaded environment and is intended
to share sessions to reduce resource issues and boost performance, which may be where our problem is. We are working on a proof of
concept using .NET, but surely this problem has been encountered by others as well as overcome.

In summary, we need a means to call an Oracle Stored Procedure multiple times including sequential and parallel executions, but
maintain separate transactions. There should never be an occassion where the local varibles of one transaction blend over to the
other.

Any one ???

Barry Chase
Mylxhq - My Oracle Portal
http://www.mylxhq.com

Reply to this Message

DA Morgan - 31 May 2005 02:08 GMT

> Okay here is an odd one that neither us or Oracle can currently explain. Need some help from the experts. I have an Oracle Stored
> package for which I provide a common stored procedure for an external ASP web page to call upon and pass the appropriate parameters
[quoted text clipped - 25 lines]
> Mylxhq - My Oracle Portal
> http://www.mylxhq.com

And your version number is?

Signature

Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Reply to this Message

bsc7080mqc@mylxhq.com - 31 May 2005 05:12 GMT

9.2.0.5 Database... not sure about the Microsoft side of things. We have tried both the Oracle ODBC driver and Microsoft ODBC
driver, both with the same disheartening result. Oracle support has replicated the problem and what appears to be happening is the
default functionality of Microsoft ... Microsoft has an MTS server that maintains a connection for 2minutes after a request has been
made. If another request occurs within that time, it reuses that connection. Problem appears that it reuses it as long as
transactions continue to occur until 2min lapses with no activity at which time it releases the session. We would like a way to turn
this so called connection pooling effect off. we disabled the connection pool feature on the ODBC setup for the Microsoft server,
but the problem persisted. It is why we suspect that if we can go to true multi-threaded environment, the problem will go away
(hopefully). I am only frustrated that I can't find out how others got around the problem in the past without use of C, Java, or
.NET. Just plain old ASP.

Barry

>> Okay here is an odd one that neither us or Oracle can currently explain. Need some help from the experts. I have an Oracle Stored
>> package for which I provide a common stored procedure for an external ASP web page to call upon and pass the appropriate parameters
[quoted text clipped - 27 lines]
>
>And your version number is?

Reply to this Message

Andy Hassall - 31 May 2005 13:22 GMT

>In summary, we need a means to call an Oracle Stored Procedure multiple times including sequential and parallel executions, but
>maintain separate transactions. There should never be an occassion where the local varibles of one transaction blend over to the
>other.

It seems to me the main point to clarify is what you term "local variables of
one transaction".

If you are setting package variables, and relying on them between separate
calls, then yes I'd expect this behaviour under certain circumstances, since
connection pooling will be running the statements through a shared set of
sessions. The scope of package variables is effectively "per session" not "per
transaction".

Is the process happening across multiple pages? If so then that would seem to
result in this behaviour if you're relying on package variables. If it's on the
same page, though, I'd expect you'd have exclusive access to the session for
the duration of the request. This is at least how it works in PHP with
persistent connections - I haven't worked with ASP for a while now, but if it
mixed statements in the same session then that would be a severe bug since
it'll mix up transactions - but it'd be such a huge bug it'd be surprising.

Signature

Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Reply to this Message

Chet Justice - 31 May 2005 21:44 GMT

A possible workaround would be to use the dbms_session.set_identifier
procedure for each page to maintain state.

http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_sessio.htm
#996936

On login generate a unique session id and return that to the
application. Pass that session id to each page and let each page
(re)set the client identifier in the database.

CREATE OR REPLACE
PACKAGE p_my_context
AS
PROCEDURE set_sessionid(p_sessionid IN VARCHAR2);
END p_my_context;
/
show errors

CREATE OR REPLACE
PACKAGE BODY p_my_context
AS
PROCEDURE set_sessionid(p_sessionid IN VARCHAR2)
IS
BEGIN
dbms_session.set_identifier(p_sessionid);
END set_sessionid;
END p_my_context;
/
show errors

Your call would be something like this (in java, don't know asp)

String mysessionid = (String) session.getAttribute("sessionid");
String SQL = "BEGIN " +
" p_my_context.set_sessionid(?); " +
" p_my_package.do_something(?, ?, ?); " +
"END;";

CallableStatement stmt = null;
Connection conn = null;
DataSource ds = (DataSource) ctx.lookup("jdbc/PFSFuserDS");

try
{
conn = ds.getConnection();
stmt = conn.prepareCall(SQL);
stmt.setString(1, mysessionid);
stmt.setString(2, firstParameter);
...
...
stmt.execute();
}
catch (SQLException ex)
{
out.println(ex);
}
finally
{
if (stmt != null) { stmt.close(); stmt = null; }
if (conn != null) { conn.close(); conn = null; }
}

Every page would make the " p_my_context.set_sessionid(?); " call to
reset the identifier.

There are probably better ways.

Hope this helps.

Chet

Reply to this Message

bsc7080mqc@mylxhq.com - 01 Jun 2005 00:19 GMT

Just want to say thank you for the feedback. We heard back from one of our feelers and it looks like a parameter setting on the ODBC
connection string appears to resolve the problem. We have some more testing though.

OLE DB Services=-4;

yes thats a negative 4 on there. It disables the connection pool programmatically for the given execution. This essentially appears
to enforce a separate session for each transaction requested. To provide some addtional clarity. We do not have a web page but a
secure posting url that a vendor posts there XML transaction to. This in turn parses and then executes a singular Oracle stored
procedure. So it is the same path way for all transations. The parms values are the only thing that effectively changes from one
transaction to another.

Additional note on this is the following :

The OLE DB .NET Data Provider automatically pools connections using OLE DB session pooling. Connection string arguments can be used
to enable or disable OLE DB services including pooling. For example, the following connection string will disable OLE DB session
pooling and automatic transaction enlistment.

Provider=SQLOLEDB; OLE DB Services=-4;
Data Source=localhost; Integrated Security=SSPI;

For more information about OLE DB session pooling or resource pooling, as well as disabling pooling by overriding OLE DB provider
service defaults, see the OLE DB Programmer's Reference in the MSDN library.

CAUTION: You must always close the Connection when you are finished using it. This can be done using either the Close or Dispose
methods of the Connection object. Connections that are not explicitly closed are not added or returned to the pool.

Reply to this Message