Multithreaded Application

In a multithreaded application, multiple threads are spawned to perform concurrent tasks. Usually global transactions have thread affinity; i.e., they are identified and associated by the threads in which they run. All connections participating in the same global transaction must be drawn from the pool or a data source in the same thread. You can maintain affinity to a node pool by associating the thread with the node pool. The connection factory responsible for providing the connection should then inspect the thread and reuse the node pool referenced in the thread space. The connection factory is allowed to randomly pick a pool only if there is no node pool associated with the thread. You can implement this in Java using the ThreadLocal class. When the connection factory requests a connection, it inspects the thread space to check if a node pool is associated with the thread; if there is, it reuses it. Otherwise, it randomly picks one.

The above method creates an affinity between the thread and the node pool. If your application spawns a new thread for every new task, you don't need to manually clean up the thread space. However, for applications that have thread pools or reuse threads (like web servers, app servers, etc.), you need to manually clean up the thread space after each global transaction or task completion. This entirely depends on how you manage threads in your application. If you don't clean up the thread space, the node pool will get associated with the thread for the complete lifespan of the thread, which may result in suboptimal load balancing across RAC instances.

Web Application

Modern web applications have a distinct advantage over standalone applications. They run in a managed environment which delegates most of the dirty work to the underlying application/web server. Here you can leverage an approach similar to standalone applications--using a thread pool--but by using HTTP filters, you can eliminate the problem with reusable threads by resetting the state of the thread in the HTTP filters.

Pick a node pool and set it in the ThreadLocal object in the in-filter before the request is sent to the actual request handler.

After the request handler processes the request (in the out filter), reset the thread state.

All of the connections for a particular request are directed to a single RAC instance. Alternatively, you can also use the request object to store the reference of the node pool rather then the ThreadLocal object. The downside of this approach is that you need to pass the request object to all of the components that draw a connection to the database.

Known Issues

This approach assumes a specific pattern in the life span of global transactions that happens to work with most popular applications. Some places where this would not work include:

Global transactions spanning multiple threads.

Multithreaded application reusing threads: this is kind of tricky, though not very dangerous. In this case, the node pool (RAC node) gets tightly coupled to the thread for the complete lifespan of the thread. You can still work around this problem depending on your application. For example, in a web server, you can have the request filters reset the thread state after each request. You will have to figure out your application-specific logic to reset the thread state after the thread has processed each request.

What happens if an RAC node goes down while the corresponding node pool is being used by some threads? I would still prefer to let the complete global transaction roll back and report the operation as a failed operation.