원본 :http://dev.mysql.com/tech-resources/articles/mysql-connector-cpp.html#trx
Using Transactions
A database transaction is a set of one or more statements that are executed together as a unit, so either all of the statements are executed, or none of them are executed. MySQL supports local transactions within a given client session through statements such as SET autocommit, START TRANSACTION, COMMIT, and ROLLBACK.
Disable AutoCommit Mode
By default, all the new database connections are in autocommit mode. In the autocommit mode, all the SQL statements will be executed and committed as individual transactions. The commit occurs when the statement completes or the next execute occurs, whichever comes first. In case of statements that return a ResultSet object, the statement completes when the last row of the ResultSet object has been retrieved or the ResultSet object has been closed.
One way to allow multiple statements to be grouped into a transaction is to disable autocommit mode. In other words, to use transactions, the Connection object must not be in autocommit mode. The Connection class provides the setAutoCommit method to enable or disable the autocommit. An argument of 0 to setAutoCommit() disables the autocommit, and a value of 1 enables the autocommit.
Connection *con; .. /* disable the autocommit */ con -> setAutoCommit(0);
It is suggested to disable autocommit only while you want to be in transaction mode. This way, you avoid holding database locks for multiple statements, which increases the likelihood of conflicts with other users.
Commit or Rollback a Transaction
Once autocommit is disabled, changes to transaction-safe tables such as those for InnoDB and NDBCLUSTER are not made permanent immediately. You must explicitly call the method commit to make the changes permanent in the database or the method rollback to undo the changes. All the SQL statements executed after the previous call to commit() are included in the current transaction and committed together or rolled back as a unit.
The following code fragment, in which con is an active connection, illustrates a transaction.
Connection *con;
PreparedStatement *prep_stmt;
..
con -> setAutoCommit(0);
prep_stmt = con -> prepareStatement ("INSERT INTO City (CityName) VALUES (?)");
prep_stmt -> setString (1, "London, UK");
prep_stmt -> executeUpdate();
con -> rollback();
prep_stmt -> setString (1, "Paris, France");
prep_stmt -> executeUpdate();
con -> commit();
In this example, autocommit mode is disabled for the connection con, which means that the prepared statement prep_stmt is committed only when the method commit is called against this active connection object. In this case, an attempt has been made to insert two rows into the database using the prepared statement, but the first row with data "London, UK" was discarded by calling the rollback method while the second row with data "Paris, France" was inserted into the City table by calling the commit method.
Another example to show the alternate syntax to disable the autocommit, then to commit and/or rollback transactions explicitly.
Connection *con;
Statement *stmt;
..
stmt = con -> createStatement();
//stmt -> execute ("BEGIN;");
//stmt -> execute ("BEGIN WORK;");
stmt -> execute ("START TRANSACTION;");
stmt -> executeUpdate ("INSERT INTO City (CityName) VALUES ('London, UK')");
stmt -> execute ("ROLLBACK;");
stmt -> executeUpdate ("INSERT INTO City (CityName) VALUES ('Paris, France')");
stmt -> execute ("COMMIT;");
The START TRANSACTION or BEGIN statement starts a new transaction. COMMIT commits the current transaction to the database by making the changes permanent. ROLLBACK rolls back the current transaction by canceling the changes to the database. With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.
BEGIN and BEGIN WORK are supported as aliases of START TRANSACTION for initiating a transaction. START TRANSACTION is standard SQL syntax and it is the recommended way to start an ad-hoc transaction.
Rollback to a Savepoint within a Transaction
The MySQL connector for C++ supports setting savepoints with the help of Savepoint class, which offer finer control within transactions. The Savepoint class allows you to partition a transaction into logical breakpoints, providing control over how much of the transaction gets rolled back.
As of this writing, InnoDB and Falcon storage engines support the savepoint transactions in MySQL 6.0.
To use transaction savepoints, the Connection object must not be in autocommit mode. When the autocommit is disabled, applications can set a savepoint within a transaction and then roll back all the work done after the savepoint. Note that enabling autocommit invalidates all the existing savepoints, and the Connector/C++ driver throws an InvalidArgumentException when an attempt has been made to roll back the outstanding transaction until the last savepoint.
A savepoint is either named or unnamed. You can specify a name to the savepoint by supplying a string to the Savepoint::setSavepoint method. If you do not specify a name, the savepoint is assigned an integer ID. You can retrieve the savepoint name using Savepoint::getSavepointName().
Signatures of some of the relevant methods are shown below. For the complete list of methods supported by Connection, Statement, PreparedStatement and Savepoint interfaces, check the connection.h, statement.h and prepared_statement.h headers in your Connector/C++ installation.
/* connection.h */ Savepoint* Connection::setSavepoint(const std::string& name); void Connection::releaseSavepoint(Savepoint * savepoint); void Connection::rollback(Savepoint * savepoint);
The following code fragment inserts a row into the table City, creates a savepoint SAVEPT1, then inserts a second row. When the transaction is later rolled back to SAVEPT1, the second insertion is undone, but the first insertion remains intact. In other words, when the transaction is committed, only the row containing "London, UK" will be added to the table City.
Connection *con;
PreparedStatement *prep_stmt;
Savepoint *savept;
..
prep_stmt = con -> prepareStatement ("INSERT INTO City (CityName) VALUES (?)");
prep_stmt -> setString (1, "London, UK");
prep_stmt -> executeUpdate();
savept = con -> setSavepoint ("SAVEPT1");
prep_stmt -> setString (1, "Paris, France");
prep_stmt -> executeUpdate();
con -> rollback (savept);
con -> releaseSavepoint (savept);
con -> commit();
The method Connection::releaseSavepoint takes a Savepoint object as a parameter and removes it from the current transaction. Any savepoints that have been created in a transaction are automatically released and become invalid when the transaction is committed, or when the entire transaction is rolled back. Rolling back a transaction to a savepoint automatically releases and invalids any other savepoints that were created after the savepoint in question. Once a savepoint has been released, any attempt to reference it in a rollback operation causes the SQLException to be thrown.