Consuming database connections

[Migrated From Jforum.net]

I'm using 2.1.7, but I doubt things are much different in 2.1.8.

What I noticed is that each view action keeps a db connection open even when it's not needed.
The context opens a db connection when first db request is made (to get/set some data) and remains open (not released) until the context is finished.

It's not a big deal with pageviews or post writes/edits.

But with large attachments, it consumers all available db connections and takes the whole application context down.

downloadAttach might take a long time to complete. Now imagine 50 users on dial-up connections downloading 10MB attachment.

That's 50 database connections used all at once.

Is there a way (some quick hack) to make it release the db connections right after the "am.isPhysicalDownloadMode" call within downloadAttach()?

I don't care that the download counter would be off when people abort downloads. I would rather just commit the transaction and after that pipe the data to the client.

Is it possible to do that without writing almost redundant methods for dao,context classes to be used with downloadAttach?

My box was down 3 times during the last month, all because of 50 or so people trying to download the same large attachment at the same time.


[originally posted on jforum.net by bcc]

[Migrated From Jforum.net]

Here is a hack for anyone in a similar situation.
I tried to change as little as I can.

The line numbers might not match with 2.1.7 vanilla because I made some other changes.
So if a patch run fails, then try editing manually or match the line numbers in the diff below to your real line numbers.

Index: src/net/jforum/dao/generic/GenericAttachmentDAO.java
===================================================================
--- src/net/jforum/dao/generic/GenericAttachmentDAO.java	(revision 3083)
+++ src/net/jforum/dao/generic/GenericAttachmentDAO.java	(working copy)
@@ -42,6 +42,7 @@
  */
 package net.jforum.dao.generic;
 
+import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.Timestamp;
@@ -405,7 +406,8 @@
 		String sql = SystemGlobals.getSql("AttachmentModel.selectExtension");
 		sql = sql.replaceAll("\\$field", paramName);
 		
-		PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(sql);
+		Connection conn=JForumExecutionContext.getConnectionForDownloadAttach();
+		PreparedStatement p = conn.prepareStatement(sql);
 		p.setObject(1, paramValue);
 		
 		AttachmentExtension e = new AttachmentExtension();
@@ -420,6 +422,7 @@
 		
 		rs.close();
 		p.close();
+		JForumExecutionContext.releaseConnectionForDownloadAttach(conn);
 		
 		return e;
 	}
@@ -519,13 +522,15 @@
 	 */
 	public void updateAttachment(Attachment a) throws Exception
 	{
-		PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(
+		Connection conn=JForumExecutionContext.getConnectionForDownloadAttach();
+		PreparedStatement p = conn.prepareStatement(
 				SystemGlobals.getSql("AttachmentModel.updateAttachment"));
 		p.setString(1, a.getInfo().getComment());
 		p.setInt(2, a.getInfo().getDownloadCount());
 		p.setInt(3, a.getId());
 		p.executeUpdate();
 		p.close();
+		JForumExecutionContext.releaseConnectionForDownloadAttach(conn);
 	}
 	
 	/**
@@ -576,7 +581,8 @@
 	{
 		Attachment e = null;
 		
-		PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(
+		Connection conn=JForumExecutionContext.getConnectionForDownloadAttach();
+		PreparedStatement p = conn.prepareStatement(
 				SystemGlobals.getSql("AttachmentModel.selectAttachmentById"));
 		p.setInt(1, attachId);
 		
@@ -587,6 +593,7 @@
 		
 		rs.close();
 		p.close();
+		JForumExecutionContext.releaseConnectionForDownloadAttach(conn);
 		
 		return e;
 	}
@@ -594,8 +601,9 @@
 	public boolean isPhysicalDownloadMode(int extensionGroupId) throws Exception
 	{
 		boolean result = true;
+		Connection conn=JForumExecutionContext.getConnectionForDownloadAttach();
 		
-		PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(
+		PreparedStatement p = conn.prepareStatement(
 				SystemGlobals.getSql("AttachmentModel.isPhysicalDownloadMode"));
 		
 		p.setInt(1, extensionGroupId);
@@ -608,6 +616,7 @@
 		
 		rs.close();
 		p.close();
+		JForumExecutionContext.releaseConnectionForDownloadAttach(conn);
 		
 		return result;
 	}
Index: src/net/jforum/JForumExecutionContext.java
===================================================================
--- src/net/jforum/JForumExecutionContext.java	(revision 3083)
+++ src/net/jforum/JForumExecutionContext.java	(working copy)
@@ -169,7 +169,17 @@
 	    
 		return c; 
 	}
+
+	public static Connection getConnectionForDownloadAttach()
+	{
+		return DBConnection.getImplementation().getConnection();
+	}
 	
+	public static void releaseConnectionForDownloadAttach(Connection conn)
+	{
+		DBConnection.getImplementation().releaseConnection(conn);
+	}
+	
 	/**
 	 * Sets a request to the execution context.
 	 * @param request The request to set

[originally posted on jforum.net by bcc]