In smaller or mid sized web applications it may not be required to use transactions but whenever there arises a condition where the current database operation depends on the success or failure of a previously carried out database operation, Transactions can be used.

For example, suppose you are redeeming 1000 ponits in a battle game from your account to add a new weapon to your profile. It can be simply achieved by 2 SQL queries, one for deducting 1000 points from your account and another for adding the new weapon to your profile or to the table where players’ weapon are stored. However imagine a situation when the 2nd query failed to execute for some reason, then what happens? Well, you lose those points without getting the automatic fancy new gun you wanted. You can say, we will code it like that if the 2nd query fails, we again add those points that were deducted by the 1st query. Although you can manage this in the coding level, but why those extra checks and coding when the database can do it for you by itself? Yes, that’s what Transactions do.

Transactions treat multiple queries as a single unit and if any one of those fails, it reverts back the previous state of the database before carrying out those queries. Hence, in the above scenario, if the queries had been executed as part of a transaction and the 2nd query had failed, it would have rolled back to the initial state before executing those queries and you wouldn’t have lost any points. Sounds cool? Ok, now let’s see how we can use transactions. It’s very simple.

1st you issue the command to let mysql know that you are going to carry out a transaction. The command for this is:

start_mysql_transaction

After that 1st command, enter your multiple queries as you normally do.

run_transaction_queries

Now that you have executed the required queries, you have 2 options complete this transaction process. Either you can roll back to the initial state by issuing the command ROLLBACK or commit those changes to the database by issuing the command COMMIT. One thing to note here is that, although your previous queries have been executed, the changes will not be reflected until the transaction is complete.  If you go to your game profile and check, there is no new weapon available yet, but once you COMMIT the transaction the database will get updated with the new values. This process actually maintains transaction isolation and helps data corruption due to other database operations that might be in execution. Ok, now let’s COMMIT the transaction by the following command.

mysql_transaction_complete

The below screenshot shows result from another terminal i.e. another mysql connection instance before committing and after the commit.

before_and_after_transaction

This completes the transaction and now you have a rough idea about using transactions in mySQL. The above example is not much data critical one, but in application where data accuracy is highly desired such as trading application or bank applications, you should always consider using transactions.

One thing you need to make note of is that:

MySQL has different storage engines, but InnoDB supports this transaction feature, so you must make sure the tables you are using are InnoDB ones, else once data is written to the tables it will not be reverted back by using commands like ROLLBACK or when any one of the query from the transaction fails.

So now you know the basic commands for mysql transaction, so how do you execute it in php? Well, it’s damn simple. Just run each query separately using the mysql_query() method. However as mysql_query() is deprecating as of PHP 5.5 and also instead of writing each queries individually it would be cool to execute multiple queries at once, I suggest using the multi_query() function from mysqli PHP extension. So using the multi_query() function the transaction query code in PHP will be something like below:

MySQL has been supporting Transactions since version 4 and there are also more cool features like SAVEPOINTS which lets you rollback to any particular save point during the transaction not just only to the initial state before the transaction started. Overall transactions are a cool feature of MySQL that you should be making use of in your web application.  Let me know your views or queries in comments.