Working with “InnoDB” tables: using the “commit()” and “autocommit()” methods - MySQL

In order to use the “commit()” and “autocommit()” methods, you should define your database tables as being of type “InnoDB.” Other types, such as “MyISAM” or “ISAM” won’t support these features. Keep this limitation in mind before implementing the methods that I mentioned before.

Having clarified the previous issue, let me describe briefly the meaning of the COMMIT and ROLLBACK statements. In short, the COMMIT statement means that the changes made during a transaction to a particular database (or a set of databases) are permanent and become visible to other users. On the other hand, as you may have guessed, a ROLLBACK statement will allow you to cancel the modifications made during the current transaction. Quite simple, right?

Since the above mentioned features are only available in “InnoDB” tables, here is an example that shows hot to use the “commit()” and “autocommit()” methods, assuming that the correct type of table is used:

// commit-autocommit example that uses an InnoDB table
$mysqli=new mysqli('host','user','password','database');
if(mysqli_connect_errno()){
    trigger_error('Error connecting to host. '.$mysqli-
>error,E_USER_ERROR);
}
// turn off AUTOCOMMIT, then run some queries
$mysqli->autocommit(FALSE);
$mysqli->query("INSERT INTO customers (id,name,email) VALUES
(NULL,'customer1','email1@domain.com')");
$mysqli->query("INSERT INTO customers (id,name,email) VALUES
(NULL,'customer2','email2@domain.com')");
// commit transaction
$mysqli->commit();
// close connection
$mysqli->close();

In this case, I used three different methods that come with the “mysqli” extension, after performing the corresponding connection to the MySQL server and selecting the appropriate database. The first one, “autocommit()”, turns off the AUTOCOMMIT feature of MySQL, which means that the current transaction will be open for the selected user. Then, the script inserts two new customers into the sample “CUSTOMERS” table via the familiar “query()” method, and finally it commits the transaction, obviously by using the “commit()” method.

As you can see, using the AUTOCOMMIT and COMMIT features available in “InnoDB” tables is a fairly understandable process, which can be performed by using a few simple methods. Now, let’s move on and see how the “ROLLBACK” feature can be implemented with the “mysqli” extension. That’s exactly the subject of the next section.