How to start MySQL transaction that will be committed by mysql_commit()

Question

I'm writing a C++ application that uses MySQL C API to connect to the database. MySQL server version is 5.6.19-log.

I need to run several SQL UPDATE, INSERT and DELETE statements in one transaction to make sure that either all changes or no changes are applied.

I found in the docs functions mysql_commit() and mysql_rollback() that finish the transaction (commit it or roll it back), but I can't find a corresponding function that starts a transaction.

Is there such a function? Am I missing something obvious?

---

I run UPDATE, INSERT and DELETE statements using mysql_real_query() function.

I guess I should be able to start the transaction by running START TRANSACTION SQL statement using same mysql_real_query() function. Then I should be able to commit the transaction by running COMMIT SQL statement using same mysql_real_query() function.

But then, what is the point of having dedicated mysql_commit() and mysql_rollback() functions in the API?

Answer 1

It looks like MySQL C API indeed doesn't have a dedicated function that is equivalent of the START TRANSACTION SQL statement.

The MySQL C API has mysql_commit() function that does the same as COMMIT SQL statement.

The MySQL C API has mysql_rollback() function that does the same as ROLLBACK SQL statement.

But, there is no function for starting the transaction in this API.

Answer 2

//connect to mysql server:
MYSQL *mysql = mysql_init(NULL);  
mysql  = mysql_real_connect(mysql, ......)

//turn off auto_commit
mysql_autocommit(mysql , false);

OR

//start a tranaction directly as follows
mysql_real_query(mysql,"BEGIN");

//run your commands:
mysql_real_query(mysql,"UPDATE...");

//commit your transaction
mysql_real_query(mysql, "COMMIT");