MSc BIT BSc HND PHP Web Development Projects Assignments Guidance Local London A/L O/L ICT Tuition: How to implement PHP: PDO::beginTransaction/Commit/Rollback for MySQL

When started with MySQL, we do not care too much about whether all requests that are going to go perfectly run. We can of course put a <? php or exit (mysql_error ()); ?> at the end of each query (or a more elaborate system) but this is not always enough.

Indeed it is necessary that the MySQL server returns an error (so if it breaks down at this point it's brown) or the PHP server can also handle this error (like his colleague he may also have issues) . We must therefore ensure that for certain queries (INSERT and UPDATE among others) everything went as we wanted before applying the changes to the database.

You still do not see the interest of the transactions? Instead you're interested and want to know more?Luckily that is the purpose of this tutorial.

Explanations

Examples to better understand

Banking transactions

We'll take an honest citizen (Mr. Michou) who wants to buy a server that is € 2,000 he decides to pay by credit card. He then gives his number and all the information to go with the bank to make the transfer of money between the account and the account Mr. Michou server vendor. This bank after making sure that everything was in order will then transfer the money from the account of the buyer to the seller.

First of all the bank fee € 2000 on the account of Mr. Michou.




UPDATE  account  SET  money  =  money  -  2000  WHERE  compte_proprio  =  michou

Then she sends the money to the account of the server vendor.




UPDATE  account  SET  money  =  money  +  2000  WHERE  compte_proprio  =  seller

The transfer is being well done, sir Michou receives its server. There everything went well because the two queries are properly conducted, but it can also be an error during the transfer. Using the previous example:

First of all the bank fee € 2000 on the account of Mr. Michou.




UPDATE  account  SET  money  =  money  -  2000  WHERE  compte_proprio  =  michou

Then she sends the money to the account of the server vendor.




// crash  the  server  MySQL

And there is the drama (for Mr. Michou), the bank has withdrawn € 2,000 in his account, but the money never arrived on the account of the seller who therefore never send the server saw it n has not received any money. It should therefore be able to ensure that these two queries are performed correctly before applying them. This is the purpose of this tutorial.

MySQL Storage Engines

The special feature of MySQL is to provide multiple storage engines in the same database. Without going into details I'll give you a brief overview of some of these current engines by detailing some of their features, all in order to understand when and why to use a particular storage engine. Because these engines have very specific features which have even entrained misconceptions for a while.

MyISAM: the most widespread

Benefits ...

MyISAM is the default engine for MySQL, so far the concept of storage engine was unknown to you, it's probably this engine where you use for your beginning with MySQL. This engine is very popular because it is very easy to use (for a beginner) and offers excellent performance on very frequently open read-write tables.

Its other strong point is to offer a FULL-TEXT index, which allows for fairly precise research (compared with LIKE) to columns of text and thus allows everyone to have a small search engine, including through sort by relevance.

... And cons

To stay on the FULL-TEXT index, it nevertheless suffers on large tables, more configuration (word size in particular) is only available on a dedicated server.

But the two biggest flaws of MyISAM is that it supports neither foreign keys or transactions (which are the subject of this tutorial). This is also the strong presence of this engine (more proposed by default) that some believe that MySQL does not support transactions. This is wrong, but this requires using a storage engine that supports: InnoDB.

InnoDB: an engine for robust bases

Unlike MyISAM, InnoDB is an engine that is used to its features that make it the most used engine in sensitive sectors, that is to say requiring consistency and high data integrity (finance, online games very solicited complex architecture, etc ...).

His two great strengths are its management of foreign keys and transaction support (ah I feel a renewed interest small player

). These transactional mechanisms have high compatibility with the criteria ACID .

Regarding its flaws: besides having larger tables (on average 25% larger) and not offer FULL-TEXT index, InnoDB is slightly slower in operations, but this is due to the tests integrity (foreign key and transactions) that keep a consistent basis.

Memory (Heap): everything in Ram

As the name suggests, this storage engine stores data of the table into memory, the structure when it is stored in a file. Its main interest is its speed access, very useful for a very heavily used table. The problem is that in case of server shutdown, all stored data is deleted (since stored in the Ram which empties during the current blocking).

So use this storage engine for data that are not essential to the operation of such a site a visitor counter or a chat system (unless you want to keep track of discussions).

Storage engine for MySQL, there are many other but the main ones are there, let not wait to practice.

The theory: MySQL

As stated above, use the InnoDB storage engine, for it must be specified when creating the table on which we will work:




CREATE  TABLE  account


(


    - List of fields


)


ENGINE  =  InnoDB ;

This simple table that we will use, by taking the first example contains 2 fields:

the name of the account owner
the amount on the account




CREATE  TABLE  account


(


    name  varchar  ( 30 )  NOT  NULL ,


    amount  MEDIUMINT  UNSIGNED  NOT  NULL


)


ENGINE  =  InnoDB ;

We will of course insert our 2 users, the seller and M. Michou (here called Buyer) by giving them each a well filled account.




INSERT  INTO  account


( name , value )


VALUES


( 'vendor' ,  '10000' ) ,


( buyer ' ,  '25000' ) ;

Transactions: COMMIT and ROLLBACK

The principle of transactions is very simple to implement: one launches the transaction with START TRANSACTION; then perform operations on the tables, and then we have 2 choices:

We validate these operations with COMMIT.
It cancels any changes with ROLLBACK.

But by default, the InnoDB storage engine is set to automatically validate all transactions because it considers each individual application as a transaction: this behavior is canceled with the command SET autocommit = 0; we'll see right away what happens in convenient:




- It disables autocommit


SET  autocommit  =  0 ;


- The transaction is launched


START  TRANSACTION ;


- Performing this simple query on our table Account


UPDATE  account  SET  amount  =  amount  +  20000  WHERE  name  =  'seller' ;

You can perform this suite of applications as many times as you want, the amount the seller will not change an inch, the transaction has not been validated. For this it is necessary to use the COMMIT command will validate requests made during the transaction:




- It disables autocommit


SET  autocommit  =  0 ;


- The transaction is launched


START  TRANSACTION ;


- Performing this simple query on our table Account


UPDATE  account  SET  amount  =  amount  +  20000  WHERE  name  =  'seller' ;


- It validates the transaction


COMMIT ;

And then magically the request is considered.

There the COMMIT command to commit, but there is also the ROLLBACK to cancel it returns to the structure before the start of the transaction, we make a clear Ctrl + Z on our database. Very useful if there is a problem like this one for example:




- It disables autocommit


SET  AUTOCOMMIT  = 0 ;


- The transaction is launched


START  TRANSACTION ;


- Performing this simple query on our table Account


UPDATE  account  SET  amount  =  amount  +  20000  WHERE  name  =  'seller' ;


- This query returns an error


UPDATE  account  SET  amount  =  amount  -  20000  WHERE  name  =  'foo' ;


- So it cancels the transaction


ROLLBACK  ;

If these two transactions were made outside a transaction, the seller would have received his money but there would had a problem since the thing member does not exist, so in the end the total amount in my table is no longer the Similarly, it was more then a consistent basis, hence the importance of the transactions.

To make best use of transactions, will now pass to examples using PHP.

Practice: use of PDO

PDO to interface with MySQL

PDO overview

There are many ways to connect to a database with PHP, one of them is to use PDO which in particular use natively transactions. For those who do not know I advise you to read this tutorial in order to have this interface by hand.

PDO in particular featured the concept of object-oriented programming, it is still very simple and accessible to those who have never programmed object. The first thing to do is connect to the database: it is necessary as with the usual driver MySQl provide the following information:

The address of the database (localhost if you are working locally)
The name of the database (here transactions)
The user name (root here)
And of course the password to access the database (test here)

It only remains to create a PDO for interacting with MySQL.




<? Php  $ pdo  =  new  PDO ( 'mysql: host = localhost; dbname = transactions' ,  'root' ,  'test' ) ;  ?>

For more details I recommend you read the tutorial that I suggested to you earlier.

The exceptions

One advantage of PDO is to use exceptions. This will allow us to initiate a transaction and depending on the outcome (success or failure of the transaction) act accordingly:

If successful: continue following the script (confirmation, validation, etc).
If unsuccessful: An error was detected, it is then possible to recover (in a log for example) and then treat it. In most cases the errors are foreign key problems or just coding errors. In this sense the exceptions are also very useful for debugging a system without any screw up during testing.

The easiest way for me is to mount an example, you will immediately understand better. An exception will use to connect to the previous database.




<? Php


try


{


    $ Pdo  =  new  PDO ( 'mysql: host = localhost; dbname = transactions' ,  'root' ,  'test' ) ;


}


catch ( Exception  $ e )


{


    echo  "Failed to connect to database ' ;


    exit ( ) ;


}


?>

The first part can be seen as an attempt by the system (means test try in English) to execute the code placed in the first brace. If all goes well then everything is fine but if the script portion returns an error, it will then be used by the catch statement (catch English). Then the code placed in the brackets will be executed. In our example we will then display the error to why the connection to the database failed.




<? Php


catch ( Exception  $ e )


{


    echo  'Error:' . $ e -> getMessage ( ) . <br /> ' ;


    echo  'No' . $ e -> getCode ( ) ;


}

Most of the time the error is explicit (wrong password, the db does not exist, the SQl server is not responding), otherwise with the given error number and a Google search found the solution very quickly.This system is used on the SdZ to display an error page when the PostgreSQL server has a problem (often overloaded).

A complete transaction

The first step is to initialize the transaction with the PDO beginTransaction (we rarely easier). I do not rebuke the previous code you're tall.




<? Php  $ pdo -> beginTransaction ( ) ;  >?

The suite is very simple, just simply run SQL queries that one wishes (these queries are fanciful):




<? Php


$ Pdo -> query ( 'SELECT * FROM WHERE foo = foo  \ ' thing \ ' ' ) ;





$ Pdo -> query ( 'INSERT INTO foo = foo SET  \ stuff \ ' , thing =  \ ' I \ ' ' ) ;





$ Pdo -> query ( 'UPDATE foo SET number = number + 1' ) ;

Finally we apply all these requests with the commit method:




<? Php  $ PDO -> commit ( ) ;  >?

If all goes well then these three queries will be applied to the database, but if an error occurs we will treat it and cancel previous requests to the rollback method.




<? Php  $ pdo -> rollback ( ) ;  >?

What with the system of exceptions gives it to the final:




<? Php


try


{


    // When trying to execute the following queries in a transaction





    // We start the transaction


    $ Pdo -> beginTransaction ( ) ;





    3 // our requests


    $ Pdo -> query ( 'SELECT * FROM WHERE foo = foo  \ ' thing \ ' ' ) ;





    $ Pdo -> query ( 'INSERT INTO foo = foo SET  \ stuff \ ' , thing =  \ ' I \ ' ' ) ;





    $ Pdo -> query ( 'UPDATE foo SET number = number + 1' ) ;





    // If so far all goes well it commits the transaction


    $ PDO -> commit ( ) ;


   


    // We display a small message confirmation


    echo  's Everything \ ' went well. ' ;


}


catch ( Exception  $ e )  // if an error


{


    // We cancel the transation


    $ Pdo -> rollback ( ) ;





    // We display an error message and errors


    echo  'Not everything s \ ' is not well, see the following errors <br /> ' ;


    echo  'Error:' . $ e -> getMessage ( ) . <br /> ' ;


    echo  'No' . $ e -> getCode ( ) ;





    // We stop execution if any of the following code


    exit ( ) ;


}

So what does a complete transaction, it helps to make sure everything went well and we will not end up with an incorrect database such as a message of a forum which is not part any subject because the creation of the subject failed.

Transactions are mainly used on databases that require reliability (finance, health, defense). All major RDBMS (Oracle, PostgreSQL, Firebird, DB2, SQL server, etc.) provide this functionality which is essential for some.

I am aware that for small transactions websites are not a necessity, however, I hope you enjoyed this tutorial.

Ref - https://openclassrooms.com/courses/les-transactions-avec-mysql-et-pdo

==========================================================================

Php PDO Transactions - BeginTransaction,RollBack,Commit

In transaction management query is not actually applied to database untill we does not commit the transaction.
And if any error occurs then we can rollback the transaction to the previous state.

Understanding Basic Transaction Flow

<?php
// Begin transaction
If(success)
{
   If(success)
   {      // Commit the transaction   }
   else
   {      // Roll back the transaction   }
}
else
{
   // Rollback the transaction
}
?>

Using Php PDO we can manage transaction easily.
Here is some Explanation of How Php PDO transactions works.

PDO::beginTransaction– commit the transaction

PDO::beginTransaction() used to begin the transaction. This function will return true on success and false on error. By calling this function autocommit mode will gets turned off.

<?php
// Begin a transaction 
$obj->beginTransaction();
?>

PDO::commit – commit the transaction

PDO::commit() is used to commit any changed made to DB via PDO object. This will return true on success and false on error.The return type is boolean. Basically this will commit a transaction and autocommit the database connection until the next beginTransaction call.

<?php
    // Begin a transaction
    $obj->beginTransaction();
    // Change the database schema
    $obj->exec("DROP TABLE books");
    // Commit the changes 
    $obj->commit();
    // Database connection is now back in autocommit mode 
?>

PDO::rollBack – Roll back a Transaction

PDO::rollback() will rollback all changes made to database after calling beginTransaction statement and return the connection to autocommit mode.
The return type is boolean.This also return true on success and false on error.

<?php
// Begin a transaction, turning off autocommit 
$obj->beginTransaction();
// Change the database schema and some data 
$obj->exec("UPDATE books SET name = 'PhpBooks'");
// Recognize mistake and roll back changes 
$obj->rollBack();
// Database connection is now back in autocommit mode 
?>

Ref - http://hirengprajapati.blogspot.com/2012/09/php-pdo-transactions.html

==============================================================================

13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax

START TRANSACTION [WITH CONSISTENT SNAPSHOT]
BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

These statements provide control over use of transactions:

START TRANSACTION or BEGIN start a new transaction
COMMIT commits the current transaction, making its changes permanent
ROLLBACK rolls back the current transaction, canceling its changes
SET autocommit disables or enables the default autocommit mode for the current session

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent.

To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.

You can also begin a transaction like this:

START TRANSACTION WITH CONSISTENT SNAPSHOT;

The WITH CONSISTENT SNAPSHOT option starts a consistent read for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from anyInnoDB table. See Section 14.2.8.2, “Consistent Nonlocking Reads”. The WITH CONSISTENT SNAPSHOT option does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that permits consistent read (REPEATABLE READ or SERIALIZABLE).

Important

Many APIs used for writing MySQL client applications (such as JDBC) provide their own methods for starting transactions that can (and sometimes should) be used instead of sending a START TRANSACTION statement from the client. See Chapter 20, Connectors and APIs, or the documentation for your API, for more information.

To disable autocommit mode explicitly, use the following statement:

SET autocommit=0;

After disabling autocommit mode by setting the autocommit variable to zero, changes to transaction-safe tables (such as those for InnoDB, BDB, or NDBCLUSTER) are not made permanent immediately. You must use COMMIT to store your changes to disk or ROLLBACK to ignore the changes.

autocommit is a session variable and must be set for each session. To disable autocommit mode for each new connection, see the description of the autocommit system variable at Section 5.1.4, “Server System Variables”.

BEGIN and BEGIN WORK are supported as aliases of START TRANSACTION for initiating a transaction. START TRANSACTION is standard SQL syntax and is the recommended way to start an ad-hoc transaction.

The BEGIN statement differs from the use of the BEGIN keyword that starts a BEGIN ... END compound statement. The latter does not begin a transaction. See Section 13.6.1, “BEGIN ... END Compound-Statement Syntax”.

Note

Within all stored programs (stored procedures and functions, and triggers), the parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block. Begin a transaction in this context with START TRANSACTION instead.

Beginning with MySQL 5.0.3, the optional WORK keyword is supported for COMMIT and ROLLBACK, as are theCHAIN and RELEASE clauses. CHAIN and RELEASE can be used for additional control over transaction completion. The value of the completion_type system variable determines the default completion behavior. SeeSection 5.1.4, “Server System Variables”.

The AND CHAIN clause causes a new transaction to begin as soon as the current one ends, and the new transaction has the same isolation level as the just-terminated transaction. The RELEASE clause causes the server to disconnect the current client session after terminating the current transaction. Including the NO keyword suppresses CHAIN or RELEASE completion, which can be useful if the completion_type system variable is set to cause chaining or release completion by default.

Beginning a transaction causes any pending transaction to be committed. See Section 13.3.3, “Statements That Cause an Implicit Commit”, for more information.

Beginning a transaction also causes table locks acquired with LOCK TABLES to be released, as though you had executed UNLOCK TABLES. Beginning a transaction does not release a global read lock acquired with FLUSH TABLES WITH READ LOCK.

For best results, transactions should be performed using only tables managed by a single transaction-safe storage engine. Otherwise, the following problems can occur:

If you use tables from more than one transaction-safe storage engine (such as InnoDB and BDB), and the transaction isolation level is not SERIALIZABLE, it is possible that when one transaction commits, another ongoing transaction that uses the same tables will see only some of the changes made by the first transaction. That is, the atomicity of transactions is not guaranteed with mixed engines and inconsistencies can result. (If mixed-engine transactions are infrequent, you can use SET TRANSACTION ISOLATION LEVEL to set the isolation level toSERIALIZABLE on a per-transaction basis as necessary.)
If you use tables that are not transaction-safe within a transaction, changes to those tables are stored at once, regardless of the status of autocommit mode.
If you issue a ROLLBACK statement after updating a nontransactional table within a transaction, anER_WARNING_NOT_COMPLETE_ROLLBACK warning occurs. Changes to transaction-safe tables are rolled back, but not changes to nontransaction-safe tables.

Each transaction is stored in the binary log in one chunk, upon COMMIT. Transactions that are rolled back are not logged. (Exception: Modifications to nontransactional tables cannot be rolled back. If a transaction that is rolled back includes modifications to nontransactional tables, the entire transaction is logged with a ROLLBACK statement at the end to ensure that modifications to the nontransactional tables are replicated.) See Section 5.2.3, “The Binary Log”.

You can change the isolation level for transactions with the SET TRANSACTION statement. See Section 13.3.6, “SET TRANSACTION Syntax”.

Rolling back can be a slow operation that may occur implicitly without the user having explicitly asked for it (for example, when an error occurs). Because of this, SHOW PROCESSLIST displays Rolling back in the Statecolumn for the session, not only for explicit rollbacks performed with the ROLLBACK statement but also for implicit rollbacks.

Ref http://dev.mysql.com/doc/refman/5.0/en/commit.html

http://dev.mysql.com/doc/refman/5.6/en/commit.html