playframework/playframework — pull request #1854 by jroper (2014-08-25 08:49:22+00:00)
Title: play.api.db.DB.withTransaction doesn't restore autocommit or reset connection

Play 2.2.0's withTransaction (and getConnection, which probably it should call directly) doesn't restore autocommit when it's done. bonecp's resetconnectiononclose option does not seem to be set, so it doesn't either. As a result, in some cases bonecp tries to reuse a connection with an existing, active transaction, which causes an error (at least on postgres):

```
play.api.Application$$anon$1: Execution exception[[PSQLException: Cannot change transaction read-only property in the middle of a transaction.]]
at play.api.Application$class.handleError(Application.scala:293) ~[play_2.10.jar:2.2.0]
at play.api.DefaultApplication.handleError(Application.scala:399) [play_2.10.jar:2.2.0]
at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$12$$anonfun$apply$1.applyOrElse(PlayDefaultUpstreamHandler.scala:165) [play_2.10.jar:2.2.0]
at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$12$$anonfun$apply$1.applyOrElse(PlayDefaultUpstreamHandler.scala:162) [play_2.10.jar:2.2.0]
at scala.runtime.AbstractPartialFunction.apply(AbstractPartialFunction.scala:33) [scala-library.jar:na]
at scala.util.Failure$$anonfun$recover$1.apply(Try.scala:185) [scala-library.jar:na]
Caused by: org.postgresql.util.PSQLException: Cannot change transaction read-only property in the middle of a transaction.
at org.postgresql.jdbc2.AbstractJdbc2Connection.setReadOnly(AbstractJdbc2Connection.java:725) ~[postgresql-9.2-1003-jdbc4.jar:na]
at com.jolbox.bonecp.ConnectionHandle.setReadOnly(ConnectionHandle.java:1279) ~[bonecp-0.8.0-rc1.jar:na]
at com.jolbox.bonecp.ConnectionHandle.(ConnectionHandle.java:254) ~[bonecp-0.8.0-rc1.jar:na]
at com.jolbox.bonecp.ConnectionHandle.recreateConnectionHandle(ConnectionHandle.java:273) ~[bonecp-0.8.0-rc1.jar:na]
at com.jolbox.bonecp.ConnectionHandle.close(ConnectionHandle.java:476) ~[bonecp-0.8.0-rc1.jar:na]
at play.api.db.AutoCleanConnection.close(DB.scala:485) ~[play-jdbc_2.10.jar:2.2.0]
```

This only seems to cause a problem for some exception cases that aren't caught by withTransaction, such as exceptions in initializers. Here's one way to reproduce:

object fail {
val x : Int = (null : Option[Int]).get
}
def action = Action { request => DB.withTransaction { db =>
db.prepareStatement("SELECT 1").execute // do anything with db
Ok(fail.x.toString) // trigger null pointer in fail object init
} }

This code will trigger the above exception, effectively masking the real error cause. Setting bonecp's resetConnectionOnClose option might be the easiest way to fix this.