Transactions with PDO::ATTR_EMULATE_PREPARES = 0

korvinko commented 7 years ago

What steps will reproduce the problem?

db.conf:

return [
    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=127.0.0.1;dbname=db',
    'username' => 'username',
    'password' => 'pass',
    'charset' => 'utf8',
    'tablePrefix' => '',
    'attributes' => [
        \PDO::ATTR_EMULATE_PREPARES => false,
        \PDO::ATTR_STRINGIFY_FETCHES => false,
    ],
];

Code in controller:

        $boilerTest1 = new Boiler();
        $boilerTest2 = new Boiler();
        $boilerTest3 = new Boiler();
        $boilerTest4 = new Boiler();
        $boilerTest1->title = 'test1';
        $boilerTest2->title = 'test2';
        $boilerTest3->title = 'test3';
        $boilerTest4->title = 'test4';

        $outerTransaction = \Yii::$app->db->beginTransaction();
        $boilerTest1->save();

        $innerTransaction = \Yii::$app->db->beginTransaction();
        $boilerTest2->save();
        $innerTransaction->rollBack();

        $innerTransaction2 = \Yii::$app->db->beginTransaction();
        $boilerTest3->save();
        $innerTransaction2->rollBack();

        $boilerTest4->save();
        $outerTransaction->commit();

Where Boiler is some model.

What is the expected result?

Code should finish without exception

What do you get instead?

In this line rise exception: $innerTransaction = \Yii::$app->db->beginTransaction();

Exception message: "SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.\nThe SQL being executed was: SAVEPOINT LEVEL1"

Additional info

Q	A
Yii version	2.0.12
PHP version	7.1.8 NTS
Operating system	Linux 4.12.8-2

shijiaoliang commented 6 years ago

I also met the same problem, I changed the 【yiisoft/yii2/db/Transaction.php】 this file， work well

======================

<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

namespace yii\db;

use Yii;
use yii\base\InvalidConfigException;
use yii\db\mssql\PDO;

/**
 * Transaction represents a DB transaction.
 *
 * It is usually created by calling [[Connection::beginTransaction()]].
 *
 * The following code is a typical example of using transactions (note that some
 * DBMS may not support transactions):
 *
 * ```php
 * $transaction = $connection->beginTransaction();
 * try {
 *     $connection->createCommand($sql1)->execute();
 *     $connection->createCommand($sql2)->execute();
 *     //.... other SQL executions
 *     $transaction->commit();
 * } catch (\Exception $e) {
 *     $transaction->rollBack();
 *     throw $e;
 * } catch (\Throwable $e) {
 *     $transaction->rollBack();
 *     throw $e;
 * }
 * ```
 *
 * > Note: in the above code we have two catch-blocks for compatibility
 * > with PHP 5.x and PHP 7.x. `\Exception` implements the [`\Throwable` interface](http://php.net/manual/en/class.throwable.php)
 * > since PHP 7.0, so you can skip the part with `\Exception` if your app uses only PHP 7.0 and higher.
 *
 * @property bool $isActive Whether this transaction is active. Only an active transaction can [[commit()]] or
 * [[rollBack()]]. This property is read-only.
 * @property string $isolationLevel The transaction isolation level to use for this transaction. This can be
 * one of [[READ_UNCOMMITTED]], [[READ_COMMITTED]], [[REPEATABLE_READ]] and [[SERIALIZABLE]] but also a string
 * containing DBMS specific syntax to be used after `SET TRANSACTION ISOLATION LEVEL`. This property is
 * write-only.
 * @property int $level The current nesting level of the transaction. This property is read-only.
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @since 2.0
 */
class BaseTransaction extends \yii\base\BaseObject
{
    /**
     * A constant representing the transaction isolation level `READ UNCOMMITTED`.
     * @see http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Isolation_levels
     */
    const READ_UNCOMMITTED = 'READ UNCOMMITTED';
    /**
     * A constant representing the transaction isolation level `READ COMMITTED`.
     * @see http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Isolation_levels
     */
    const READ_COMMITTED = 'READ COMMITTED';
    /**
     * A constant representing the transaction isolation level `REPEATABLE READ`.
     * @see http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Isolation_levels
     */
    const REPEATABLE_READ = 'REPEATABLE READ';
    /**
     * A constant representing the transaction isolation level `SERIALIZABLE`.
     * @see http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Isolation_levels
     */
    const SERIALIZABLE = 'SERIALIZABLE';

    /**
     * @var Connection the database connection that this transaction is associated with.
     */
    public $db;

    /**
     * @var int the nesting level of the transaction. 0 means the outermost level.
     */
    private $_level = 0;

    /**
     * Returns a value indicating whether this transaction is active.
     * @return bool whether this transaction is active. Only an active transaction
     * can [[commit()]] or [[rollBack()]].
     */
    public function getIsActive()
    {
        return $this->_level > 0 && $this->db && $this->db->isActive;
    }

    /**
     * Begins a transaction.
     * @param string|null $isolationLevel The [isolation level][] to use for this transaction.
     * This can be one of [[READ_UNCOMMITTED]], [[READ_COMMITTED]], [[REPEATABLE_READ]] and [[SERIALIZABLE]] but
     * also a string containing DBMS specific syntax to be used after `SET TRANSACTION ISOLATION LEVEL`.
     * If not specified (`null`) the isolation level will not be set explicitly and the DBMS default will be used.
     *
     * > Note: This setting does not work for PostgreSQL, where setting the isolation level before the transaction
     * has no effect. You have to call [[setIsolationLevel()]] in this case after the transaction has started.
     *
     * > Note: Some DBMS allow setting of the isolation level only for the whole connection so subsequent transactions
     * may get the same isolation level even if you did not specify any. When using this feature
     * you may need to set the isolation level for all transactions explicitly to avoid conflicting settings.
     * At the time of this writing affected DBMS are MSSQL and SQLite.
     *
     * [isolation level]: http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Isolation_levels
     * @throws InvalidConfigException if [[db]] is `null`.
     */
    public function begin($isolationLevel = null)
    {
        if ($this->db === null) {
            throw new InvalidConfigException('Transaction::db must be set.');
        }
        $this->db->open();

        if ($this->_level === 0) {
            if ($isolationLevel !== null) {
                $this->db->getSchema()->setTransactionIsolationLevel($isolationLevel);
            }
            Yii::debug('Begin transaction' . ($isolationLevel ? ' with isolation level ' . $isolationLevel : ''), __METHOD__);

            $this->db->trigger(Connection::EVENT_BEGIN_TRANSACTION);
            $this->db->pdo->beginTransaction();
            $this->_level = 1;

            return;
        }

        $schema = $this->db->getSchema();
        if ($schema->supportsSavepoint()) {
            Yii::debug('Set savepoint ' . $this->_level, __METHOD__);

            $this->db->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
            $schema->createSavepoint('LEVEL' . $this->_level);
            $this->db->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        } else {
            Yii::info('Transaction not started: nested transaction not supported', __METHOD__);
        }
        $this->_level++;
    }

    /**
     * Commits a transaction.
     * @throws Exception if the transaction is not active
     */
    public function commit()
    {
        if (!$this->getIsActive()) {
            throw new Exception('Failed to commit transaction: transaction was inactive.');
        }

        $this->_level--;
        if ($this->_level === 0) {
            Yii::debug('Commit transaction', __METHOD__);
            $this->db->pdo->commit();
            $this->db->trigger(Connection::EVENT_COMMIT_TRANSACTION);
            return;
        }

        $schema = $this->db->getSchema();
        if ($schema->supportsSavepoint()) {
            Yii::debug('Release savepoint ' . $this->_level, __METHOD__);

            $this->db->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
            $schema->releaseSavepoint('LEVEL' . $this->_level);
            $this->db->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        } else {
            Yii::info('Transaction not committed: nested transaction not supported', __METHOD__);
        }
    }

    /**
     * Rolls back a transaction.
     * @throws Exception if the transaction is not active
     */
    public function rollBack()
    {
        if (!$this->getIsActive()) {
            // do nothing if transaction is not active: this could be the transaction is committed
            // but the event handler to "commitTransaction" throw an exception
            return;
        }

        $this->_level--;
        if ($this->_level === 0) {
            Yii::debug('Roll back transaction', __METHOD__);
            $this->db->pdo->rollBack();
            $this->db->trigger(Connection::EVENT_ROLLBACK_TRANSACTION);
            return;
        }

        $schema = $this->db->getSchema();
        if ($schema->supportsSavepoint()) {
            Yii::debug('Roll back to savepoint ' . $this->_level, __METHOD__);

            $this->db->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
            $schema->rollBackSavepoint('LEVEL' . $this->_level);
            $this->db->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        } else {
            Yii::info('Transaction not rolled back: nested transaction not supported', __METHOD__);
            // throw an exception to fail the outer transaction
            throw new Exception('Roll back failed: nested transaction not supported.');
        }
    }

    /**
     * Sets the transaction isolation level for this transaction.
     *
     * This method can be used to set the isolation level while the transaction is already active.
     * However this is not supported by all DBMS so you might rather specify the isolation level directly
     * when calling [[begin()]].
     * @param string $level The transaction isolation level to use for this transaction.
     * This can be one of [[READ_UNCOMMITTED]], [[READ_COMMITTED]], [[REPEATABLE_READ]] and [[SERIALIZABLE]] but
     * also a string containing DBMS specific syntax to be used after `SET TRANSACTION ISOLATION LEVEL`.
     * @throws Exception if the transaction is not active
     * @see http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Isolation_levels
     */
    public function setIsolationLevel($level)
    {
        if (!$this->getIsActive()) {
            throw new Exception('Failed to set isolation level: transaction was inactive.');
        }
        Yii::debug('Setting transaction isolation level to ' . $level, __METHOD__);
        $this->db->getSchema()->setTransactionIsolationLevel($level);
    }

    /**
     * @return int The current nesting level of the transaction.
     * @since 2.0.8
     */
    public function getLevel()
    {
        return $this->_level;
    }
}

======================

korvinko commented 6 years ago

Thanks for your solution, but could you please correctly cover code to make it more readable?

shijiaoliang commented 6 years ago

https://note.youdao.com/share/?id=4741bb67a66f137b899530fa65762518&type=note#/

machour commented 5 years ago

@shijiaoliang I updated your comment to properly format the PHP code. Would you mind opening a PR against current master so that we can review it?

yiisoft / yii2