Announcement Announcement Module
Collapse
No announcement yet.
Threading question Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Threading question

    Hi.

    Our problem involves our connection pool. But, if my current understanding is correct, I don't think the issues are in the connection pool. Rather, I think it has to do with the way Spring or our app is interacting with the connection pool. Specifically, I think the problem involves threads somehow. So, I think it's a question for folks who have good understanding of the framework itself. Thanks in advance for reading long post.

    We're using OracleOCIConnectionPool & Oracle's proxy authentication. The pool creates a relatively small number of database connections & multiplexes many database sessions over those connections. (Although developers don't usually need to notice the distinction, the fact is that database connections & database sessions are 2 different things. A connection is a physical circuit; a session can be thought of as end user's security context.)

    OracleOCIConnectionPool returns OracleOCIConnection objects to user, which represent the end user's database session. Calling con.close() on those terminates user's database session, not the physical connection. So, I have a SmartDataSource with shouldClose() always returning false. When users log out, I close their database sessions.

    So, how in the world can physical connections be returned to pool so that other sessions can use them? Well, according to Oracle's docs,

    The Oracle JDBC OCI driver provides several transaction monitor capabilities, such as the fine-grained management of Oracle sessions and connections. It is possible for a high-end application server or transaction monitor to multiplex several sessions over fewer physical connections on a call-level basis
    ...
    Note that OCI connection pool is mainly beneficial only if the middle tier is multithreaded. Each thread could maintain a session to the database. The actual connections to the database are maintained by OracleOCIConnectionPool, and these connections, including the pool of dedicated database server processes, are shared among all the threads in the middle tier.
    So, OracleOCIConnectionPool manages the multiplexing transparently. No need to call con.close() to make connection available to other users/threads.

    I have a relatively simple multi-threaded test case demonstrating that it works as advertised. My code never does anything to put connections back; nevertheless 2 physical circuits shared by 5 threads/sessions. Basically, it works like this:

    Code:
    Main class configures an OracleOCIConnectionPool
    Then main class spawns 5 client threads
    Each client:
    * calls getProxyConnection(type, uprops) to begin database session
    * does n work loops
    * calls con.close() to end its database session.
    This works exactly as expected. Logs show 5 different threads doing their work more or less round robin:

    Code:
    2006-09-22 21:45:31,883 INFO [main][PoolTest:89] - pool config:
            user=proxy_user
            pwd=abc$123
            urljdbc:oracle:oci:@sjs
            type=oci
            connpool_max_limit=2
    2006-09-22 21:45:31,888 INFO [main][PoolTest:101] - begin run(5)
    2006-09-22 21:45:31,890 INFO [user1][PoolTest:164] - begin run(user1)
    2006-09-22 21:45:31,891 INFO [user5][PoolTest:164] - begin run(user5)
    2006-09-22 21:45:31,890 INFO [user2][PoolTest:164] - begin run(user2)
    2006-09-22 21:45:31,891 INFO [user4][PoolTest:164] - begin run(user4)
    2006-09-22 21:45:31,891 INFO [user3][PoolTest:164] - begin run(user3)
    2006-09-22 21:45:31,977 INFO [user1][PoolTest:174] - user1 received connection=oracle.jdbc.oci.OracleOCIConnection@6779e6
    2006-09-22 21:45:31,983 INFO [user2][PoolTest:174] - user2 received connection=oracle.jdbc.oci.OracleOCIConnection@14ae2c1
    2006-09-22 21:45:31,990 INFO [user5][PoolTest:174] - user5 received connection=oracle.jdbc.oci.OracleOCIConnection@1556d12
    2006-09-22 21:45:31,997 INFO [user3][PoolTest:174] - user3 received connection=oracle.jdbc.oci.OracleOCIConnection@fced4
    2006-09-22 21:45:32,003 INFO [user4][PoolTest:174] - user4 received connection=oracle.jdbc.oci.OracleOCIConnection@15718f2
    2006-09-22 21:46:12,432 INFO [user2][PoolTest:194] - user2: proxies=2; audsid=224581    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:46:18,042 INFO [user5][PoolTest:194] - user5: proxies=2; audsid=224582    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:46:27,444 INFO [user4][PoolTest:194] - user4: proxies=2; audsid=224584    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:46:38,683 INFO [user3][PoolTest:194] - user3: proxies=2; audsid=224583    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:46:40,765 INFO [user1][PoolTest:194] - user1: proxies=2; audsid=224578    row=39646       auth type=PROXY server=PSEUDO
    2006-09-22 21:46:54,056 INFO [user5][PoolTest:194] - user5: proxies=2; audsid=224582    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:46:56,258 INFO [user2][PoolTest:194] - user2: proxies=2; audsid=224581    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:47:14,999 INFO [user4][PoolTest:194] - user4: proxies=2; audsid=224584    row=19818       auth type=PROXY server=DEDICATED
    ...
    2006-09-22 21:50:02,644 INFO [user4][PoolTest:194] - user4: proxies=2; audsid=224584    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:50:07,294 INFO [user3][PoolTest:194] - user3: proxies=2; audsid=224583    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:50:20,258 INFO [user5][PoolTest:194] - user5: proxies=2; audsid=224582    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:50:22,962 INFO [user2][PoolTest:194] - user2: proxies=2; audsid=224581    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:50:41,540 INFO [user1][PoolTest:194] - user1: proxies=2; audsid=224578    row=39646       auth type=PROXY server=PSEUDO
    2006-09-22 21:50:42,937 INFO [user4][PoolTest:194] - user4: proxies=2; audsid=224584    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:51:00,541 INFO [user5][PoolTest:194] - user5: proxies=2; audsid=224582    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:51:00,629 INFO [user3][PoolTest:194] - user3: proxies=2; audsid=224583    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:51:19,737 INFO [user2][PoolTest:194] - user2: proxies=2; audsid=224581    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:51:25,060 INFO [user4][PoolTest:194] - user4: proxies=2; audsid=224584    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:51:35,706 INFO [user5][PoolTest:194] - user5: proxies=2; audsid=224582    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:51:39,862 INFO [user3][PoolTest:194] - user3: proxies=2; audsid=224583    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:51:56,929 INFO [user1][PoolTest:194] - user1: proxies=2; audsid=224578    row=39646       auth type=PROXY server=PSEUDO
    2006-09-22 21:51:57,703 INFO [user2][PoolTest:194] - user2: proxies=2; audsid=224581    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:52:15,055 INFO [user4][PoolTest:194] - user4: proxies=2; audsid=224584    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:52:15,721 INFO [user5][PoolTest:194] - user5: proxies=2; audsid=224582    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:52:15,721 INFO [user5][PoolTest:206] - close user5's connection=oracle.jdbc.oci.OracleOCIConnection@1556d12
    2006-09-22 21:52:15,725 INFO [user5][PoolTest:213] - finally run(user5)
    2006-09-22 21:52:17,428 INFO [user3][PoolTest:194] - user3: proxies=2; audsid=224583    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:52:34,138 INFO [user2][PoolTest:194] - user2: proxies=2; audsid=224581    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:52:34,139 INFO [user2][PoolTest:206] - close user2's connection=oracle.jdbc.oci.OracleOCIConnection@14ae2c1
    2006-09-22 21:52:34,144 INFO [user2][PoolTest:213] - finally run(user2)
    2006-09-22 21:52:35,577 INFO [user4][PoolTest:194] - user4: proxies=2; audsid=224584    row=19818       auth type=PROXY server=DEDICATED
    2006-09-22 21:52:35,577 INFO [user4][PoolTest:206] - close user4's connection=oracle.jdbc.oci.OracleOCIConnection@15718f2
    2006-09-22 21:52:35,579 INFO [user4][PoolTest:213] - finally run(user4)
    2006-09-22 21:52:36,068 INFO [user3][PoolTest:194] - user3: proxies=2; audsid=224583    row=19818       auth type=PROXY server=DEDICATED
    ...
    2006-09-22 21:53:18,900 INFO [user1][PoolTest:194] - user1: proxies=2; audsid=224578    row=39646       auth type=PROXY server=PSEUDO
    2006-09-22 21:53:32,877 INFO [user1][PoolTest:194] - user1: proxies=2; audsid=224578    row=39646       auth type=PROXY server=PSEUDO
    2006-09-22 21:53:32,877 INFO [user1][PoolTest:206] - close user1's connection=oracle.jdbc.oci.OracleOCIConnection@6779e6
    2006-09-22 21:53:32,878 INFO [user1][PoolTest:213] - finally run(user1)
    2006-09-22 21:53:32,879 INFO [main][PoolTest:129] - finally run(5)
    However, in our spring app, if MAX_CONS=2, then as soon as 2 end users start doing work with connections, the pool never gets them back. Note, many users can log in and begin their database sessions. But as soon as 2 of them start executing SQL, everybody else has to wait.

    So, my admittedly vague hypothesis is that somehow our spring app doesn't look like a multi-threaded client to the connection pool. Does this make any sense to anybody? Any thoughts or suggestions welcome.

    BTW, we're using Tomcat 5.0.28 & Oracle 9.2.0.1.

    TIA. ASD
    Last edited by adrury; Sep 22nd, 2006, 10:39 PM. Reason: Add some env info.

  • #2
    Threading question

    I know it's poor form to reply to myself, but an important clarification has occurred to me. Specifically, an important difference between the successful test case & the problematic app.

    * In the test case, clients/threads/sessions are exactly one-to-one. Each client has its own thread & its own session. OracleOCIConnectionPool handles that properly.
    * In the app, it's more like a thread pool. I have given each end user a database session; so users & db sessions are one-to-one. But, users & client threads are not one-to-one; users' requests are serviced, I guess, by any thread from Tomcat's thread pool (whatever that might be like).

    So, I guess the question, still vague, is: what can I do to make each end user be, or look like, a thread to OracleOCIConnectionPool?

    TIA for suggestions. ASD

    Comment


    • #3
      Threading question [SOLVED]

      Just to wrap up this pleasant conversation amongst myself, we were able to narrow the problem down until it was clear that the pool only fails to share connections properly when connections are used to read LONG data. Using CLOBs seems to allow OracleOCIConnectionPool to work as advertised.

      Comment

      Working...
      X