database/sql nested transactions support

[Jan Mercl]

AFAICT, the database/sql+database/sql/driver model doesn't support
databases capable of nested transactions.

It seems the current model forces the driver to choose from
transaction isolation or transaction nesting support - depending on if
the transaction context is attached to driver.Conn (the intended case
I think) or to a specific DB per se.

Transactions can stay isolated and have (optional) nesting support by,
for example:

- in database/sql/driver
-- Adding method 'Begin() (inner driver.Tx, err error)' to interface
Tx. It'll return a nested Tx from an existing Tx or (nil, error) if
the driver doesn't support nested transactions.

- in database/sql
-- Adding method 'Begin() (inner sql.Tx, err error)' to interface Tx.
It'll return a nested Tx from an existing Tx or (nil, error) if the
driver doesn't support nested transactions.

In the case the dev team would approve the idea or any similar one,
I'd fill an issue for it. Thanks in advance to anyone caring to
consider the proposal.

-j

[Marko Tiikkaja]

On 2014-03-30 4:16 PM, Jan Mercl wrote:
> It seems the current model forces the driver to choose from
> transaction isolation or transaction nesting support - depending on if
> the transaction context is attached to driver.Conn (the intended case
> I think) or to a specific DB per se.

I have no idea what you mean by this. Transaction isolation and nesting
are two completely different concepts, and I don't see how they're related.

> Transactions can stay isolated and have (optional) nesting support by,
> for example:
>

> <description of a new interface>

How is this better than just running txn.Exec("BEGIN") etc. manually?


.marko

[Jan Mercl]

On Sun, Mar 30, 2014 at 4:42 PM, Marko Tiikkaja <ma...@joh.to> wrote:
> On 2014-03-30 4:16 PM, Jan Mercl wrote:
>>
>> It seems the current model forces the driver to choose from
>> transaction isolation or transaction nesting support - depending on if
>> the transaction context is attached to driver.Conn (the intended case
>> I think) or to a specific DB per se.
>
> I have no idea what you mean by this. Transaction isolation and nesting are
> two completely different concepts, and I don't see how they're related.

Cf. "depending on if the transaction context is attached to
driver.Conn (the intended case I think) or to a specific DB per se",
ie. this is only about how the sql and sql/driver model works, not
about the general situation.

>> Transactions can stay isolated and have (optional) nesting support by,
>> for example:
>>
>> <description of a new interface>
>
>
> How is this better than just running txn.Exec("BEGIN") etc. manually?

""""

If the database has a concept of per-connection state, such state can
only be reliably observed within a transaction.
""""[0]

If 'transaction' means [1] then it is IMO clear that clients of
database/sql should not manage their own transactions.

""""
Prepare creates a prepared statement for use within a transaction.
""""[2]

If a client creates her own nested transaction and calls tx.Prepare,
the above will probably will not be true.

""""
Stmt returns a transaction-specific prepared statement from an
existing statement.
""""[3]

Ditto.

""""
You should not mingle the use of transaction-related functions such as
Begin() and Commit() with SQL statements such as BEGIN and COMMIT in
your SQL code. Bad things might result:

- The Tx objects could remain open, reserving a connection from the
pool and not returning it.
- The state of the database could get out of sync with the state of
the Go variables representing it.
- You could believe you’re executing queries on a single connection,
inside of a transaction, when in reality Go has created several
connections for you invisibly and some statements aren’t part of the
transaction.

""""[4]

The above seems to indicate that the rule of thumb to not use own
transactions with database/sql is recognized by some others as well.

[0]: http://golang.org/pkg/database/sql/#DB
[1]: http://golang.org/pkg/database/sql/#Tx
[2]: http://golang.org/pkg/database/sql/#Tx.Prepare
[3]: http://golang.org/pkg/database/sql/#Tx.Stmt
[4]: http://go-database-sql.org/modifying.html#working_with_transactions

-j

[Marko Tiikkaja]

On 2014-03-30 5:07 PM, Jan Mercl wrote:
> On Sun, Mar 30, 2014 at 4:42 PM, Marko Tiikkaja <ma...@joh.to> wrote:
>>> Transactions can stay isolated and have (optional) nesting support by,
>>> for example:
>>>
>>> <description of a new interface>
>>
>>
>> How is this better than just running txn.Exec("BEGIN") etc. manually?
>
> """"
> If the database has a concept of per-connection state, such state can
> only be reliably observed within a transaction.
> """"[0]
>
> If 'transaction' means [1] then it is IMO clear that clients of
> database/sql should not manage their own transactions.

Right, I guess I should have been more clear about this. I said
"txn.Exec" on purpose; I meant using that to control nested
transactions, and nested transactions only. The *sql.Tx would only be
the top-level transaction.

> """"
> Prepare creates a prepared statement for use within a transaction.
> """"[2]
>
> If a client creates her own nested transaction and calls tx.Prepare,
> the above will probably will not be true.
>
> """"
> Stmt returns a transaction-specific prepared statement from an
> existing statement.
> """"[3]
>
> Ditto.

.. but these two are actually valid points, and would create big caveats
if the user managed their own nested/subtransactions without
database/sql knowing about it.

As for the actual interface, please keep in mind that some databases
don't support nested transactions, but instead support the SQL standard
SAVEPOINT syntax. As far as I can tell, just generating unique names in
the driver and mapping Commit() of a "nested" transaction to RELEASE
SAVEPOINT and Rollback() to ROLLBACK TO SAVEPOINT should work, but maybe
the documentation should reflect that somehow.


.marko

[Jan Mercl]

On Sun, Mar 30, 2014 at 4:16 PM, Jan Mercl <0xj...@gmail.com> wrote:

Gentle ping.

-j

[Brad Fitzpatrick]

We're in a freeze now anyway so I'm not paying attention to this.

We can discuss after Go 1.3 is out.

[Marko Tiikkaja]

On 2014-04-29 7:42 PM, Jan Mercl wrote:
> Gentle ping.

There's a patch for this somewhere? Because I believe I've missed it.


.marko

[Jan Mercl]

On Tue, Apr 29, 2014 at 8:57 PM, Marko Tiikkaja <ma...@joh.to> wrote:
> There's a patch for this somewhere? Because I believe I've missed it.

AFAIK you've not missed anything.

-j

[Marko Tiikkaja]

Then I'm not sure I understand what the ping is for. If you're looking
for feedback on the original proposal, I considered it lacking in
detail. For example:

tx1, _ := db.Begin()
tx2, _ := tx1.Begin()
_ = tx1.Rollback()
// What happens to tx2?

I also don't see any specifics as to how this is supposed to communicate
with the drivers, which currently don't explicitly interact with
transactions at all.


.marko