transaction/transactional/readonly

[gobagoo]

I have wrapped all of my individual SELECT statements in
transactional() blocks. However this causes each statement to wrapped
in BEGIN .. ROLLBACK.

transactional() =>
LOG: execute S_1: BEGIN
LOG: execute <unnamed>: SELECT * FROM attributes WHERE attribute_id =
$1
DETAIL: parameters: $1 = '1043'
LOG: execute S_2: ROLLBACK
LOG: execute S_1: BEGIN
LOG: execute <unnamed>: SELECT * FROM attributes WHERE attribute_id =
$1
DETAIL: parameters: $1 = '1044'
LOG: execute S_2: ROLLBACK

If i change them to transaction() blocks I get =>

LOG: execute S_1: BEGIN
LOG: execute <unnamed>: SELECT * FROM attributes WHERE attribute_id =
$1
DETAIL: parameters: $1 = '1039'
LOG: execute S_2: COMMIT
LOG: execute S_1: BEGIN
LOG: execute <unnamed>: SELECT * FROM attributes WHERE attribute_id =
$1
DETAIL: parameters: $1 = '1040'
LOG: execute S_2: COMMIT

If I change them to readOnly() blocks I get =>

LOG: execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION
READ ONLY
LOG: execute S_1: BEGIN
LOG: execute <unnamed>: SELECT * FROM attributes WHERE attribute_id =
$1
DETAIL: parameters: $1 = '1065'
LOG: execute S_2: ROLLBACK
LOG: execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION
READ ONLY
LOG: execute S_1: BEGIN
LOG: execute <unnamed>: SELECT * FROM attributes WHERE attribute_id =
$1
DETAIL: parameters: $1 = '1069'
LOG: execute S_2: ROLLBACK


My question is, is there a way to use orbroker so that when one
transaction is started, future statements will not try to start their
own transactions?? Should I somehow set up my 'select' functions so
that they receive an implicit transaction?

Code is here: https://github.com/atistler/mailproc/tree/master/src/main/scala/db

[Nils Kilden-Pedersen]

The only difference between transactional() and transaction() blocks are that with transactional() it's your responsibility to commit() or rollback(). If you don't, you'll get a RollbackException. With transaction() blocks, the code is committed for you, unless an exception is thrown, in which case it's rolled back.

Ok, now looking at your code, it appears that you've provided each element with the ability to persist itself. That means, as you've discovered, that they will not partake in the same transaction. And it also means that, as you may also have discovered, that it will perform horribly.

O/R Broker is designed so that everything is explicit, i.e. there's no hidden ThreadLocal session.

I will propose these two solutions, the first being my recommendation in general, but I'm not sure if that is easy for you for this code, and the second being the solution to this particular problem.

1. Decouple query/persistence code from the objects themselves. This is generally considered a poor practice, in part due to your present problem. When decoupling the code, you get cleaner classes and you're not tying yourself to a given framework.
2. Use the Broker.transactional(conn: java.sql.Connection) method instead, for both queries and save/updates. In that case, you control the scope by supplying a Connection object. How you manage that, is in your hands. You could do it either implicitly, by some ThreadLocal object, or pass it in explicitly.

If you go with 2, you'll still need some central starting point when saving, to at least commit the transaction on the Connection object. I would imagine something like this:

object GloballyReachable {

  private val threadLocalConn: ThreadLocal[java.sql.Connection]

  val connection: java.sql.Connection = threadLocalConn.get

}

def saveEverything() {

  saveWhatNeedsToBeSaved()

  connection.commit()

}

And the code inside your e.g. Attribute class would start like this:

def save() = {

  broker.transactional( GloballyReachable.connection ) { t =>

    // Do whatever

  }

}

And depending on how transactionally consistent you want your reads to be, you could do the same with those.

Hope that helps,

Nils

[gobagoo]

Hey Nils,

Thanks for the great (and very detailed) answer. I see what you mean
regarding passing the connection explicitly. I don't think that
option 1) is really a possiblility for me given the db structure. I
will need to do some work on reorganizing my code since 'saves()' may
occur in different actors(threads) so I think that I will need to pass
around the connection explicitly rather than keeping it a global.
Anyway, this is my first scala project that is anything more than a
script, so I excepted to run into problems. Thanks again.

On Aug 31, 9:35 am, Nils Kilden-Pedersen <nil...@gmail.com> wrote:
> The only difference between transactional() and transaction() blocks are
> that with transactional() it's your responsibility to commit() or
> rollback(). If you don't, you'll get a RollbackException. With transaction()
> blocks, the code is committed for you, unless an exception is thrown, in
> which case it's rolled back.
>
> Ok, now looking at your code, it appears that you've provided each element
> with the ability to persist itself. That means, as you've discovered, that
> they will not partake in the same transaction. And it also means that, as
> you may also have discovered, that it will perform horribly.
>
> O/R Broker is designed so that everything is explicit, i.e. there's no
> hidden ThreadLocal session.
>
> I will propose these two solutions, the first being my recommendation in
> general, but I'm not sure if that is easy for you for this code, and the
> second being the solution to this particular problem.
>

>    1. Decouple query/persistence code from the objects themselves. This is

>    generally considered a poor practice, in part due to your present problem.
>    When decoupling the code, you get cleaner classes and you're not tying
>    yourself to a given framework.

>    2. Use the Broker.transactional(conn: java.sql.Connection) method