MySQL selling database consistency: queued transactions?

I’m trying to create an application that has the ability to sell gift cards but I’m fearing the consistency of data, so let me explain what I mean:

I have 3 tables

transactions(id, created_date),

cards(id, name) and

vouchers(id, code, card_id, transaction_id).

The database contains many cards and vouchers and each voucher belongs to one card. The user will want to select a card to buy and choose a quantity.

So, the application will select vouchers according to the selected card and LIMIT according to quantity then creating a new transaction and adding the transaction_id into the selected vouchers to flag them as bought cards for this new transaction.

So, what I am afraid of is what if multiple users sent the same buying request for the same card at the exact same time, then will some data collision occur? and what is the best approach to fix this?

I am currently using MySQL 8 Community Edition with InnoDB engine for all tables.

What I am searching for is if I can create some kind of a queue that activates transactions one by one without collision even if it means that the users will have to wait until they get to their turn in the queue.

Thanks in advance

Answer

This is a job for MySQL transactions. With SQL you can do something like this.

START TRANSACTION;
SELECT id AS card_id FROM cards WHERE name = <<<<chosen name>>>> FOR UPDATE;
--do whatever it takes in SQL to complete the operation
COMMIT;

Multiple php processes, may try to do something with the same row of cards concurrently. Using START TRANSACTION combined with SELECT ... FOR UPDATE will prevent that by making the next process wait until the first process does COMMIT.

Internally, MySQL has a queue of waiting processes. As long as you do the COMMIT promptly after you do BEGIN TRANSACTION your users won’t notice this. This most often isn’t called “queuing” at the application level, but rather transaction consistency.

Laravel / eloquent makes this super easy. It does the START TRANSACTION and COMMIT for you, and offers lockForUpdate().

DB::transaction(function() {
    DB::table('cards')->where('name', '=', $chosenName)->lockForUpdate()->get();
    /* do whatever you need to do for your transaction */
});