May 21, 2019

In a simple database transaction is actually not much different than what happened in the real world. Imagine your friends have a bank account and want to transfer some money to a friend. The transaction process is performed by different stages of data change. Through this document, I am interested in learning how to implement this simple case in the PHP programming language and some of its frameworks.

For example, suppose I have as much as $100 that I’ve saved to a bank account. I will transfer it to my friend, Sabrina, for $20 to pay the debt. Logically, my balance, which was originally $100, would be deducted from $20, and Sabrina’s balance would increase by $20. What happens if there is a problem in the middle of the transaction process? For example, the request was interrupted because the data change process only arrived at my balance discount? My balance will decrease by $20 while Sabrina’s balance does not increase by $20. Where is the $20? Of course, the kind of things you want to avoid. Database transactions can be applied to cases like this because if the processing of data or queries is not completed, you can restore data as before.

I have created a simple database for learning materials. Consists of tables of users and accounts with relationships a user has only one bank account. I deliberately do not make the transaction log table to be more concise and simple.

users table

table accounts


I created a special class with the name database to handle connections. Next in the index. PHP file I have two questions that I store in the variable $sender and $ receiver. It makes sense because later $sender query is used to reduce the sender’s balance, otherwise the $receiver adds the balance of the recipient based on the specified amount. In the code I wrote, all the query execution is placed in a try-catch block to handle errors. Executing queries is the same as using PDO normally, just call the start transaction () function before the query action starts, and the COMMIT () function when all queries are executed. To restore the return, call the rollback () function on the catch block. To test it, friends can try to change the second call so that an error occurs and the rollback function will run.


The CodeIgniter has a database transaction function that can be used directly. In the code I wrote, I only need to call Trans_start () function before the query execution starts and Trans_complete () function after all queries have been executed. Friends can use the Trans_status () function to find out the status transaction, whether it works or not. To test it, I connected the model with the default controller that came out when I first installed it.


Like CodeIgniter, a framework as large as Laravel must have a database transaction function. Friends can directly use the DB façade for this feature. In this case, I have a new controller and connected to the routing file first. Oiya, if there is only one method of a control file, friends can use the __invoke () method and call the controller name without the need to write the method name specifically in the routing file. To implement the Laravel, the friends must call the DB function:: Transaction () and execute the call in function parameter callback.

More or less this is the implementation of database transactions with a simple case example. The database I use for the above example is MySQL with the InnoDB Table engine. If there is input or correction, it will, of course, be very useful. Hopefully, we will learn something new today.