Phalcon DB block database in order to check it is possible to insert data

Question

I'm using Phalcon and I have a database in MySql. I have three tables in the database:

• user: id, name, sold
• company: id, name, cost
• transactions: id_company, id_user, cost

An user has to do a transaction if has enough money (sold). So I have to do it:

Step 1:
retrieve the sold of the user:
select sold
from user
where id='Charlie'

Step 2:
retrive the cost from the company:
select cost
from company
where id='Tango'

Step 3:
to check if the user has enough money:
if (sold-cost >= 0)
create the transaction
else
do not create the transaction.

My question is: Is there a way in order to block the db in order to do the three steps without the db could change?

I would like do this:

lock db
step 1
step 2
step 3
unlock db

But I have not found a solution for it.

Answer 1

I'm not sure how it implemented in Phalcon framework, but PDO extension implements transactions that can be helpful here:

<?php
$pdo->beginTransaction();
$stmt = $pdo->prepare('select sold from user where id= ?');
$stmt->execute(['Charlie']);
$row = $stmt->fetch(PDO::FETCH_ASSOC);

$sold = $row['sold'];

$stmt = $pdo->prepare('select cost from company where id= ?');
$stmt->execute(['Tango']);
$row = $stmt->fetch(PDO::FETCH_ASSOC);

$cost = $row['cost'];

printf("Sold: %d, Cost: %d", $sold, $cost);

if ($sold >= $cost) {
    //reduse user sold
    $stmt = $pdo->prepare('update user set sold = sold - ? where id= ?;');
    $stmt->execute([$cost, 'Charlie']);

    // write transaction
    $stmt = $pdo->prepare('insert into transactions values (?, ?, ?);');
    $stmt->execute(['Charlie', 'Tango', $cost]);
    $pdo->commit();
} else {
    $pdo->rollBack();
}


$stmt = $pdo->prepare('select * from transactions');
$stmt->execute();
$transactions = $stmt->fetchAll(PDO::FETCH_ASSOC);

print_r($transactions);

PHP PDO fiddle here