Database FAQ

What is the class I get as a DataSource?

The DataSource you get is really a com.caucho.sql.DBPool item. You can look at the getActiveConnections() and getTotalConnections() to get the state of the pool.

What happens when a request is made and all the connections are in use?

If I have a dbpool set to 20 max connections, and say 60 people hit the jsp that makes connections to the database, what happens to the other 40 people that can't get an immediate open connection? How often does it try (or wait?) to get one?

There are a number of configuration variables which control that.

First, the pool will wait for a period of time to get a connection. That's specified by the connection-wait-time parameter. The default is pretty large, 10 minutes. In your example, the 40 requests would get connections as they become available.

If that times out, then the pool will look at the max-overflow-connections. If there are some overflow connections allowed, Resin will try to create a new connection to the database anyway. (The default is zero.)

If all that fails, the pool throws a SQLException.

How I protect the access to my database?

I need to put the database username and password in my configuration file, how can I make sure that only the Resin servers in my cluster can access the database? I don't want outside users to be able to access it.

1. (the easiest) - the machines in the cluster are behind a firewall, only other servers in the cluster can reach the database server. This is generally a good idea even without database considerations. You configure your firewall to let port 80 and 443 through to your frontend load balancing machine. You might also open up a ssh port to the frontend machine for maintenance access.
2. use the database features to secure the communication. MySQL, for example, can be configured to only accept connections from certain ip addresses, or only from certain username/ip address combinations. So you configure it to only allow connections from the machines in your cluster.
3. tunneling tricks, for example you configure your database server to not accept connections from anything but localhost, and then you use ssh to tunnel a port on each machine in the cluster to the port on the database server. This becomes a more attractive option if you wish to strongly secure all communication between machines in the cluster, secure even from local network access. You might, for example, use ssh to tunnel both your database access and your srun communication.

Will the pool be automocatically set to queue if number of connections requested is more than max connections?

You mean block the waiting thread. Yes. "queue" doesn't make sense in this context.

If you're using the PooledDataSource instead of the Driver SPI, you'll get the Oracle connection directly.

Otherwise, you need to case the Connection to com.caucho.sql.UserConnectionAdapter and call getConnection() to get the underlying Oracle connection.

What are the Driver/XADataSource/ConnectionPoolDataSource driver interfaces?

Driver is the old JDBC 1.0 interface. You should use XADataSource whenever it's available and you are using transactions. Replace the <driver-name> with that class name

It appears you can't just use an XADataSource everywhere. For "normal" JDBC use ConnectionPoolDataSource. When using JDBC with JTA (UserTransaction), use XADataSource.

Oracle Configuration for Resin 3.0

Oracle Configuration for Resin 3.0 - ConnectionPoolDataSource

<web-app xmlns="http://caucho.com/ns/resin">

<database name="jdbc/oracle"
          driver-class="oracle.jdbc.pool.OracleConnectionPoolDataSource">
  <driver-init url="jdbc:oracle:thin:@gryffindor:1521:ferg"
               user=""
               password=""/>
</database>

</web-app>

Oracle Configuration for Resin 3.0 - XADataSource

<web-app xmlns="http://caucho.com/ns/resin">

<database name="jdbc/oracle-xa"
          driver-class="oracle.jdbc.xa.client.OracleXADataSource">
  <driver-init url="jdbc:oracle:thin:@gryffindor:1521:ferg"
               user=""
               password=""/>
  <xa>true</xa>
</database>

</web-app>

JDBC 2.0 upgraded the Driver interface to XADataSource and ConnectionPoolDataSource. (The names are somewhat misleading. XADataSource and ConnectionPoolDataSource are driver interfaces provided by the database vendor, not application interfaces.)

The DataSource you get is really a com.caucho.sql.DBPool item. You can look at the getActiveConnections() and getTotalConnections() to get the state of the pool.

When is the connetion to the database made?

Does Resin connect to the database when lookup(Context.lookup()) is executed?

Normally, it will only connect when you do a getConnection().

What does Resin do if it is given a JDBC Driver that does not support XAResource in a definition of an XA-capable resource?

E.g., the JDBC/ODBC bridge in a Resin <resource-ref> with <res-type> javax.sql.XADataSource. First of all, how does the container detect if a java.sql.Driver is XA-capable? Does it at all?

java.sql.Drivers never support XAResource. XADataSource is the database vendor's interface for XA-capable resources.

Any time you're using the javax.sql.Driver interface directly, you're getting an XA adapter, not the JDBC vendor's XA interface.

You need to supply the JDBC vendor's XADataSource if you want to use it.

With Resin's DBPool you can specify that class in the "driver" parameter, but you can also configure the XADataSource separately with a <resource-ref> and then refer to it using "data-source" for Resin's pool.

Upon committing a UserTransaction spanning such a resource, I assume that the Driver underneath cannot take part in a 2-phase-commit?

So, it probably does not get a prepare call on prepare but still a regular non-XA commit call on commit? At least it seems to be this way, because an XA resource with a non-XA Driver beneath it gets committed by a UserTransaction commit.

Exactly.

Why do some vendors deliver ConnectionPoolDataSource implementations?

As far as I understand, most application servers including Resin and Tomcat/Tyrex use their own connection pool with the vendor's standard Driver/DataSource/XADataSource implementation. So what good is a ConnectionPoolDataSource provided by the JDBC driver vendor? Maybe for standalone use without a container?

ConnectionPoolDataSource is a helper class. It works with the application server (Resin) for pooling. ConnectionPoolDataSource doesn't pool directly, just like XADataSource doesn't handle transaction directly (it relies on the app server to register with the TM.)

The ConnectionPoolDataSource has a callback which tells when the underlying connection has died. That's nice since otherwise Resin needs to heuristically decide if the connection has died.

The JDBC DataSource API is a bit messy. I see the logic behind it, but it just seems overly complicated.

What is the interaction between container, driver, and application in a distributed transaction?

The container is responsible for all pooling, registration with the TM, and the transaction management itself.

The driver just provides some hooks to better support transactions and pooling. It acts as the RM (Resource Manager), but isn't doing any pooling itself.

Using getConnection(username,password)

It turns out that Jeff's problem was that he had not specified the user/password in the resource-ref in the resin.conf file. In looking through the DBPool code, it's clear that if the getConnection(username,password) method is used, then it needs to match the user/password specified in the resource-ref. If not, it will always create a new connection.

No, it's because the pool only has connections for a single user. It doesn't make sense for the pool to mix different connections for different users, because that would completely mess up any database permissions.

In theory, it would be possible to create subpools for each user, but that just doesn't seem to be all that valuable.

Can the ping-on-idle interval be changed?

Is there a way to configure the ping-on-idle interval? What's the default interval?

There's a ping-interval parameter. The default value is 60s.