PHP PDO MySQL and how does it really deal with MySQL transactions?

Question

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.

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:

And given this code:

schema:

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 (with 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 (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:

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?

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):
- 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 is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.
  
  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);
- 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();;

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?

My PHP version is 7.0.22, MySQL version is 5.7.20.

Thanks for the attention.

https://stackoverflow.com/questions/3106737/pdo-mysql-transactions-and-table-locking looks informative . According to that `PDO::beginTransaction` is actually turning off autocommit and not starting a new transaction. That claim seems consistent with this behaviour — apokryfos, Dec 14 '17 at 09:53
But what's the difference then? If `PDO::beginTransaction` turns off autocommit, then why the results of the 2 scripts I wrote are different depending on whether I use `$this->pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,0);` or not? — tonix, Dec 14 '17 at 10:03

score 1 · Accepted Answer · answered Dec 14 '17 at 20:47

1

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

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.

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

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.

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.

answered Dec 14 '17 at 20:47

Bill Karwin

538,548
86
673
828

Thank you for your response. In my case session 1 is the one which sets `autocommit = 0` and uses `LOCK TABLES` (not session 2). What happens in this case to `session 2`? Everything before the LOCK in session 1 will be committed and another transaction begins straightforward, but session 2 will not see the changes until there's an explicit `COMMIT/ROLLBACK` statement? – tonix Dec 15 '17 at 10:23
@tonix, I suggest you open two terminal windows and run the `mysql` client in each. Then you can do some experiments easily. – Bill Karwin Dec 15 '17 at 15:44
I did what you said and it seems that when `AUTOCOMMIT=0`, your statement `A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.` is also true for implicit commit statements like `LOCK TABLES`. Is it correct? – tonix Dec 15 '17 at 16:12
Yes, that should be correct. When autocommit=0, you always have a transaction open, which means as soon as one transaction is committed, a new transaction begins. – Bill Karwin Dec 15 '17 at 16:20
So it is like if MySQL automatically does `START TRANSACTION` for you as soon as you `COMMIT`, `ROLLBACK` or issue an implicit commit statement like `LOCK TABLES`, right? – tonix Dec 15 '17 at 16:23
Thank you very much, you explained me and made me understand the difference between `AUTOCOMMIT = 0` and `START TRANSACTION`! :) – tonix Dec 15 '17 at 17:14

PHP PDO MySQL and how does it really deal with MySQL transactions?

1 Answers1

Linked