Menu
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.
841
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.
85
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
