Using closures in encapsulating PDO transactions does not work. Why?

Question

I'm currently having a problem in encapsulating PDO transactions for ease of use; after the transaction is executed, there is NO database changes that happened! My idea was to just provide the parameters and callable transaction needed to be executed in the PDO transaction. The protected executeTransaction method defines the encapsulation of the PDO transaction as shown below. While the client method is getNextWidWithLock which uses the executeTransaction method. I tried moving the transaction out of the closure and it worked! What could be the reason as to why it does not seem to be committing the changes to the database.

    class ParentRepository
    { ...
       /**
         * Executes a transaction with logging. 
         * @param  [type]   $data        data to be passed inside the callable.
         * @param  Callable $transaction function of the procedure
         * @return [type]                [description]
         */
        protected function executeTransaction(Array $data, Callable $transaction)
        {
            $returnVariable = false;

            try 
            {
                $this->mySqlConnect->beginTransaction();
                $returnVariable = $transaction($data);
                $this->mySqlConnect->commit();
            }
            catch(\PDOException $e)
            {
                $this->mySqlConnect->rollBack();
                // Log errors.
                $dateTime = date("Y-m-d H:i", time());
                self::log(sprintf("Error Transaction @ %s" . PHP_EOL, $dateTime));
                self::log(sprintf("[Error Message] => %s %s" . PHP_EOL, PHP_EOL, $e->getMessage()));
                self::log(sprintf("[File] => %s" . PHP_EOL, $e->getFile()));
                self::log(sprintf("[Line Number] => %s" . PHP_EOL, $e->getLine()));
                self::log(sprintf("[Back Trace] => %s %s" . PHP_EOL, PHP_EOL, $e->getTraceAsString()));
                self::log("______________________________________" . PHP_EOL);
                throw $e;
            }

            return $returnVariable;
        }

    /**
         * Reserves wid in the given table with write-locking. 
         * @param  [type]  $tableName     [description]
         * @param  [type]  $branchId      [description]
         * @param  integer $reservedSlots [description]
         * @return array                  returns an array of wids. 
         * The last element must be used in an UPDATE statement while 
         * the rest must be used in an INSERT statement.  
         */
        protected function getNextWidWithLock($tableName, $branchId, $reservedSlots = 1)
        {
            if ($reservedSlots === 0)
                return [];

            $this->executeTransaction(
                [
                    'tableName' => $tableName, 
                    'branchId' => $branchId, 
                    'reservedSlots' => $reservedSlots
                ], 
                function($params)
                {
                    extract($params);

                    $minimumWid = $branchId . "0000000";
                    $maximumWid = $branchId . "9999999";

                    $preparedStatements = array(
                        $this->mySqlConnect->prepare("SET @wid_d = 0"),
                        $this->mySqlConnect->prepare("SELECT COALESCE(MAX(`wid`), $minimumWid) INTO @wid_d FROM $tableName
                                    WHERE `wid` >= $minimumWid AND `wid` <= $maximumWid FOR UPDATE"),
                        $this->mySqlConnect->prepare("INSERT INTO $tableName(`wid`)
                                 VALUES ( IF(@wid_d = 0, $minimumWid + 1 , @wid_d + $reservedSlots) )")
                    );


                    foreach ($preparedStatements as $statement)
                        $statement->execute();

                    $result = $this->mySqlConnect->prepare("SELECT `wid` FROM $tableName WHERE id = " . $this->mySqlConnect->lastInsertId());
                    $result->execute();
                    $end = $result->fetchColumn();

                    $statement = $this->mySqlConnect->prepare("SELECT IF(@wid_d = 0, $minimumWid + 1 , @wid_d + 1)");
                    $statement->execute();
                    $begin = $statement->fetchColumn();

                    return range($begin, $end);
                }
            ); 
        }


    }

Nick · Answer 1 · 2015-11-14T13:12:01.050

You using $this in closure, but it is not supporting from PHP 5.3

You can write something like this:

    protected function getNextWidWithLock($tableName, $branchId, $reservedSlots = 1)
    {
        if ($reservedSlots === 0)
            return [];

        $self = $this;
        $this->executeTransaction(
            [
                'tableName' => $tableName, 
                'branchId' => $branchId, 
                'reservedSlots' => $reservedSlots
            ], 
            function($params) use ($self, $tableName, $branchId, $reservedSlots)
            {
                extract($params);

                $minimumWid = $branchId . "0000000";
                $maximumWid = $branchId . "9999999";

                $preparedStatements = array(
                    $self->mySqlConnect->prepare("SET @wid_d = 0"),
                    $self->mySqlConnect->prepare("SELECT COALESCE(MAX(`wid`), $minimumWid) INTO @wid_d FROM $tableName
                                WHERE `wid` >= $minimumWid AND `wid` <= $maximumWid FOR UPDATE"),
                    $self->mySqlConnect->prepare("INSERT INTO $tableName(`wid`)
                             VALUES ( IF(@wid_d = 0, $minimumWid + 1 , @wid_d + $reservedSlots) )")
                );


                foreach ($preparedStatements as $statement)
                    $statement->execute();

                $result = $self->mySqlConnect->prepare("SELECT `wid` FROM $tableName WHERE id = " . $self->mySqlConnect->lastInsertId());
                $result->execute();
                $end = $result->fetchColumn();

                $statement = $self->mySqlConnect->prepare("SELECT IF(@wid_d = 0, $minimumWid + 1 , @wid_d + 1)");
                $statement->execute();
                $begin = $statement->fetchColumn();

                return range($begin, $end);
            }
        ); 
    }


}

So what should I do? Should I pass the PDO object to the closure as a parameter? — Xegara, Nov 14 '15 at 12:31
It seems that I just forgot to return a value for getNextWidWithLock method. I tried using this, and it still works. Isn't it supposed not to work since I'm using PHP 5.6.3? — Xegara, Nov 14 '15 at 13:00
I forget about $tableName, $branchId, $reservedSlots in use(). Edited. — Nick, Nov 14 '15 at 13:12

Using closures in encapsulating PDO transactions does not work. Why?

1 Answers1