Database transactions
Database transactions are used to maintain data integrity throughout the database. For example, let’s say you are doing a checkout function. You are creating an order in database, and then add products in that order in another table. Suppose your second query fails. Now you have orders in your database that does not have any product with it.
In order to protect it, we will use database transactions. Here’s how it works:
- First we will begin the transaction. This will tell the database that if anything fails, you must rollback the database to this state.
- Then we will wrap our entire function in a try catch block. If there is any exception, then we will call the rollBack() function and it will move the database to the state where beginTransaction() was called.
- Only when everything works fine and all functions are executed correctly, then we will call the commit() function. It will only save it in database only when commit() function is called.
Checkout
Following is an example in checkout feature in Laravel:
<?php
DB::beginTransaction();
try
{
$user = auth()->user();
$name = request()->name ?? "";
$phone = request()->phone ?? "";
$email = request()->email ?? "";
$address = request()->address ?? "";
$coupon_code = request()->coupon_code ?? "";
$payment_id = request()->payment_id ?? "";
$stripe = new \Stripe\StripeClient("sk_test_123");
$payment = $stripe->paymentIntents->retrieve(
$payment_id,
[]
);
if ($payment->status != "succeeded")
{
DB::rollBack();
return response()->json([
"status" => "error",
"message" => "Payment not successful."
]);
}
$cart = DB::table("user_cart")
->select("user_cart.*", "products.price", "products.stock")
->join("products", "products.id", "=", "user_cart.product_id")
->where("user_cart.user_id", "=", $user->id)
->get();
$total = 0;
$discount = 0;
foreach ($cart as $c)
{
if ($c->quantity > $c->stock)
{
DB::rollBack();
return response()->json([
"status" => "error",
"message" => "Product quantity exceeded."
]);
}
$total += ($c->price * $c->quantity);
}
$coupon_code_obj = DB::table("coupon_codes")
->where("code", "=", $coupon_code)
->first();
if ($coupon_code_obj != null)
{
$discount = ($total * $coupon_code_obj->discount) / 100;
$total -= $discount;
}
$order_id = DB::table("orders")
->insertGetId([
"user_id" => $user->id,
"total" => $total,
"discount" => $discount,
"name" => $name,
"phone" => $phone,
"email" => $email,
"address" => $address,
"created_at" => now()->utc(),
"updated_at" => now()->utc()
]);
foreach ($cart as $c)
{
DB::table("order_items")
->insertGetId([
"order_id" => $order_id,
"product_id" => $c->product_id,
"price" => $c->price,
"quantity" => $c->quantity,
"created_at" => now()->utc(),
"updated_at" => now()->utc()
]);
DB::table("products")
->where("id", "=", $c->product_id)
->increment("sold", $c->quantity);
DB::table("products")
->where("id", "=", $c->product_id)
->decrement("stock", $c->quantity);
}
Mail::raw("Your order has been received. Your total is PKR " . $total, function ($message, $email) {
$message->to($email)
->subject('New Order.');
});
DB::commit();
}
catch (\Exception $exp)
{
DB::rollBack();
return response()->json([
"status" => "error",
"message" => "Internal server error."
]);
}
- It begins the database transaction before performing any function.
- First we are making sure the payment from user card is valid (if you are receiving online payments).
- Then we are fetching user cart items and making sure that any product’s stock quantity must not exceed the one added in user’s cart.
- We are calculating the total.
- Validating and applying any coupon code if entered by user.
- Inserting the record in “orders” table.
- Saving all the products that were added in that order.
- Incrementing the sales and decrementing the stock quantity of each product.
- Sending the email to the user for order confirmation.
Let’s say if your code breaks at inserting in “order_items” step. Now you will have orders in your database that do not have any product in them, which is wrong. That’s why we are commiting the database only when all actions are performed correctly.
Payment transfer
Let’s say you have a feature in your website that allows user to transfer money from their account to other user’s account. In this case also, you have to perform multiple operations. Following is the example of basic payment transfer module. For the sake of simplicity, we are using virtual payments:
<?php
DB::beginTransaction();
try
{
$user = auth()->user();
$user_id = request()->user_id ?? 0;
$amount = request()->amount ?? 0;
$other_user = DB::table("users")
->where("id", "=", $user_id)
->first();
if ($other_user == null)
{
return response()->json([
"status" => "error",
"message" => "User not found."
]);
}
if ($amount > $user->balance)
{
return response()->json([
"status" => "error",
"message" => "Insufficient balance."
]);
}
DB::table("users")
->where("id", "=", $other_user->id)
->increment("balance", $amount);
DB::table("users")
->where("id", "=", $user->id)
->decrement("balance", $amount);
DB::table("payments")
->insertGetId([
"sender_id" => $user->id,
"receiver_id" => $other_user->id,
"amount" => $amount,
"created_at" => now()->utc(),
"updated_at" => now()->utc()
]);
Mail::raw("You have received a payment of PKR " . $amount . " from " . $user->name, function ($message, $other_user) {
$message->to($other_user->email)
->subject("Payment received.");
});
Mail::raw("You have made a payment of PKR " . $amount . " to " . $other_user->name, function ($message, $user) {
$message->to($user->email)
->subject("Payment sent.");
});
DB::commit();
}
catch (\Exception $exp)
{
DB::rollBack();
return response()->json([
"status" => "error",
"message" => "Internal server error."
]);
}
Here, we are:
- Making sure the recipient user exists.
- Then checking if user has sufficient balance.
- Crediting the amount in recipient’s account.
- Debiting from my account.
- Saving in database so both users can see their payment history.
- Sending email to both users.
In this case also, let’s say your code breaks at line #37, when the amount has been credited to receiver’s account. But has not been deducted from your account, and there is no payment record yet. That will be a lot of loss to the website owner.
So we are preventing such scenarios by commiting in database only when all the actions are performed correctly. If there is any error in any place, then the code will fall in catch block where we are rolling back the database to it’s previous state.
That’s how you can use database transactions to maintain data integrity and prevent data inconsistency in your database.
