Add auto-transaction support?

[schveiguy]

When writing to the database, I'm finding myself copy-pasting these 3 lines:

conn.exec("START TRANSACTION");
scope(failure) conn.exec("ROLLBACK");
scope(success) conn.exec("COMMIT");

I'm wondering if there's a way to do this in a one-liner, something like:

auto transaction = conn.scopedTransaction(); // please bikeshed this

Which automatically does the right thing on destruction. What I don't know, exactly, is how to deal with the scope statements. I don't think there's a way to see if an exception is in-flight in the destructor.

It may be possible to do this:

auto transaction = conn.scopedTransaction();
scope(failure) transaction.rollback(); //  also disables the commit on destructor.

But I don't love it -- if you have a scope failure, you likely always want to rollback, which won't happen if you forget the scope statement.

Possibly a mixin could help, but that's a bit ugly.

Another nice feature we could provide with such a construct -- reentrant transactions. In other words, you can nest transaction statements, and only the outer scope will start and commit/rollback.

Thoughts?

[Abscissa]

That would definitely be nice to have. String mixins would definitely make it possible. I know string mixins tend to make people uncomfortable, but if nothing else, I think at least that would be worthwhile. One thing to think about: how to handle the case where the failure is due to the connection going bad (either severed, or MYXProtocol, etc).

It's an interesting (or frustrating) problem though. Scope guards vs struct RAII, both appear to have their own downsides: Scope guards can't be abstracted away (without mixins), but AFAIK struct RAII can't distinguish between "failure" and "success". I think it would be a good discussion topic for the NG.

I'm not following you with the reentrant tractactions though. Example?

[schveiguy]

What I mean is that I may have an encapsulated object that can save itself. It may save itself with multiple transactions. So I'll have code that looks like this:

void save(Connection conn)
{
    conn.exec("START TRANSACTION"); // exec is my one-liner function
    scope(success) conn.exec("COMMIT");
    scope(failure) conn.exec("ROLLBACK");
    ... // save the data
}

But what if saving this object is part of saving a larger construct? If I try to make the whole thing a transaction, I'm in trouble, as mysql will auto-commit if you execute "START TRANSACTION" again.

So if we have a reentrant startTransaction function, which allows nesting calls (like a lock), that will then only execute the transaction calls once, I can nest these things, and it works on any level of granularity you are looking at.

I also had another idea for the scope dilemma. Instead of a type, you could use a template lambda:

auto execTransaction(alias fn, Args...)(Connection conn, Args args)
{
    conn.exec("START TRANSACTION"); // or conn.startTransaction a la above
    scope(failure) conn.exec("ROLLBACK");
    scope(success) conn.exec("COMMIT");
    return fn(conn, args);
}

I can do this without mysqln's help since it's obviously quite trivial, but it would be a nifty little utility to include. The reentrant functions would be nice too.

[schveiguy]

One thing to think about: how to handle the case where the failure is due to the connection going bad

Can't we check if the connection is valid before calling commit? In any case, if we have the actual functions, the transaction counter can be reset when the kill function is executed, and then no commit or rollback is actually sent.

[Abscissa]

...But what if saving this object is part of saving a larger construct? If I try to make the whole thing a transaction, I'm in trouble, as mysql will auto-commit if you execute "START TRANSACTION" again...

I see. Yes, definitely would be good.

I also had another idea for the scope dilemma. Instead of a type, you could use a template lambda:

Ahh, of course! Since scope guards can't wrap around code that's outside their own encapsulation, you bring the transaction's body into the scope guard's encapsulation. I like it. Definately worth including in mysqln.

Can't we check if the connection is valid before calling commit? In any case, if we have the actual functions, the transaction counter can be reset when the kill function is executed, and then no commit or rollback is actually sent.

I haven't really dealt with transactions much, so "dead connection" just struck me as a special case that likely needed appropriate attention to do...whatever the correct thing would be. I confess I'm not really sure what even happens when a connection dies mid-transaction. I guess automatic rollback would by far make the most sense, but I've been around computers long enough not to assume the sensible thing is necessarily the actuality ;)

[schveiguy]

I guess automatic rollback would by far make the most sense, but I've been around computers long enough not to assume the sensible thing is necessarily the actuality ;)

Indeed, looking on stackoverflow and others, I didn't find anything conclusive. But at the point where the connection is dead, whatever the server was going to do, it did already. I will test to see what actually happens. I've been reading that it depends on the autocommit setting, but part of me thinks even if autocommit is on, the transaction should be rolled back (clearly the user didn't want to do something partially).

[schveiguy]

Another way to do this is to use a lambda:

conn.inTransaction((conn) {
    // do the transaction here
});

which can recurse. But that's really the only way I can imagine to hook scope(failure) and scope(success), AND deal with reentrancy.

It can probably be supported with primitives:

conn.startTransaction;
scope(failure) conn.rollback;
scope(success) conn.commit;

which is reentrant, with any rollback cancelling the whole thing.