[java] Connection pooling ResultSet connections

[verytallman]

Ik heb tot nu toe 2 connection pooling libraries gebruikt; DBPool en c3p0.
De programma's die ik schrijf bevatten allemaal een klasse Database die alle queries uitvoert en de connection pooling uitvoert/beheert.

Nu ben ik met beide libraties op het volgende probleem tegengekomen; bij het opvragen van een ResultSet geeft de klasse Database het ResultSet terug. Helaas mag Database dan niet meteen de gemaakte verbinding verbreken, anders krijg ik een error bij het doorlopen van de resultset. Namelijk "Operation not allowed after ResultSet closed".

Nu heb ik zelf een wat omslachtige oplossing bedacht; de verbindingen die gemaakt worden om een ResultSet op te vragen stop ik in een array, waarna ik met een Timer de verbindingen in de array sluit als ze ouder zijn dan 10 seconden.

Opzich werkt dit goed, geen errors, tenzij de while(rs.next()) loop langer dan 10 seconden bezig is.

Echter heb ik toch het gevoel dat dit makkelijker moet kunnen. Suggesties?

[Herko_ter_Horst]

Ja, de verbinding pas sluiten/teruggeven aan de pool als je klaar bent met de ResultSet.

[Robtimus]

Met ^^.

Zo hoort de volgorde te zijn:
1) creeer een Connection
2) creeer een (Prepared)Statement
3) creeer een ResultSet
4) doe je ding
5) sluit de ResultSet
6) sluit de (Prepared)Statement
7) sluit de Connection

Bij niet-pooling connections doe je het zo, waarom dan met pooling niet?

[BalusC]

Inderdaad. Het is geheel transparant. Gewoon de connectie verkrijgen, je ding doen en de connectie sluiten. The usual way. De connectionpool implementatie zal dan achter de schermen wel oordelen of het de connectie daadwerkelijk sluit of terugzet in de pool.

[verytallman]

Ja dat volgorde klinkt idd logisch.
Echter door mijn implementatie van connection pooling lukt dat niet zo lekker.
Zal dus moeten kijken naar een andere implementatie.

[BalusC]

Welke implementatie gebruik je dan Dit klinkt meer als een denkfout in jouw kant. Het is niet de bedoeling om zélf een connection pool te bouwen rond een connection pool implementatie heen (een homegrown pool van connections afkomstig van een echte connectionpool oid ). In theorie hoef je alleen maar de URL van de getConnection() en/of de manier van getConnection() te vervangen wanneer je wilt switchen naar een connection pool. Het hele idee is dat je jouw JDBC code gewoon intact kunt laten

[verytallman]

Ik gebruik nu c3p0 volgens de methode van IceMan, alleen worden nu de connections alsnog niet vrijgegeven en blijven ze busy. Allereerst schematisch hoe ik nu werk:

code:

try {
   connection = pool.getConnection();
   statement = connection.createStatement();
   resultset = statement.executeQuery("een query");
   while (resultset.next()) {
      // doe iets
   }
} catch (SQLException e) {
   throw new SQLException(e);
} finally {
   resultset.close();
   statement.close();
   connection. close();
}

Mijn programma vraagt erg veel resultsets op in threads en er worden dus veel connections gemaakt in een korte tijd. Als het goed is worden die connections weer gesloten.

Echter, de connections worden niet goed gesloten. Na een tijdje krijg ik standaard de foutmelding:
"WARNING: com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1418a63 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30)."

Als ik nu de volgende waarden opvraag:
getNumConnections(): 340 (atijd rond de 340 bij crash)
getNumBusyConnections(): 340
getNumIdleConnections(): 0

De exceptions die gegooid worden zijn dus te verklaren, er staan teveel connections open en dat kan mysql niet aan. Ik dacht ook dat het max_connections van mysql 350 was, dus wanneer je bij 340 openstaande connections nog nieuwe gaat aanvragen krijg je een exception..

Nu is de vraag dus waarom dus connections niet gesloten worden.

Note; bij INSERT, UPDATE, DELETE queries gebruik ik dezelfde manier om connections te sluiten.

[Salandur]

probeer gebruikt te maken van al bestaande libraries die dit probleem voor je oplossen.

Als je het echt goed wilt doen:
in je finnaly kan ook weer een exception gegooit worden door de close methodes, dus deze moet je ook in een try{} catch{} blok zetten.

[verytallman]

Salandur schreef op maandag 01 december 2008 @ 17:18:
bestaande libraries

Welke libraties bedoel je?

Salandur schreef op maandag 01 december 2008 @ 17:18:
Als je het echt goed wilt doen:
in je finnaly kan ook weer een exception gegooit worden door de close methodes

Vergeten te zetten in me OP maar ik doe het wel in me code.

Heeft MYSQL misschien een setting waarbij connections na een close() toch een x aantal seconden worden opengehouden?

[momania]

verytallman schreef op maandag 01 december 2008 @ 17:34:
Heeft MYSQL misschien een setting waarbij connections na een close() toch een x aantal seconden worden opengehouden?

Daarmee probeer je een slechte work-around te vinden voor een probleem wat gewoon op te lossen is

Kun je eens code laten zien van hoe je pool opgebouwd wordt?

[verytallman]

momania schreef op maandag 01 december 2008 @ 17:39:
Kun je eens code laten zien van hoe je pool opgebouwd wordt?

code:

ComboPooledDataSource pool = new ComboPooledDataSource(); 
pool.setDriverClass("com.mysql.jdbc.Driver");
pool.setJdbcUrl("jdbc:mysql://localhost:3306/db"); 
pool.setUser("user"); 
pool.setPassword("pass");
pool.setMaxPoolSize(500); // geprobeerd tussen 20 en 500
pool.setMinPoolSize(30); // geprobeerd tussen 5 en 50
pool.setNumHelperThreads(40); // geprobeerd tussen 5 en 50
            
//pool.setMaxIdleTime(10); // verschillende settings al gedaan
//pool.setMaxAdministrativeTaskTime(3); // verschillende settings al gedaan
//pool.setMaxIdleTimeExcessConnections(1); // verschillende settings al gedaan

[momania]

Ziet er gewoon normaal uit. Hoeveel threads laat je tegelijk lopen? Heb je daar een threadpool voor?

Hou er rekening mee dat het natuurlijk onzin is om meer thread te hebben dan je connection pool aan kan.
Daarnaast lijkt mij sowieso 340 connecties overdreven. Een beetje applicatie moet je wel met max. 10 concurrent connecties afkunnen.

[verytallman]

Het probleem komt denk ik doordat ik het sluiten van de verbinding ergens anders doe.
In mijn OP had ik de situatie iets te versimpeld...
Mijn code:

code:

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class Programma {

  public Database db;

  public start () {
    ComboPooledDataSource pool = new ComboPooledDataSource(); 
    pool.setDriverClass("com.mysql.jdbc.Driver");
    pool.setJdbcUrl("jdbc:mysql://localhost:3306/mm"); 
    pool.setUser("user"); 
    pool.setPassword("pass");
    pool.setMaxPoolSize(100);
    pool.setMinPoolSize(10);

    db = new Database(pool);

    try {
      ResultSet rs = db.getRs();
      while (rs.next()) {
        // doe iets
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
        db.closeConn(); // hier in Database de verbinding sluiten
    }
}

public class Database {

  ComboPooledDataSource pool;
  Connection conn;

  public Database (ComboPooledDataSource p_pool) {
    pool = p_pool;
  }

  public ResultSet getRs (String p_query) {
    ResultSet rs = null;
    Statement st = null;
    try {
      conn = pool.getConnection();
      st = connection.createStatement();
      rs = st.executeQuery(p_query);
      return rs;
    } catch (SQLException e) {
        e.printStackTrace();
    }
  }

  }

  public void closeConn () {
    try {
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  }

}

Dat ik deze opzet gebruik is omdat het programma al geschreven was voordat ik connection pooling ging gebruiken. Om de opzet van het programma te veranderen is even geen optie.

Ik begrijp nog steeds niet waarom de verbinding niet goed gesloten wordt.

[Confusion]

Je sluit wel de connections, maar nergens de resultsets of de preparedstatements.

[ Voor 6% gewijzigd door Confusion op 01-12-2008 20:56 ]

[verytallman]

Confusion schreef op maandag 01 december 2008 @ 20:56:
Je sluit wel de connections, maar nergens de resultsets of de preparedstatements.

Ik heb net van ResultSet en Statement ook een klasse var gemaakt zoals Connection, en ResultSet en Statement ook gesloten in closeRs(), maar probleem blijft.

code:

public class Database {

  ComboPooledDataSource pool;
  Connection conn;
  ResultSet rs;
  Statement st;

 // etc...

  public void closeConn () {
    try {
      rs.close();
      st.close();
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

}

}

[momania]

verytallman schreef op maandag 01 december 2008 @ 20:35:
Het probleem komt denk ik doordat ik het sluiten van de verbinding ergens anders doe.
In mijn OP had ik de situatie iets te versimpeld...

Volgens mij heb je het nu nog steeds te versimpeld weergegeven hier?
Gebruik je de class Database als een singleton multitreaded toevallig? Want dat zou betekenen dat bij iedere query je de connection variable overschrijft en dus altijd maar 1 referentie overhoud die je kan sluiten.

Om de opzet van het programma te veranderen is even geen optie.

Waarom niet? Refactoring hoort, zeker als ik naar deze code kijk, juist altijd een optie te zijn en tijd voor te zijn. De opzet niet willen/kunnen veranderen bij verandering/toevoegen van funtionaliteit waneer het nodig/beter is, is de start van spagetti code

[verytallman]

momania schreef op maandag 01 december 2008 @ 22:22:
Volgens mij heb je het nu nog steeds te versimpeld weergegeven hier?

Nee zo ongeveer werkt het wel.

momania schreef op maandag 01 december 2008 @ 22:22:
Gebruik je de class Database als een singleton multitreaded toevallig?

Nee

Heb wel een deel van de code aangepast:
Bij elke getRs() maak ik een Connection aan in de methode en stop die in een array
Bij closeConn() zoek ik de verbinding in de array en sluit die.

Heb wel een deel van het probleem gevonden, dit gebeurt wanneer ik meerdere resultsets in 1 try block aanvraag:

code:

try {
  ResultSet rs;
  rs = getRs("query")
  rs = getRs("nog een query")
} catch (SQLException e) {
  e.printStackTrace();
} finally {
  closeConn();
}

Nu wordt er 2x een Connection gemaakt en maar 1 keer gesloten.

Ik hou nu een een file bij hoeveel Connection ik maak en sluit, en nu worden er wel meer Connections gesloten dan eerst, maar er staan nog steeds teveel Connections open.

[BalusC]

verytallman schreef op dinsdag 02 december 2008 @ 02:01:
Bij elke getRs() maak ik een Connection aan in de methode en stop die in een array

Dit stinkt. Ben je bekend met de SQL JOIN clause? Verdiep je je daar eens in. In dit subforum is daar een erg heldere FAQ over geschreven. Met JOIN's kun je diverse gerelateerde queries combineren tot één query zodat je netjes alles in één ResultSet hebt.

Punt is: de connectie, statement en resultset mogen absoluut niet "rondzwerven" in je applicatie. Verkrijg én sluit ze allemaal binnen éénzelfde try/catch/finally binnen één methode.

[ Voor 10% gewijzigd door BalusC op 02-12-2008 03:06 ]

[verytallman]

BalusC schreef op dinsdag 02 december 2008 @ 03:00:
[...]diverse gerelateerde queries combineren tot één query zodat je netjes alles in één ResultSet hebt.

Dit had niets met joins te maken, meer mijn luiheid om meerdere resultsets op te vragen binnen 1 try-catch block.

BalusC schreef op dinsdag 02 december 2008 @ 03:00:
[...]diverse gerelateerde queries combineren tot één query zodat je netjes alles in één ResultSet hebt.
Punt is: de connectie, statement en resultset mogen absoluut niet "rondzwerven" in je applicatie.

Dat is inderdaad netter. En iets om aan te werken als ik er tijd voor heb.

De reden dat ik nu een tijdelijke oplossing heb (connections opslaan in een array) is omdat ik nu even niet alle andere code wil aanpassen.

Dmv de oplossing in me vorige post sluiten alle connectie nu wel goed, heb geen errors meer.

[ Voor 8% gewijzigd door verytallman op 02-12-2008 04:55 ]

[Salandur]

verytallman schreef op dinsdag 02 december 2008 @ 02:01:
Heb wel een deel van de code aangepast:
Bij elke getRs() maak ik een Connection aan in de methode en stop die in een array
Bij closeConn() zoek ik de verbinding in de array en sluit die.

Heb wel een deel van het probleem gevonden, dit gebeurt wanneer ik meerdere resultsets in 1 try block aanvraag:

code:

try {
  ResultSet rs;
  rs = getRs("query")
  rs = getRs("nog een query")
} catch (SQLException e) {
  e.printStackTrace();
} finally {
  closeConn();
}

Nu wordt er 2x een Connection gemaakt en maar 1 keer gesloten.

Ik hou nu een een file bij hoeveel Connection ik maak en sluit, en nu worden er wel meer Connections gesloten dan eerst, maar er staan nog steeds teveel Connections open.

Je kan beter het volgende doen:

code:

try {
  con = getConnection()
  ResultSet rs;
  rs = getRs(con, "query")
  rs = getRs(con, "nog een query")
} catch (SQLException e) {
  e.printStackTrace();
} finally {
  closeConn(con);
}

Dan vraag je er 1 op en sluit je er 1 af.
Of je moet de getRS methode aanpassen dat deze de data in de ResultSet terug geeft ipv de ResultSet zodat getRS de connectie opvraagt en sluit.