Implementing the commit() and rollback() Methods with mysqli and PHP 5 - Working with “InnoDB” tables: using the “commit()” and “autocommit()” methods

(Page 2 of 4 )

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. 

Next: Canceling database modifications: using the “rollback()” method >>
 

More MySQL Articles
More By Alejandro Gervasio

Comments On: Implementing the commit() and rollback() Methods with mysqli and PHP 5

· In this second installment of the series, the use of the "commit()" and rollback()...    · what about MyISAM type databases, can same methods work with MyISAM DBs. If not,...    · Thank you for commenting on my article. With regard to your question, you have some...    · Thank you for this tutorial. As I was implementing transactions into my project I...    · I made a mistake. I was using mysqli->multi_query(), this prevented the transaction...    · Hello again Rafael,Thank you for posting your comments here, and also I'm glad...   >>> Post your comment now!