MySQL 嵌套事务、PHP+MySQL嵌套事务、ThinkPHP 嵌套事务、Laravel 嵌套事务
在 MySQL 的官方文档中有明确的说明不支持嵌套事务:
Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.
翻译:
当执行一个START TRANSACTION指令时,会隐式的执行一个commit操作。 所以我们就要在系统架构层面来支持事务的嵌套。
所幸的是在一些成熟的ORM框架中都做了对嵌套的支持,比如 ThinkPHP 和 Laravel等。
如果多层嵌套,transTimes会进行标记。如果嵌套,只有最外层的事务是生效的。这种事务嵌套处理方式跟laravel是一模一样的。
ThinkPHP 6.x 嵌套事务的逻辑代码
文件位置:vendor/topthink/think-orm/src/db/PDOConnection.php
/**
* 启动事务
* @access public
* @return void
* @throws \PDOException
* @throws \Exception
*/
public function startTrans(): void
{
try {
$this->initConnect(true);
++$this->transTimes;
if (1 == $this->transTimes) {
$this->linkID->beginTransaction();
} elseif ($this->transTimes > 1 && $this->supportSavepoint()) {
$this->linkID->exec(
$this->parseSavepoint(‘trans‘ . $this->transTimes)
);
}
$this->reConnectTimes = 0;
} catch (\Exception $e) {
if ($this->reConnectTimes <4 && $this->isBreak($e)) {
--$this->transTimes;
&#43;&#43;$this->reConnectTimes;
$this->close()->startTrans();
} else {
throw $e;
}
}
}
/**
* 用于非自动提交状态下面的查询提交
* &#64;access public
* &#64;return void
* &#64;throws PDOException
*/
public function commit(): void
{
$this->initConnect(true);
if (1 &#61;&#61; $this->transTimes) {
$this->linkID->commit();
}
--$this->transTimes;
}
/**
* 事务回滚
* &#64;access public
* &#64;return void
* &#64;throws PDOException
*/
public function rollback(): void
{
$this->initConnect(true);
if (1 &#61;&#61; $this->transTimes) {
$this->linkID->rollBack();
} elseif ($this->transTimes > 1 && $this->supportSavepoint()) {
$this->linkID->exec(
$this->parseSavepointRollBack(‘trans‘ . $this->transTimes)
);
}
$this->transTimes &#61; max(0, $this->transTimes - 1);
}
Laravel 8.x 嵌套事务的逻辑代码
文件位置&#xff1a;/laravel/framework/blob/8.x/src/Illuminate/Database/DatabaseTransactionsManager.php
/**
* Start a new database transaction.
*
* &#64;param string $connection
* &#64;param int $level
* &#64;return void
*/
public function begin($connection, $level)
{
$this->transactions->push(
new DatabaseTransactionRecord($connection, $level)
);
}
/**
* Rollback the active database transaction.
*
* &#64;param string $connection
* &#64;param int $level
* &#64;return void
*/
public function rollback($connection, $level)
{
$this->transactions &#61; $this->transactions->reject(function ($transaction) use ($connection, $level) {
return $transaction->connection &#61;&#61; $connection &&
$transaction->level > $level;
})->values();
}
/**
* Commit the active database transaction.
*
* &#64;param string $connection
* &#64;return void
*/
public function commit($connection)
{
$this->transactions &#61; $this->transactions->reject(function ($transaction) use ($connection) {
if ($transaction->connection &#61;&#61; $connection) {
$transaction->executeCallbacks();
return true;
}
return false;
})->values();
}
文件位置&#xff1a;/laravel/framework/blob/8.x/src/Illuminate/Database/DatabaseTransactionRecord.php
namespace Illuminate\Database;
class DatabaseTransactionRecord
{
/**
* The name of the database connection.
*
* &#64;var string
*/
public $connection;
/**
* The transaction level.
*
* &#64;var int
*/
public $level;
/**
* The callbacks that should be executed after committing.
*
* &#64;var array
*/
protected $callbacks &#61; [];
/**
* Create a new database transaction record instance.
*
* &#64;param string $connection
* &#64;param int $level
* &#64;return void
*/
public function __construct($connection, $level)
{
$this->connection &#61; $connection;
$this->level &#61; $level;
}
/**
* Register a callback to be executed after committing.
*
* &#64;param callable $callback
* &#64;return void
*/
public function addCallback($callback)
{
$this->callbacks[] &#61; $callback;
}
/**
* Execute all of the callbacks.
*
* &#64;return void
*/
public function executeCallbacks()
{
foreach ($this->callbacks as $callback) {
call_user_func($callback);
}
}
/**
* Get all of the callbacks.
*
* &#64;return array
*/
public function getCallbacks()
{
return $this->callbacks;
}
}
php&#43;mysql 事务多层嵌套的实现方式
msyql 本身不支持事务嵌套的&#xff0c;但是可以按照嵌套事务的思路变相实现事务多层嵌套。
开启事务时 先 mark 一个标志&#xff0c;每嵌套一次&#xff0c;就将该值加 1&#xff0c;但是开启事务这个操作只在 mark&#61;1 时才真的去实现&#xff0c;其他只是累加。
而提交时&#xff0c;肯定是从最内层开始提交&#xff0c;每提交一次&#xff0c;mark 减去 1&#xff0c;直到 mark&#61;1 时&#xff0c;才真的去实现提交。
回滚也是如此。
代码如下&#xff1a;
public function beginTransaction() {
&#43;&#43;$this->transactions;
if ($this->transactions&#61;&#61;1){
$this->pdo->beginTransaction();
}
}
public function rollBack() {
if ($this->transactions &#61;&#61;1) {
$this->transactions &#61;&#61;0;
$this->pdo->rollBack();
} else {
--$this->transactions;
}
}
public function commit() {
if ($this->transactions &#61;&#61;1){
$this->pdo->commit();
}
--$this->transactions;
}
MySQL 使用 savepoint 和 rollback to 的语句实现事务嵌套
在 MySQL 多次开启事务&#xff0c;出现了数据错乱问题&#xff0c;伪代码如下&#xff1a;
begin;
# 操作逻辑代码块1
begin;
# 操作逻辑代码块2
rollback;
执行完后出现了操作1的数据真正写入&#xff0c;只有操作2的数据回滚了。在第一个事务没有提交或回滚时&#xff0c;再开启第二个事务时&#xff0c;会自动提交第一个事务。
这明显不符合心理预期&#xff0c;而且也无法回滚一部分操作。那么问题来了&#xff0c;MySQL 支不支持事务嵌套呢&#xff1f;
这个问题很难准确回答支持还是不支持&#xff01;
首先&#xff0c;调用多次begin的写法&#xff0c;在 MySQL 里肯定是无法首先事务嵌套的。经过群内一位朋友的提醒&#xff0c;了解到 MySQL 中有一个叫 savepoint 和 rollback to 的语句。
示例代码&#xff1a;
DROP TABLE IF EXISTS &#96;test&#96;;
CREATE TABLE &#96;test&#96; (
&#96;id&#96; int(10) unsigned NOT NULL AUTO_INCREMENT,
&#96;name&#96; varchar(255) DEFAULT NULL,
PRIMARY KEY (&#96;id&#96;)
) ENGINE&#61;InnoDB AUTO_INCREMENT&#61;1 DEFAULT CHARSET&#61;utf8;
begin;
insert into &#96;test&#96;(&#96;name&#96;) values(‘111‘);
SAVEPOINT p1;
insert into &#96;test&#96;(&#96;name&#96;) values(‘222‘);
ROLLBACK TO p1;
commit;
最终执行结果&#xff0c;test表中只有 111 这个数据&#xff0c;实现了部分操作的回滚操作。同理也避免了多次开启事务&#xff0c;导致前一个事务被提交的问题。
可能savepoint和rollback to语句并不能称之为事务嵌套&#xff0c;也不能说 MySQL 是支持还是不支持事务嵌套。总之通过savepoint和rollback to&#xff0c;是可以用来达到一些事务嵌套特性的。