PHP PDO MySQL及其如何真正处理MySQL事务? [英] PHP PDO MySQL and how does it really deal with MySQL transactions?

查看：57
本文介绍了PHP PDO MySQL及其如何真正处理MySQL事务?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我试图克服它，但是我只是不理解使用PDO和MySQL在PHP中进行事务处理的逻辑.

I am trying to get over it, but I just can't understand the logic behind the process of transactions in PHP using PDO and MySQL.

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

I know this question is going to be long, but I think it's worth it.

鉴于我阅读了很多有关MySQL事务，它们如何由服务器处理，它们如何与锁和其他隐式提交语句等相关的知识，不仅在SO上，而且在MySQL和PHP手册上也是如此:

Given that I read a lot about MySQL transactions, how they are handled by the server, how they relate to locks and other implicit commit statements, etc., not only here on SO, but also on the MySQL and PHP manuals:

  • Mysql transactions within transactions
  • Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)
  • https://dev.mysql.com/doc/refman/5.7/en/commit.html
  • https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
  • http://php.net/manual/en/pdo.transactions.php
  • http://php.net/manual/en/pdo.begintransaction.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_AUTOCOMMIT, 0)行的数据库(已注释)):

test2.php (Database without the PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0) line (commented out)):

<?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();
}

我仍然有以下疑问和未解决的问题:

I still have the following doubts and unanswered questions:

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

  • Using InnoDB, is there a difference between PDO::beginTransaction() and PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0) when we use PDO in PHP and/or in MySQL with plain MySQL statements SET AUTOCOMMIT = 0; and START TRANSACTION;? If yes, what is it?

如果检查我的PHP示例，在Database::beginTransaction()包装方法中，我同时在文件 test1.php 中使用PDO::beginTransaction()和PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0)，并且在文件PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0) > test2.php . 我发现使用PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0)时会发生奇怪的事情:

If you check my PHP example, within the Database::beginTransaction() wrapper method I use both PDO::beginTransaction() and PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0) in file test1.php and do not use PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0) in file test2.php. I found out that strange things happen when I use PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0):

  • 在Database( test1.php )中的PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0)行中 带有LOCK TABLES语句的事务，LOCK TABLES 不 似乎隐式提交了交易，因为如果我连接 与另一个客户端，我看不到插入的行，直到代码到达$db->commit();行，而MySQL 手册说:

  • With PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0) line in Database (test1.php), inside a transaction with a LOCK TABLES statement, LOCK TABLES does not seem to implicitly commit the transaction, because if I connect with another client I cannot see the rows inserted until the code reaches the $db->commit(); line, whereas the MySQL manual says:

LOCK TABLES不是事务安全的，它会在尝试锁定表之前隐式提交任何活动事务.

LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.

因此我们可以用PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0)这么说吗(在MySQL上是 SET AUTOCOMMIT = 0;)事务不是由隐式提交的 像LOCK TABLES这样的语句?然后我会说有一个 MySQL手册和PHP PDO实现之间的不一致 (我不是在抱怨，我只是想了解)；

Can we therefore say that with PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0) (which on MySQL would be SET AUTOCOMMIT = 0;) a transaction is not implicitly committed by statements like LOCK TABLES? Then I would say that there's an inconsistency between the MySQL manual and the PHP PDO implementation (I am not complaining, I just want to understand);

没有Database( test2.php )中的PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0)行，该代码的行为似乎与MySQL的一致 手动LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.:一旦到达LOCK TABLES查询，就会有一个隐式提交，因此在$db->insert('table_name', array('table_col' => 'TEST2_TABLE_NAME'));行之后，其他客户端甚至可以在到达$db->commit();;

Without the PDO::setAttribute(PDO::ATTR_AUTOCOMMIT, 0) line in Database (test2.php), the code seems to behave consistently with the MySQL's manual LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.: as soon as it reaches the LOCK TABLES query, there's an implicit commit, so after the line $db->insert('table_name', array('table_col' => 'TEST2_TABLE_NAME')); other clients can see the new inserted row even before reaching $db->commit();;

我刚刚描述的以下行为的解释是什么?当我们使用PHP的PDO并在交易中包含implicit-commit语句时，交易如何工作?

What is the explanation to the following behaviour I just described? How does transactions work when we use PHP's PDO and have implicit-commit statements within our transaction?

我的PHP版本是7.0.22，MySQL版本是5.7.20.

My PHP version is 7.0.22, MySQL version is 5.7.20.

感谢您的关注.

推荐答案

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

如果在具有SET autocommit = 0的会话中禁用了自动提交模式，则该会话始终具有打开的事务. COMMIT或ROLLBACK语句结束当前事务，并开始新的事务.

If autocommit mode is disabled within a session with SET autocommit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.

因此，当您在会话(将其称为会话1)中设置autocommit = 0时，这会隐式地打开一个事务，并使其无限期地保持打开状态.

So when you set autocommit=0 in a session (call it session 1), this implicitly opens a transaction, and leaves it open indefinitely.

默认事务隔离级别为REPEATABLE-READ.因此，在会话1明确提交或回滚之前，您的会话将不会看到其他会话工作中已提交更改的刷新视图.

The default transaction isolation level is REPEATABLE-READ. So your session will not see a refreshed view of committed changes from other sessions' work until session 1 explicitly commits or rolls back.

您在另一个会话2中的锁表确实导致隐式提交，但是会话1看不到结果，因为由于它自己的事务，它仍然只能看到数据的隔离视图快照.

Your LOCK TABLES in another session 2 does cause an implicit commit, but session 1 doesn't see the result because it's still only able to see an isolated view of the data because of its own transaction snapshot.

这篇关于PHP PDO MySQL及其如何真正处理MySQL事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
PHP最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆