pixie
pixie copied to clipboard
Support manual transaction and make sure only one transaction active
upps.. sorry it seems duplicate with #102
but with different style.
try {
\QB::beginTransaction();
\QB::table('table')->insert(['column' => 'data']);
\QB::table('table_2')->insert(['column' => 'data']);
\QB::commit();
} catch (\Exception $e) {
\QB::rollback();
}
@swznd: wouldn't it be a better idea to use booleans instead of 0 and 1 to check if a transaction is active?
hmm, if using booleans, when we call commit
in another function/module it will disturb the previous transaction, because it just check true or false, without knowing how much transaction has been started.
For example, we meet the condition like this:
\QB::transaction(function($qb) {
$insertId = $qb->table('table')->insert(['column' => 'data']);
$anotherId = anotherFunction($insertId);
\QB::table('table_3')->where('another_id', $anotherId)->update(['column' => 'data']);
});
function anotherFunction($id)
{
try {
\QB::beginTransaction();
$insertId = \QB::table('table_another')->insert(['table_id' => $id, 'column' => 'data']);
\QB::commit();
} catch (\Exception $e) {
\QB::rollback();
throw $e;
}
return $insertId;
}
when commit
/ rollback
in anotherFunction
executed it will disturb the transaction outside, so the next query will not run inside transaction as expected.
for reference: https://github.com/laravel/framework/issues/1686 https://github.com/laravel/framework/issues/1823
I had similar problem. Its my solution below (pseudocode):
function firstQuery(){
$qb->table('table_1')->insert($data1).....
}
function secondQuery(){
$qb->table('table_2')->update($data2).....
}
function thirdQuery(){
$qb->table('table_3')->delete().....
}
Main class/function:
$inTransaction = $qb->pdo()->inTransaction();
try {
if (!$inTransaction) {
$qb->pdo()->beginTransaction();
}
firstQuery();
secondQuery();
thirdQuery();
if (!$inTransaction) {
$qb->pdo()->commit();
}
} catch (Exception $e) {
if (!$inTransaction) {
$qb->pdo()->rollBack();
}
}
Of course for all sub-function/methods and main function/method instance of $qb have to by the same - only one connection to DB. In each method we should check if we are in transaction, then we can stack and nest methods/functions.