Closing database connections in SmartGWT

[jcovert]

I've recently been contacted by my company's database team, saying that my SmartGWT app is holding 200+ connections to one of our prod databases, effectively locking everybody else out. Looking through the SmartGWT Datasource samples, I see no mention of closing connections whatsoever, and I was under the impression that this was all taken care of under the hood. I bounced the server to free up the connections but am facing the same problem, less than a week later.

The application uses Datasource connections on both the client and server side (com.smartgwt.client.data.DataSource and com.isomorphic.datasource.DataSource), and they are instantiated with DataSource.get("widgetsDS") and DataSourceManager.getDataSource("linksDS"), respectively.

Is there something major (regarding connection pooling, closing connections, etc) that I'm missing?

Here is the relevent parts of the application configuration:

########################
# SmartGWT Server

webRoot:                               __AUTODETECT__

isomorphicPathRootRelative:            workbench/sc

sql.defaultDatabase:                   PostgreSQL
sql.PostgreSQL.driver.serverName:      dtord01qdb01d
sql.PostgreSQL.driver.portNumber:      5432
sql.PostgreSQL.driver.databaseName:    prod_db
sql.PostgreSQL.driver.user:            produser
sql.PostgreSQL.driver.password:        password

project.datasources:                   $webRoot/ds
RPCManager.enabledBuiltinMethods:      *

Thanks!

SmartGWT Power (4.0.d20130618)
SmartClient Version: v9.0d_2013-06-18/PowerEdition Deployment (built 2013-06-18)

[Isomorphic]

DataSource.get() is undocumented (don't call such methods, they are internal and not supported) and the docs for DataSourceManager.getDataSource() tell you that, if you call it, you have to return the DataSource to the pool.

Generally, writing writing DMIs, Custom DataSources or other similar logic, there would be no reason to call either of these APIs - calling RPCManager.getDataSource(), for example, automatically handles releasing the DataSource at the end of request processing.

[jcovert]

The SmartGWT EE Showcase sample "Java Data Integration" > "SQL" > "Editable Live Grid" uses the following code to get a Datasource to attach to a ListGrid. (There are several other samples doing the same, too...)

public class LiveGridFetchSample implements EntryPoint {  
  
    @Override  
    public void onModuleLoad() {  
        DataSource dataSource = DataSource.get("supplyItem");  
  
        ...
  
        final ListGrid listGrid = new ListGrid();  
        listGrid.setDataSource(dataSource);  
}

Is this not the correct way to go about accomplishing this?

Additionally, the RPCManager class you mentioned with the getDataSource() method is an isomorphic class and is limited to use on the server side. The smartgwt.client version of RPCManager doesn't seem to have a similar method.

So what is your recommended way of accessing data in the database from the client side? Or should I only make the gwt RPC calls and hit the tables from the server side? And if I'm doing it from the server side, RPCManager.getDataSource("dsName") should really be all I need, it sounds like?

Thanks again

[Isomorphic]

In your original post you mentioned DataSource.get() and DataSourceManager.getDataSource() in a single phrase:

DataSource.get("widgetsDS") and DataSourceManager.getDataSource("linksDS"), respectively.

So we assumed you meant the (undocumented) server-side API. The use of DataSource.get() you found in *client-side* code is correct and documented usage - DataSource.get() is documented as a client-side method and demonstrated in samples as you point out. Use it all you want. It cannot affect server-side pooling.

From the client side, it seems that I can use the DataSourceManager.get("dsName") to get access to a DataSource

This won't work since DataSourceManager.getDataSource() is not a client-side API.

Again your results with leaking connections suggest you are using the *server-side API* DataSourceManager.getDataSource() incorrectly - not returning DataSources to pools. Or, calling it when there is no need to, since RPCManager.getDataSource() can be called instead.

[jcovert]

Excellent - alright. So if I could recap and you can confirm once more, that would be great.

Client side - I can use com.smartgwt.client.data.DataSource.get("dsName") to get a ds and bind it to a ListGrid (or do a fetchData(Criteria), etc), and this connection will be all closed up when the user exits the app. This is just like the samples.

Server side - I should make an instance of a com.isomorphic.rpc.RPCManager, and call .getDataSource("dsName") on it. These connections will be all closed up when we're done with them too.

I should not need to do any freeing in either case, correct?

Thanks - I really I appreciate your speedy responses.

[Isomorphic]

Close..

Client-side, you can use DataSource.get(). But it's not that this is a correct usage, it's just that there's no way of retrieving DataSources client-side that could influence server-side connection pooling.

Server-side, in DMIs and similar logic, you need to use the instance of RPCManager *which is already available* (as a DMI parameter, or available on the DSRequest, depending on the context). You would not create one yourself.

If you are writing standalone code (not involved in processing an HttpRequest), then an RPCManager instance is not available and you cannot create one. Here, use DataSourceManager.get(), but you must free DataSources when you are done with them, as the docs explain.

[jcovert]

So on the client-side, if I do a Datasource.get() with a single fetchData() on the ds, when does the connection get freed up? Right after the callback? Or does it live until the user closes the app? Moreover, if I bind said ds to a ListGrid - does that connection stay open until the user closes the app? It seems to me that if I'm going to be doing the single fetchData(), I should make the rpc call to the server side of my app, open the DS there, fetch, close the DS, and return an object back over the wire to the front end. That way, the db connections will be cleaned up nicely.

And for the server side - I'm trying to do a handful of fetches inside the implementation of my GWT RPC Service, so I don't believe I have access to any of the things you mentioned. I will use DataSourceManager.get() and free them up when I'm done - this seems to be more consistent with the idea of db connection handling anyways.

[Isomorphic]

It seems to me that if I'm going to be doing the single fetchData(), I should make the rpc call to the server side of my app, open the DS there, fetch, close the DS, and return an object back over the wire to the front end. That way, the db connections will be cleaned up nicely.

This is accurate, except that connections are pooled for re-use on subsequent requests. But the pool has a (configurable) max size as noted in the docs, so it won't grow in an unbounded way and starve other DB users. This is all just typical best practices stuff found in all competently written web apps.

And for the server side - I'm trying to do a handful of fetches inside the implementation of my GWT RPC Service, so I don't believe I have access to any of the things you mentioned. I will use DataSourceManager.get() and free them up when I'm done - this seems to be more consistent with the idea of db connection handling anyways.

If you're forced to use GWT-RPC, yes this the right approach.

The FAQ makes a very compelling case that you should not use GWT-RPC. Add to the list in the FAQ: DataSource pool management becomes manual when it would have been automatic had you used the recommended approaches.

[jcovert]

Perfect - thank you very much.

And as for not using GWT-RPC, I'm afraid it might be a little late now. Remember my problem is not that I was designing an app and couldn't figure the DS stuff out - it's that my current application seems to be blowing up in prod.

That section of the FAQ is certainly interesting though, and I'll absolutely keep it in mind when considering an overhaul of this app or when I'm creating my next one.

Thanks again for everything, I really appreciate the help. You've cleared up a lot.

[jcovert]

Still not closing connections...

I made all of the changes as suggested, but we're still hemorrhaging db connections.

There are no more client side db calls being made (with com.smartgwt.client.data.DataSource), they have ALL been moved to the server side (with com.isomorphic.datasource.DataSource). And everytime I do a DataSourceManager.getDataSource("dsName"), I also do a DataSourceManager.freeDataSource(ds) immediately after the .fetch() returns.

Do you have any more ideas or suggestions?

The .fetch() returns a list of records - could it be that the db connections are staying open while this list is still in scope? I will make a local copy of the list and see if it will then let the DataSourceManager free the connection?

Thanks!

[Isomorphic]

We need some clarifications on what you're doing - runnable sample code is always the best thing, but the questions are:

1. what is the context of all this code? a DMI? standalone process launched completely separately from a servlet engine? Thread running in a servlet engine, but not initiated by a request?

2. for what reason are you calling DataSourceManager.getDataSource()? Are you aware that you can just do new DSRequest(..).execute() and never need to retrieve a DataSource as such?

3. what are you fetching from the database - does it involve binary data (CLOB/BLOB etc fields)?

[jcovert]

A coworker of mine finally figured it out. The code's all good, but our Tomcat had a bunch old isomorphic and old apache DBCP libs in its lib dir leftover from earlier deployments of whatever app was occupying this Tomcat previously. Removing these resolved it fully without any further code modification.

As far as DSRequest-executing vs DataSource-getting goes, are there benefits to using one over the other? It appears I don't need to do any sort of freeing of resources in the DSRequest version, but it might open a connection, execute, and close the connection right after? So if I wanted to do 5 fetches, I might want to go with the latter - getting the DataSource, using it a bunch, then freeing it after? For an occasional single fetch, the DSRequest sounds like the way to go.

Thanks!

[Isomorphic]

When you just do "new DSRequest(..).execute()", correct, there's nothing to free.

It will *borrow* a connection from the pool, use it, then *return* it to the pool (unless pooling is off, opening and closing a connection won't be involved).

This is indeed typically the way to go, so it's what we cover in the QuickStart Guide.

You might need to retrieve the DataSource instance server-side if you have logic that needs to do something like iterate down the list of defined fields in the DataSource. If you don't need it, there's no reason to fetch it.

If you do need it, and you're in the context of a DMI or similar situation where you have an RPCManager, then again you can use RPCManager.getDataSource() and not have to worry about freeing the DataSource.