MySQL connection pooling with C3P0

[Dustin Withers]

Hello all,

I'm new to Scala and new to Scalatra, heck I'm kinda new to Java web
development other than a bunch of Oracle work in another life. Anyhow,
I'm having some trouble with MySQL and C3P0 connection pooling . The
code can be seen here:
https://gist.github.com/944244

Originally I wasn't using any kind of connection pooling and I
eventually exhausted my connections. Now instead of exhausting
connections the app seems to run out of C3P0 connections and then
locks up with no logging in the console and no CPU usage but the
thread count goes up each time I request a page.

I've played with all of C3P0's settings and I can stave off the
failure by increasing MaxPoolSize but eventually after a enough page
requests the app hangs.

Any thoughts? Should I bother the people on the Squeryl mailing list?

Thanks much for your time,
-dustin

[Wille Faler]

You're probably leaking sessions and connections, as you are not closing them or unbinding the session after a request:

session.close
session.unbindFromCurrentThread

[Dustin Withers]

Should something like the following work?

class DailyLifFilter extends ScalatraFilter with ScalateSupport {
var dlSession:Session = null

before {
dlSession = SessionFactory.newSession
dlSession.bindToCurrentThread
}

after {
dlSession.close
dlSession.unbindFromCurrentThread
}
....

Thanks,
-dustin

On Apr 27, 9:01 am, Wille Faler <wille.fa...@gmail.com> wrote:
> You're probably leaking sessions and connections, as you are not closing
> them or unbinding the session after a request:
>
> session.close
> session.unbindFromCurrentThread
>

[Wille Faler]

Hmm, making it an instance variable of your filter is not thread-safe, so you should probably bind your session to a DynamicVariable/ThreadLocal (DynamicVariable is Scala's equivalent of ThreadLocal)..

[Ross A. Baker]

There is only one instance of the ScalatraFilter, so the dlSession
variable is not threadsafe. A second request could reset dlSession
between the before and after of the first request. You probably want
to store dlSession as a request attribute or a DynamicVariable.

--
Ross A. Baker
ba...@alumni.indiana.edu
Indianapolis, IN, USA

[Dustin Withers]

Where should I bind that variable? In a companion object to the
Filter? Or should I still do it in my Filters class? Like:
class DailyLifFilter extends ScalatraFilter with ScalateSupport with
DatabaseInit {
val dlSession = new DynamicVariable[Session](null)

before {
dlSession.withValue(SessionFactory.newSession) {
dlSession.value.bindToCurrentThread
}
}

after {
dlSession.value.close
dlSession.value.unbindFromCurrentThread

}
}

On Apr 27, 9:12 am, Wille Faler <wille.fa...@gmail.com> wrote:
> Hmm, making it an instance variable of your filter is not thread-safe, so
> you should probably bind your session to a DynamicVariable/ThreadLocal
> (DynamicVariable is Scala's equivalent of ThreadLocal)..
>

[Wille Faler]

You should probably use either the dlSession.value_ to bind the value, or a request attribute.

The DynamicVariable "withValue" function will only have the value bound inside the closure you provide, so in your code example it will get reset after "before" has run.

[Ross A. Baker]

withValue one sets the value for the life of the block you pass it, so
dlSession would be null again after the before filter. I think you
want:

before {
dlSession.value = SessionFactory.newSession
dlSession.value.bindToCurrentThread
}

after {
dlSession.value.unbindFromCurrentThread
dlSession.value.close
dlSession.value = null
}

Alternatively you could implement Handler:

trait DlSessionSupport extends Handler {

val dlSession = new DynamicVariable[Session](null)

abstract override def handle(req: HttpServletRequest, resp:
HttpServletResponse) {
dlSession.withValue(SessionFactory.newSession) {
dlSession.value.bindToCurrentThread
try {
super.handle(request, response)
} finally {
dlSession.value.close
dlSession.value.unbindFromCurrentThread
}
}
}
}

class DailyLifFilter extends ScalatraFilter with ScalateSupport

with DlSessionSupport

--

[Dustin Withers]

Wow, fantastic. Thanks for the help. I was just able to get the
dynamic variable working, it seemed like I would use the value inside
the block but I was confused. Your handler trait is quite superior. Is
there a wiki that I can post all of this? Most of the google scouring
I have done has not been this helpful and maybe having one place to
show how to do a boring old MySQL connection could be helpful to
others?

[Ross A. Baker]

I tend to prefer handlers to before/after filters, but it's not a
unanimous opinion. Depends on the use case, I guess.

There's a GitHub wiki that has never gotten off the ground.
Documentation is in the README and being migrated to the in-progress
website project. Pull requests for those are always appreciated.
With the right abstraction, I could also see that Handler evolving
into a scalatra-squeryl helper module.

[Wille Faler]

Does Scalatra have an equivalent of an "around"-filter? 

That could replace a lot of ugliness that comes with dealing with before/after/onRequest/etc lifecycle type events, as you can simply compose functions in different ways..

[Maxime Lévesque]

If you write something, let me know so I can put a link to it here :

 http://www.assembla.com/wiki/show/byi9O8iyGr4j12eJe5cbCb

ML

[Ross A. Baker]

Handler is sort of an around filter, with pros (completely wraps
around ScalatraKernel, easier to reuse) and cons (more verbose). But
around filters in the ScalatraKernel could be implemented as a
generalization of the existing before and after filters.

[Dustin Withers]

Written.
http://7sudos.com/blog/scalatra-mysql-and-star-wars-droids

-dustin

On Apr 27, 10:28 am, Maxime Lévesque <maxime.leves...@gmail.com>
wrote:

> If you write something, let me know so I can put a link to it here :
>
>  http://www.assembla.com/wiki/show/byi9O8iyGr4j12eJe5cbCb
>
> ML
>
> On Wed, Apr 27, 2011 at 11:21 AM, Ross A. Baker <ba...@alumni.indiana.edu>wrote:
>
>
>
>
>
>
>
> > I tend to prefer handlers to before/after filters, but it's not a
> > unanimous opinion.  Depends on the use case, I guess.
>
> > There's a GitHub wiki that has never gotten off the ground.
> > Documentation is in the README and being migrated to the in-progress
> > website project.  Pull requests for those are always appreciated.
> > With the right abstraction, I could also see that Handler evolving
> > into a scalatra-squeryl helper module.
>

> > On Wed, Apr 27, 2011 at 11:02 AM, Dustin Withers <fadedd...@gmail.com>

[Dustin Withers]

BTW, if anyone sees any shortcoming or errors in it please tell me and
I'll get them fixed.

On Apr 28, 11:31 pm, Dustin Withers <fadedd...@gmail.com> wrote:
> Written.http://7sudos.com/blog/scalatra-mysql-and-star-wars-droids

[Wille Faler]

If you're using Squeryl, you might want to put your handle call inside a Squeryl transaction{}-block just to be sure. Not sure what the commit/rollback behavior is without it.

[Maxime Lévesque]

I thought the same thing, I can only conclude that you are in autocommit.

[David McLaughlin]

Hi Dustin,

The problem here is that you are not wrapping your Squeryl statements
in transactions. If you look at the Query class in Squeryl dsl package
you'll find you are duplicating code from there in your Scalatra
traits almost verbatim:

https://github.com/max-l/Squeryl/blob/master/src/main/scala/org/squeryl/dsl/QueryDsl.scala

So all you really need to do in your intitialize method is set up the
c3p0 connection pool and Squeryl ConcreteFactory, then inside your
Scalatra views just wrap any database code with transaction or
inTransaction as described here:

http://squeryl.org/sessions-and-tx.html

Using the transaction abstraction also has the benefit of not having
every handler reserving a connection from your pool, regardless of
whether or not it actually needs it. In addition, I'm fairly sure that
if someone was to use transaction within your code, they would get a
NullPointerException since Squeryl will unbind the session before you
try to close and unbind it in your finalizer.

Cheers,
David

[Dustin Withers]

David,

Thanks for this, I'll be sure to update the article. Using
transactions is definitely working out. Although I don't think I
understand the distinction between transaction and inTransaction, I
suspect that is something for concurrent interactions with the
database?

-dustin

On May 1, 11:03 am, David McLaughlin <mesave...@gmail.com> wrote:
> Hi Dustin,
>
> The problem here is that you are not wrapping your Squeryl statements
> in transactions. If you look at the Query class in Squeryl dsl package
> you'll find you are duplicating code from there in your Scalatra
> traits almost verbatim:
>

> https://github.com/max-l/Squeryl/blob/master/src/main/scala/org/squer...

[Dustin Withers]

Ok, I tried modifying my DatabaseSessionSupport trait to be something
much simpler:

abstract override def handle(req: HttpServletRequest, res:
HttpServletResponse) {
transaction {
super.handle(req, res)
}
}

And I'm back to running out of connections. Was there more to this
than what I'm understanding?

Thanks for the input,
-dustin

[David McLaughlin]

The difference between transaction and inTransaction is explained
here: http://squeryl.org/sessions-and-tx.html

I typically used inTransaction everywhere, and then wrap my whole
scalatra handler in transaction {}. This should open only one
connection for the whole request.