我试图克服它,但我无法理解使用PDO和
MySQL在
PHP中进行事务处理的逻辑.
我知道这个问题会很长,但我认为这是值得的.
鉴于我阅读了很多关于MySQL事务的信息,服务器如何处理它们,它们与锁和其他隐式提交语句的关系等等,不仅在SO上,而且在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)):
// 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)行的数据库(注释掉)):
// 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_AUTOCOMMIT,0)当我们在PHP和/或MySQL中使用PDO时使用普通MySQL语句SET AUTOCOMMIT = 0;和START TRANSACTION;?如果是,那是什么?
如果你检查我的PHP示例,在Database :: beginTransaction()包装器方法中,我在文件test1.php中使用PDO :: beginTransaction()和PDO :: setAttribute(PDO :: ATTR_AUTOCOMMIT,0)并且不使用PDO: :文件test2.php中的setAttribute(PDO :: ATTR_AUTOCOMMIT,0).
我发现当我使用PDO :: setAttribute(PDO :: ATTR_AUTOCOMMIT,0)时会发生奇怪的事情:
>使用数据库(test1.php)中的PDO :: setAttribute(PDO :: ATTR_AUTOCOMMIT,0)行,在
使用LOCK TABLES语句的事务,LOCK TABLES不会
似乎隐式提交事务,因为如果我连接
与另一个客户端相比,在代码到达$db-> commit()之前,我看不到插入的行;线,而MySQL
手册说:
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实现之间的不一致
(我不是在抱怨,我只想了解);
>如果没有数据库(test2.php)中的PDO :: setAttribute(PDO :: ATTR_AUTOCOMMIT,0)行,代码似乎与MySQL的行为一致
手动LOCK TABLES不是事务安全的,也不是隐含的
在尝试锁定之前提交任何活动的事务
表:一旦到达LOCK TABLES查询,就会有一个隐式提交,所以在行$db-> insert(‘table_name’,array(‘table_col’=>’TEST2_TABLE_NAME’))之后;其他客户端甚至可以在到达$db-> commit();;之前看到新插入的行
我刚刚描述的以下行为的解释是什么?当我们使用PHP的PDO并在事务中使用隐式提交语句时,事务如何工作?
我的PHP版本是7.0.22,MySQL版本是5.7.20.
感谢您的关注.