CODEWITHSUNDEEP
phpmysqldatabaselaraveltransactions
khernik
khernik

Reputation: 2091

Two transactions at the same time and duplicated data

I have an API which is receiving two requests at exactly the same time. It results in two transactions being run at the same time as well, and so in duplicating the data. The pseudo code of my endpoint is:

public function myEndpoint(Request $request)
{
  $newOrderID = $request->get('order_id');

  DB::beginTransaction();

  try {
    $ordersCount = Order::where(['id' => $newOrderID])->count();
    if ($ordersCount === 0) {
      Order::create(['id' => $newOrderID]);
      
      $stat = Stat::where(['date' => Carbon::now()])->first();
      $stat->orders_count = $stat->orders_count + 1;
      $stat->save();
    }

    DB::commit();
  } catch (Exception $e) {
    DB::rollBack();
  }
}

So the endpoint is a transaction that:

The orders table does not have the unique key. It checks for the duplicates, and if there is an order ID already, it should not do anything.

The problem is - two transactions running at the same time, there is no duplicate yet, and the data gets duplicated, so I end up having two rows with the same order ID.

Setting the unique key will not work, because the statistics table will always get incorrectly incremented anyway.

Is there a nice solution to this problem?

Upvotes: 0

Views: 1372

Answers (2)

Shubham Srivastava
Shubham Srivastava

Reputation: 1877

You can make a hash and lock incoming requests if same order_id is already processing

  1. Make a Hash
  2. When request comes in check if order id in Hash
    1. If already in hash return duplicate request
  3. Check if order_id in DB
  4. Insert In DB if not exists
  5. Clear order_id from hash

Upvotes: 1

Rakib
Rakib

Reputation: 643

You can use mysql trigger to resolve the duplicate entry. Here is the sample trigger. You don't have to check if exist in php code.

DROP TRIGGER IF EXISTS order_transaction_before_insert;
DELIMITER $$

CREATE  TRIGGER order_transaction_before_insert
BEFORE INSERT ON order FOR EACH ROW
BEGIN

    DECLARE msg varchar(128);
    DECLARE foundCount INT;


    SET foundCount = (
        SELECT COUNT(*)
        FROM order
        WHERE
              order_id = NEW.order_id
    );

    IF( foundCount > 0) THEN
        SET msg = CONCAT('DuplicateEntryError: Trying to insert a duplicate value in order table for driver: ', NEW.order_id);
        SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT =  msg;
    END IF;

END; $$

DELIMITER ;

Upvotes: 0

Related Questions