Mysql事务
SQL命令介绍
begin:开启事务
commit:提交事务
rollback:回滚事务
savepoint:暂存点,记录事务中的某个节点,可以使用rollback命令回退到指定节点
Mysql执行demo
//1.开启事务
mysql> begin ;
Query OK, 0 rows affected
//2.第一次修改操作
mysql> update form_store set store_num=2 where store_id=1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
//3.第一次记录暂存点,暂存点名称为p1
mysql> savepoint p1;
Query OK, 0 rows affected
//第二次修改操作并记录暂存点以此类推
mysql> update form_store set store_num=3 where store_id=1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> savepoint p2;
Query OK, 0 rows affected
mysql> update form_store set store_num=4 where store_id=1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> savepoint p4;
Query OK, 0 rows affected
//rollback到指定的p2暂存点上
mysql> rollback to p2;
Query OK, 0 rows affected
//commit提交
mysql> commit ;
Query OK, 0 rows affected
结果:store_num=3;
ThinkPHP事务源码
ThinkPHP开启事务方法
/**
* 启动事务
* @access public
* @return void
* @throws \PDOException
* @throws \Exception
*/
public function startTrans(): void
{
try {
$this->initConnect(true);
//开启事务次数累加
++$this->transTimes;
//只有第一次开启才执行begin,否则都是savepoint
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 (\Throwable | \Exception $e) {
if (1 === $this->transTimes && $this->reConnectTimes < 4 && $this->isBreak($e)) {
--$this->transTimes;
++$this->reConnectTimes;
$this->close()->startTrans();
} else {
if ($this->isBreak($e)) {
// 尝试对事务计数进行重置
$this->transTimes = 0;
}
throw $e;
}
}
}
ThinkPHP提交事务方法
/**
* 用于非自动提交状态下面的查询提交
* @access public
* @return void
* @throws \PDOException
*/
public function commit(): void
{
$this->initConnect(true);
//只有根据开启事务的记数标识,直到最后一次(就是最外层事务)才真正commit
if (1 == $this->transTimes) {
$this->linkID->commit();
}
//提交一次减少一次
--$this->transTimes;
}
ThinkPHP回滚事务方法
/**
* 事务回滚
* @access public
* @return void
* @throws \PDOException
*/
public function rollback(): void
{
$this->initConnect(true);
if (1 == $this->transTimes) {
//也是只有开启事务计数标识直到最后一次(就是最外层事务)才真正的rollback
//就是rollback to p2的sql命令
$this->linkID->rollBack();
} elseif ($this->transTimes > 1 && $this->supportSavepoint()) {
//否则就是记录暂存点
$this->linkID->exec(
$this->parseSavepointRollBack('trans' . $this->transTimes)
);
}
//不断减-1
$this->transTimes = max(0, $this->transTimes - 1);
}
/**
* 生成回滚到保存点的SQL
* @access protected
* @param string $name 标识
* @return string
*/
protected function parseSavepointRollBack(string $name): string
{
return 'ROLLBACK TO SAVEPOINT ' . $name;
}