代码之家  ›  专栏  ›  技术社区  ›  tonix

PHP PDO MySQL以及它如何真正处理MySQL事务?

locking transactions pdo mysql php
  •  5
  • tonix  · 技术社区  · 7 年前

    我正在努力克服它,但我无法理解使用PDO和MySQL在PHP中处理事务背后的逻辑。

    我知道这个问题会很长,但我认为这是值得的。

    考虑到我读了很多关于MySQL事务、服务器如何处理它们、它们与锁和其他隐式提交语句的关系等方面的内容,不仅在这里如此,在MySQL和PHP手册中也有:

    根据此代码:

    架构: 

    CREATE TABLE table_name (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  table_col VARCHAR(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `another_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `another_col` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    test1.php (带 PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0) ): 

    <?php

// PDO
define('DB_HOST', 'localhost');
define('DB_USER', 'user');
define('DB_PASS', 'password');
define('DB_NAME', 'db_name');

/**
 * Uses `$this->pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,0);`
 */
class Database {

    private $host = DB_HOST;
    private $user = DB_USER;
    private $pass = DB_PASS;
    private $dbname = DB_NAME;

    private $pdo;

    public $error;

    private $stmt;


    public function __construct($host=NULL,$user=NULL,$pass=NULL,$dbname=NULL) {

        if ($host!==NULL)
            $this->host=$host;

        if ($user!==NULL)
            $this->user=$user;

        if ($pass!==NULL)
            $this->pass=$pass;

        if ($dbname!==NULL)
            $this->dbname=$dbname;

        // Set DSN
        $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;

        // Set options
        $options = array(
            PDO::ATTR_PERSISTENT    => false,
            PDO::ATTR_ERRMODE       => PDO::ERRMODE_EXCEPTION
        );

        // Create a new PDO instanace
        $this->pdo = new PDO($dsn, $this->user, $this->pass, $options);
        $this->pdo->exec("SET NAMES 'utf8'");

    }

    public function cursorClose() {
        $this->stmt->closeCursor();
    }

    public function close() {
        $this->pdo = null;
        $this->stmt = null;
        return true;
    }

    public function beginTransaction() {
        $this->pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,0);
        return $this->pdo->beginTransaction();
    }

    public function commit() {
        $ok = $this->pdo->commit();
        $this->pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,1);
        return $ok;
    }

    public function rollback() {
        $ok = $this->pdo->rollback();
        $this->pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,1);
        return $ok;
    }

    public function bind($param, $value, $type = null){
        if (is_null($type)) {
            switch (true) {
                case is_int($value):
                    $type = PDO::PARAM_INT;
                    break;
                case is_bool($value):
                    $type = PDO::PARAM_BOOL;
                    break;
                case is_null($value):
                    $type = PDO::PARAM_NULL;
                    break;
                default:
                    $type = PDO::PARAM_STR;
            }
        }
        $this->stmt->bindValue($param, $value, $type);
    }

    public function runquery() {
        $this->stmt->execute();
    }

    public function execute($nameValuePairArray = NULL) {
        try {   
            if (is_array($nameValuePairArray) && !empty($nameValuePairArray)) 
                return $this->stmt->execute($nameValuePairArray);
            else
                return $this->stmt->execute();
        } 
        catch(PDOException $e) {
            $this->error = $e->getMessage();
        }   
        return FALSE;
    }

    public function lastInsertId() {
        return $this->pdo->lastInsertId();
    }

    public function insert($table, $data) {

        if (!empty($data)){

            $fields = "";

            $values = "";

            foreach($data as $field => $value) {

                if ($fields==""){
                    $fields = "$field";
                    $values = ":$field";
                }
                else {
                    $fields .= ",$field";
                    $values .= ",:$field";
                }
            }

            $query = "INSERT INTO $table ($fields) VALUES ($values) ";

            $this->query($query);

            foreach($data as $field => $value){
                $this->bind(":$field",$value);
            }

            if ($this->execute()===FALSE)
                return FALSE;
            else
                return $this->lastInsertId();   
        }

        $this->error = "No fields during insert";

        return FALSE;
    }

    public function query($query) {
        $this->stmt = $this->pdo->prepare($query);
    }

    public function setBuffered($isBuffered=false){
        $this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, $isBuffered);
    }

    public function lockTables($tables){
        $query = "LOCK TABLES ";
        foreach($tables as $table=>$lockType){
            $query .= "{$table} {$lockType}, ";
        }
        $query = substr($query,0, strlen($query)-2);
        $this->query($query);
        return $this->execute();
    }

    public function unlockTables(){
        $query = "UNLOCK TABLES";
        $this->query($query);
        return $this->execute();
    }
}

$db = NULL;
try {
    $db = new Database();
    $db->beginTransaction();

    // If I call `LOCK TABLES` here... No implicit commit. Why?
    // Does `$this->pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,0);` prevent it?
    $db->lockTables(array('another_table' => 'WRITE'));

    $db->insert('another_table', array('another_col' => 'TEST1_ANOTHER_TABLE'));

    $db->unlockTables();


    // If I insert a row, other MySQL clients do not see it. Why?
    // I called `LOCK TABLES` above and as the MySQL manual says:
    // 
    //      LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.
    //
    $db->insert('table_name', array('table_col' => 'TEST1_TABLE_NAME'));

    //...
    // If I rollback for some reason, everything rolls back, but shouldn't the transaction
    // be already committed with the initial `LOCK TABLES`?
    // So I should expect to get a PDOException like "There's no active transaction" or something similar, shouldn't I?
    //$db->rollback();

    // If I commit instead of the above `$db->rollback()` line, everything is committed, but only now other clients see the new row in `table_name`,
    // not straightforward as soon I called `$db->insert()`, whereas I guess they should have seen the change
    // even before the following line because I am using `LOCK TABLES` before (see `test2.php`).
    $db->commit();
}
catch (PDOException $e) {
    echo $e->getMessage();
}

if (!is_null($db)) {
    $db->close();
}

    test2.php (数据库中没有 PDO::setAttribute(PDO::ATTR\u AUTOCOMMIT,0) 行(已注释掉)): 

    <?php

// PDO
define('DB_HOST', 'localhost');
define('DB_USER', 'user');
define('DB_PASS', 'password');
define('DB_NAME', 'db_name');

/**
 * Does not use `$this->pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,0);`
 */
class Database {

    private $host = DB_HOST;
    private $user = DB_USER;
    private $pass = DB_PASS;
    private $dbname = DB_NAME;

    private $pdo;

    public $error;

    private $stmt;


    public function __construct($host=NULL,$user=NULL,$pass=NULL,$dbname=NULL) {

        if ($host!==NULL)
            $this->host=$host;

        if ($user!==NULL)
            $this->user=$user;

        if ($pass!==NULL)
            $this->pass=$pass;

        if ($dbname!==NULL)
            $this->dbname=$dbname;

        // Set DSN
        $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;

        // Set options
        $options = array(
            PDO::ATTR_PERSISTENT    => false,
            PDO::ATTR_ERRMODE       => PDO::ERRMODE_EXCEPTION
        );

        // Create a new PDO instanace
        $this->pdo = new PDO($dsn, $this->user, $this->pass, $options);
        $this->pdo->exec("SET NAMES 'utf8'");

    }

    public function cursorClose() {
        $this->stmt->closeCursor();
    }

    public function close() {
        $this->pdo = null;
        $this->stmt = null;
        return true;
    }

    public function beginTransaction() {
        //$this->pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,0);
        return $this->pdo->beginTransaction();
    }

    public function commit() {
        $ok = $this->pdo->commit();
        //$this->pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,1);
        return $ok;
    }

    public function rollback() {
        $ok = $this->pdo->rollback();
        //$this->pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,1);
        return $ok;
    }

    public function bind($param, $value, $type = null){
        if (is_null($type)) {
            switch (true) {
                case is_int($value):
                    $type = PDO::PARAM_INT;
                    break;
                case is_bool($value):
                    $type = PDO::PARAM_BOOL;
                    break;
                case is_null($value):
                    $type = PDO::PARAM_NULL;
                    break;
                default:
                    $type = PDO::PARAM_STR;
            }
        }
        $this->stmt->bindValue($param, $value, $type);
    }

    public function runquery() {
        $this->stmt->execute();
    }

    public function execute($nameValuePairArray = NULL) {
        try {   
            if (is_array($nameValuePairArray) && !empty($nameValuePairArray)) 
                return $this->stmt->execute($nameValuePairArray);
            else
                return $this->stmt->execute();
        } 
        catch(PDOException $e) {
            $this->error = $e->getMessage();
        }   
        return FALSE;
    }

    public function lastInsertId() {
        return $this->pdo->lastInsertId();
    }

    public function insert($table, $data) {

        if (!empty($data)){

            $fields = "";

            $values = "";

            foreach($data as $field => $value) {

                if ($fields==""){
                    $fields = "$field";
                    $values = ":$field";
                }
                else {
                    $fields .= ",$field";
                    $values .= ",:$field";
                }
            }

            $query = "INSERT INTO $table ($fields) VALUES ($values) ";

            $this->query($query);

            foreach($data as $field => $value){
                $this->bind(":$field",$value);
            }

            if ($this->execute()===FALSE)
                return FALSE;
            else
                return $this->lastInsertId();   
        }

        $this->error = "No fields during insert";

        return FALSE;
    }

    public function query($query) {
        $this->stmt = $this->pdo->prepare($query);
    }

    public function setBuffered($isBuffered=false){
        $this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, $isBuffered);
    }

    public function lockTables($tables){
        $query = "LOCK TABLES ";
        foreach($tables as $table=>$lockType){
            $query .= "{$table} {$lockType}, ";
        }
        $query = substr($query,0, strlen($query)-2);
        $this->query($query);
        return $this->execute();
    }

    public function unlockTables(){
        $query = "UNLOCK TABLES";
        $this->query($query);
        return $this->execute();
    }
}

$db = NULL;
try {
    $db = new Database();
    $db->beginTransaction();

    // If I call `LOCK TABLES` here... There's an implicit commit.
    $db->lockTables(array('another_table' => 'WRITE'));

    $db->insert('another_table', array('another_col' => 'TEST2_ANOTHER_TABLE'));

    $db->unlockTables();


    // If I insert a row, other MySQL clients see it straightforward (no need to reach `$db->commit()`).
    // This is coherent with the MySQL manual:
    // 
    //      LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.
    //
    $db->insert('table_name', array('table_col' => 'TEST2_TABLE_NAME'));

    //...
    // If I rollback for some reason, the row does not rollback, as the transaction
    // was already committed with the initial `LOCK TABLES` statement above.
    // 
    // I cannot rollback the insert into table `table_name`
    // 
    // So I should expect to get a PDOException like "There's no active transaction" or something similar, shouldn't I?
    $db->rollback();

    // If I commit instead of the above `$db->rollback()` line, I guess nothing happens, because the transaction
    // was already committed and as I said above, and clients already saw the changes before this line was reached.
    // Again, this is coherent with the MySQL statement:
    //
    //       LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.
    //
    //$db->commit();
}
catch (PDOException $e) {
    echo $e->getMessage();
}

if (!is_null($db)) {
    $db->close();
}

    我仍然有以下疑问和未回答的问题:

    • 使用 InnoDB ,两者之间有区别吗 PDO::beginTransaction() 和 PDO::setAttribute(PDO::ATTR\u AUTOCOMMIT,0) 当我们使用 PDO 在PHP和/或MySQL中使用普通MySQL语句 SET AUTOCOMMIT = 0; 和 START TRANSACTION; ? 如果是,是什么?

      如果您查看我的PHP示例 Database::beginTransaction() 包装器方法我使用两者 PDO::beginTransaction() 和 PDO::setAttribute(PDO::ATTR\u AUTOCOMMIT,0) 在文件中 test1.php 并且不要使用 PDO::setAttribute(PDO::ATTR\u AUTOCOMMIT,0) 在文件中 test2.php . 我发现当我使用 PDO::setAttribute(PDO::ATTR\u AUTOCOMMIT,0) :

      • 具有 PDO::setAttribute(PDO::ATTR\u AUTOCOMMIT,0) 线路输入 Database ( test1.php ),内部a 与的交易 LOCK TABLES 陈述 锁定表格 没有 似乎隐式提交事务 ,因为如果我连接 对于另一个客户端,在代码到达 $db->commit(); 行,而MySQL 手册上说:

        锁定表不是事务安全的,在尝试锁定表之前隐式提交任何活动事务。

        因此,我们可以这样说吗 PDO::setAttribute(PDO::ATTR\u AUTOCOMMIT,0) (在MySQL上是 设置自动提交=0; )事务不是由隐式提交的 声明如下 锁定表格 ? 那么我会说 MySQL手册和PHP PDO实现之间的不一致 (我不是在抱怨,我只是想理解);

      • 没有 PDO::setAttribute(PDO::ATTR\u AUTOCOMMIT,0) 线路输入 数据库 ( test2.php ),代码的行为似乎与MySQL的一致 手册 LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables. :一旦到达 锁定表格 查询时,有一个隐式提交,所以在该行之后 $db->insert('table_name', array('table_col' => 'TEST2_TABLE_NAME')); 其他客户端甚至在到达之前就可以看到新插入的行 $db->提交(); ;

    我刚才描述的以下行为的解释是什么?当我们使用PHP时,事务是如何工作的 PDO公司 并且有 implicit-commit 我们交易中的报表?

    我的PHP版本是 7.0.22 ,MySQL版本为 5.7.20 .

    谢谢你的关注。

    1 回复  |  直到 7 年前
        1
  •  1
  •   Bill Karwin    7 年前

    https://dev.mysql.com/doc/refman/5.7/en/innodb-autocommit-commit-rollback.html 说:

    如果在设置自动提交=0的会话中禁用自动提交模式,则该会话始终有一个打开的事务。COMMIT或ROLLBACK语句结束当前事务,并启动新事务。

    因此,当您在会话中设置autocommit=0时(称为会话1),这会隐式地 打开 事务,并使其无限期打开。

    默认事务隔离级别为REPEATABLE-READ。因此,在会话1显式提交或回滚之前,您的会话将不会看到来自其他会话工作的已提交更改的刷新视图。

    在另一个会话2中锁定表 做 导致隐式提交,但会话1看不到结果,因为它仍然只能看到数据的独立视图,因为它有自己的事务快照。

    推荐文章
    Jacco  ·  未能格式化我的日期以在php中正确工作
    1 年前
    darshita_baldha  ·  如何在Laravel组件中传递数组变量?
    1 年前
    nitroflox  ·  在php中删除带有安全和httponly选项的cookie值得吗?
    1 年前
    Community wiki  ·  在OOP中,依赖项是否应该包含对其父项的引用?
    1 年前
    Arno van Oordt  ·  存储在MonogoDB中时将类实例转换为字符串
    1 年前
    Jaime Montoya  ·  访问器魔术方法在PHP 8中不起作用[重复]
    1 年前
    jay ram  ·  如何在URL核心php中从API获取JSON?
    1 年前
    Endricho Folabessy  ·  我在Laravel 10中的一对一关系有什么错误
    1 年前
    pernifloss  ·  如何在使用电子邮件时保持Outlook邮件未读trhough pop3
    1 年前
    Ishwarya A  ·  php电子表格在浏览器中显示多张excel
    1 年前
    关于  
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号