Commit or rollback a connection pool

[CromFr]

I'm trying to use manual commit / rollback with a MySQL connection pool, but I can't find a reasonable way to send the "COMMIT" / "ROLLBACK" statements to all used connections in the pool.

auto connPool = new MySQLPool("...");
connPool.onNewConnection = delegate(conn){
	conn.exec("SET autocommit=0");
};

// Lock several connections and send transactions
// [...]

// I'd like to do something like this:
writeln("Apply transactions? (y/n)");
if(readln() == "y\n")
	connPool.execAll("COMMIT");
else
	connPool.execAll("ROLLBACK");

// or this:
if(readln() == "y\n")
	foreach(ref conn ; connPool.connections)
		conn.exec("COMMIT");
else
	foreach(ref conn ; connPool.connections)
		conn.exec("ROLLBACK");

Is there something I'm missing?

I'm willing to send you a PR if we agree on a simple solution ;)

[Abscissa]

First of all, apologies for the delay in my response. (Life's been crazy lately.)

I'll be honest, in my own personal experience, I've kept all of my own SQL very simple (extremely simple), My background is more in being a D guy than an SQL guy. Some years ago I ended up assuming the role of maintainer for this library simply because it was, and continues to be, very important to me, my other projects and (IMHO) the D ecosystem in general. Each previous maintainer had gradually, individually left due to various larger commitments, so I was left with a choice: assume the maintainer role myself, or let a library I absolutely rely on go under. So when it comes to SQL and databases, I'm pretty much a "pointy-haired boss" doing the best I can to avoid being a Dilbertian "pointy-haired boss". Probably most of what I know about SQL and MySQL/MariaDB comes directly from my various experiences maintaining this lib, and contributions from others.

Because of that, I rely very heavily on outsiders such as yourself for SQL/database expertise.

So, based on my extremely limited transaction experience, this use-case you bring up sounds potentially reasonable on the surface to me, but I feel that I lack enough direct experience with transactions in SQL to really be able to make a valid call. Honestly, this is the first time I've come across the idea of calling commit/rollback on all connections (as opposed to just one connection) so I'm really not sure what to make of it, or whether or not to simply blame it on my own inexperience. (Pinging @schveiguy and @sludwig for input on this. Anyone else to ping?) If you have any relevant links to point me to or further direct input or rationale, that would be helpful, too.

What I can tell you is this:

Right now, this lib's connection pool class has a hook named onNewConnection ( http://semitwist.com/mysql-native-docs/v3.0.0/mysql/pool/MySQLPool.onNewConnection.html ). You should be able to use that to build and keep a list/array/whatever of references to every connection that exits (note: it's always possible some of them might be closed, although they'll be automatically reopened upon re-use). Then you can loop through all of them and run any commands you need (being careful to appropriately handle any connections that may be in the middle of returning a resultset, so you don't end a resultset that some other thread/fiber is still busy reading - Be aware, by design, this lib automatically purges the rest of any resultset that hasn't been completely read whenever you attempt any other activity on a connection currently reading a resultset. This was done to avoid the need for users to always pedantically close a resultset manually "just in case" whenever you're done with it).

If others agree this is a useful and valid need, then we could certainly add functionality to MySQLPool to make this easier. Maybe add commitAll/rollbackAll functions, or expose an allConnections range so users don't have to maintain their own list of connections, or whatever the consensus deems appropriate.

[schveiguy]

I too have never used multiple connections for a single transaction.

However, if you have a recent vibe-core, there is a mechanism in Vibe's connection pool to close all unused connections. We wrap this but do not call commit on any of them when closing. See https://github.com/mysql-d/mysql-native/blob/master/source/mysql/pool.d#L459-L470

It might be possible to either expose this functionality from vibe's connectionpool directly, or to accept a delegate handler that you can provide.

Note that this only operates on connections that are allocated but not locked. But I think this is your use case, right?

In other words, the mechanism is there, but just not exposed in a way that allows you to do what you want. If you can propose a PR to expose it, I can take a look.

[schveiguy]

If anyone wants to work on this, I'll consider it, but it's not a high priority. So it likely won't get fixed by me.