Phalcon DB块数据库，以便检查是否有可能插入数据

Question

我使用Phalcon，我在MySql中有一个数据库。我在数据库中有三个表:

• user: id, name, sold
• 公司:id，名称，成本
• transactions: id_company, id_user, cost

如果用户有足够的钱(售出)，则必须进行交易。所以我必须这样做:

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.

我的问题是:是否有一种方法可以阻止db，以便在没有db可能改变的情况下执行三个步骤?

我想这样做:

lock db
step 1
step 2
step 3
unlock db

但是我还没有找到一个解决方法。

Answer 1

我不确定它在Phalcon框架中是如何实现的，但是PDO扩展实现了在这里可以帮助的事务:

<?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