Nested Transactions vs RollbackTo/Savepoint

[Daniel Theophanes]

I'd like to hear arguments on the following options regarding nested transactions. Would you use them? Which one would you prefer and why?

Nested Transaction / Savepoint RollbackTo

Option (1): Savepoint and RollbackTo

Most SQL database systems do not really support nested transactions directly, but within a transaction implement Savepoint(name string) and RollbackTo(name string). This has the benefit of leaking fewer details of the underlying database.

Option (2): Nested Transaction

Paper over the database system and add "Tx.BeginTx(ctx context.Context, opts *TxOptions) (*Tx, error)". This would allow a seamless interface to be defined that is satisfied by either DB or Tx. I'm unsure of what the driver interface would look like.

Option (3): Do not provide an API for this. Use normal SQL if this is required.

[Jan Mercl]

On Sun, Jan 15, 2017 at 10:39 PM Daniel Theophanes <kard...@gmail.com> wrote:

> I'd like to hear arguments on the following options regarding nested transactions. Would you use them?

I do use nested transactions (obviously not through database/sql b/c of lack of such support in any Go release so far). ((A))

> Which one would you prefer and why?
>

> Option (1): Savepoint and RollbackTo
>

> Option (2): Nested Transaction

>
> Option (3): Do not provide an API for this. Use normal SQL if this is required.

(3) is not an option for me because of ((A)). I'd prefer (2) because it's a superset of (1). DBs not capable of more than a single level of nesting can fail when requested for more levels. OTOH, adopting (1) makes handling true nested transactions in DBs having that feature difficult, if reasonably possible at all.

Disclaimer: I'm biased because of ql, obviously.

--

-j

[Daniel Theophanes]

Thanks Jan. I appreciate the feedback. As far as I'm aware ql is the only sql database that implements nested transactions. All other SQL database I'm aware of use savepoint / rollbackto in their interfaces.

But I could be mistaken. Does anyone know of non-ql SQL database systems that support nested transactions? This isn't to say we shouldn't do (2), but I want to know how much of an abstraction we would need to build if we did.

On Sunday, January 15, 2017 at 1:54:09 PM UTC-8, Jan Mercl wrote:

[jimmy frasche]

The only odd-one out with savepoints that I am aware of is sqlite
which allows you to start a savepoint outside of a transaction.

This is not something that needs to be modeled in the database/sql,
however. Requiring an outer transaction is even if the db does not is
perfectly reasonable.

I prefer (2) for the same reasons Jan gives

> --
> You received this message because you are subscribed to the Google Groups
> "golang-sql" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to golang-sql+...@googlegroups.com.
> To post to this group, send email to golan...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/golang-sql/cdc818fb-ca24-4b20-a160-92db17a718e2%40googlegroups.com.
>
> For more options, visit https://groups.google.com/d/optout.

[Steven Hartland]

I can't say I've ever had cause to use nested transactions and if its not commonly supported feature by DB's, it begs the question:

Is the call for it great enough to for it to in the core, or would time be better spent improving other areas?

--

You received this message because you are subscribed to the Google Groups "golang-sql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-sql+...@googlegroups.com.
To post to this group, send email to golan...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/golang-sql/e2428ed6-b1c8-42e6-b733-b62e644088a2%40googlegroups.com.

[jimmy frasche]

savepoints are standard and supported by ms sql server, oracle,
mysql/mariadb, sqlite, and postgres.

> https://groups.google.com/d/msgid/golang-sql/8d9652cc-c375-e84d-360c-75c6ab051eb4%40multiplay.co.uk.

[Daniel Theophanes]

I've been thinking about this design for some time now.

I would like to add in the Tx.RollbackTo and Tx.Savepoint APIs as in https://go-review.googlesource.com/c/30165/.  This will allow users to create a "nestable" transaction interface. The sql.DB and the sql.Tx API would not naturally be nesting. I hope to also code up one such implementation with a common querier interface in the github.com/golang-sql/TBD repo.

Rational:

In the primary line of business application I work on it matters a great deal to me and the SQL I write if the SQL is in a Tx already or not. If I'm not in a TX, I do BEGIN TRAN and COMMIT TRAN. If I am in a tran I"ll use SAVEPOINT. In other words, for people who use complex procedural SQL, using a common querier interface would be a detriment, not a benefit. That being said, if you use an ORM for everything, you don't have your own SQL TX, I can see how a common interface may help with mocks. In that case we can still leverate the RollbackTo and Savepoint API to create a common Querier interface.  Lastly, aside from Jan's ql database, about every major database that has a go driver supports Savepoints, not nested transactions (that's the SQL Standard).

Please voice any concerns or support for this proposal.

Thanks, -Daniel

> email to golang-sql+unsubscribe@googlegroups.com.

> To post to this group, send email to golan...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/golang-sql/e2428ed6-b1c8-42e6-b733-b62e644088a2%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "golang-sql" group.
> To unsubscribe from this group and stop receiving emails from it, send an

> email to golang-sql+unsubscribe@googlegroups.com.

[Jan Mercl]

On Mon, Jan 23, 2017 at 8:59 AM Daniel Theophanes <kard...@gmail.com> wrote:

> The sql.DB and the sql.Tx API would not naturally be nesting.

Sad to hear that.

> Lastly, aside from Jan's ql database, about every major database that has a go driver supports Savepoints, not nested transactions (that's the SQL Standard).

FWIW, let me put a little perspective here:

--

-j

[Daniel Theophanes]

Jan, ql would still be able to use the database/sql API. It would just require a name to tx map.

This isn't set in stone. What do think of the reasons I gave?

[Jan Mercl]

On Mon, Jan 23, 2017 at 4:21 PM Daniel Theophanes <kard...@gmail.com> wrote:

> This isn't set in stone. What do think of the reasons I gave?

Understood, I hope. All valid, of course.

--

-j