add start_txn

[tatchi]

add start_txn cc @jorisgio

[paurkedal]

I am not so sure about this PR. Although parsing overhead is likely limited, it seems wrong to use a non-prepared query for every transaction, when the rest runs by prepared queries. Is it not better to leave it to the use to create a prepared query for the purpose? There is also a different way of doing this in MariaDB, using SET autocommit = 0, and I'm wondering what is the preferred way for MariaDB users, given the C API has commit but start-transaction.

(On the other hand, I think exposing the binding for mysql_real_query could we very useful, though handling parameters and result rows would take some work.)

[jorisgio]

I am not so sure about this PR. Although parsing overhead is likely limited, it seems wrong to use a non-prepared query for every transaction, when the rest runs by prepared queries. Is it not better to leave it to the use to create a prepared query for the purpose?

What do you mean ? we added this, because you can't prepare START TRANSACTION statement. Or are you talking about some other way to prepare transactions ?

There is also a different way of doing this in MariaDB, using SET autocommit = 0, and I'm wondering what is the preferred way for MariaDB users, given the C API has commit but start-transaction.

that is what we were using. But it is a bit finicky when you have connections pool and proxy like proxysql. Means that this becomes stateful, and just to be safe, each time you reuse a connection, you have to ensure that autocomit is 1. This is fine, but for us this ended up beging hundred of thousands of queries per second just to set autocommit, while most queries are not inside transactions, which ended up having a high cost in term of cpu usage. So this is why we added excliplit transaction support.

[paurkedal]

Aha, I recall now there is restriction with prepared queries. In that case, I think this is an acceptable solution given the lack of a full mysql_real_query binding. Thanks, I'll merge in a moment.