MySQL 事务嵌套
2024-08-25 22:51:49
- MySQL默认自动提交（autocommit=1），如果要开启事务，先关闭自动提交（autocommit=0）；
- InnoDB支持事务，MyISAM不支持；
- MySQL不支持事务嵌套；已经开启事务后，再开启事务（start transaction），会隐式的提交（commit）上一个事务。
MySQL中可以通过部分事务（savepoint）来实现嵌套
TRUNCATE nested_transaction;
BEGIN;
INSERT INTO `nested_transaction`(id) VALUES(1);
SAVEPOINT t1;
INSERT INTO `nested_transaction`(id) VALUES(2);
ROLLBACK TO SAVEPOINT t1;
COMMIT;
id
--------
1
PHP中Laravel中事务嵌套（ManagesTransactions.php）
<?php namespace Illuminate\Database\Concerns; use Closure;
use Exception;
use Throwable; trait ManagesTransactions
{
/**
* Execute a Closure within a transaction.
*
* @param \Closure $callback
* @param int $attempts
* @return mixed
*
* @throws \Exception|\Throwable
*/
public function transaction(Closure $callback, $attempts = 1)
{
for ($currentAttempt = 1; $currentAttempt <= $attempts; $currentAttempt++) {
$this->beginTransaction(); // We'll simply execute the given callback within a try / catch block and if we
// catch any exception we can rollback this transaction so that none of this
// gets actually persisted to a database or stored in a permanent fashion.
try {
return tap($callback($this), function ($result) {
$this->commit();
});
} // If we catch an exception we'll rollback this transaction and try again if we
// are not out of attempts. If we are out of attempts we will just throw the
// exception back out and let the developer handle an uncaught exceptions.
catch (Exception $e) {
$this->handleTransactionException(
$e, $currentAttempt, $attempts
);
} catch (Throwable $e) {
$this->rollBack(); throw $e;
}
}
} /**
* Handle an exception encountered when running a transacted statement.
*
* @param \Exception $e
* @param int $currentAttempt
* @param int $maxAttempts
* @return void
*
* @throws \Exception
*/
protected function handleTransactionException($e, $currentAttempt, $maxAttempts)
{
// On a deadlock, MySQL rolls back the entire transaction so we can't just
// retry the query. We have to throw this exception all the way out and
// let the developer handle it in another way. We will decrement too.
if ($this->causedByDeadlock($e) &&
$this->transactions > 1) {
--$this->transactions; throw $e;
} // If there was an exception we will rollback this transaction and then we
// can check if we have exceeded the maximum attempt count for this and
// if we haven't we will return and try this query again in our loop.
$this->rollBack(); if ($this->causedByDeadlock($e) &&
$currentAttempt < $maxAttempts) {
return;
} throw $e;
} /**
* Start a new database transaction.
*
* @return void
* @throws \Exception
*/
public function beginTransaction()
{
$this->createTransaction(); ++$this->transactions; $this->fireConnectionEvent('beganTransaction');
} /**
* Create a transaction within the database.
*
* @return void
*/
protected function createTransaction()
{
if ($this->transactions == 0) {
try {
$this->getPdo()->beginTransaction();
} catch (Exception $e) {
$this->handleBeginTransactionException($e);
}
} elseif ($this->transactions >= 1 && $this->queryGrammar->supportsSavepoints()) {
$this->createSavepoint();
}
} /**
* Create a save point within the database.
*
* @return void
*/
protected function createSavepoint()
{
$this->getPdo()->exec(
$this->queryGrammar->compileSavepoint('trans'.($this->transactions + 1))
);
} /**
* Handle an exception from a transaction beginning.
*
* @param \Exception $e
* @return void
*
* @throws \Exception
*/
protected function handleBeginTransactionException($e)
{
if ($this->causedByLostConnection($e)) {
$this->reconnect(); $this->pdo->beginTransaction();
} else {
throw $e;
}
} /**
* Commit the active database transaction.
*
* @return void
*/
public function commit()
{
if ($this->transactions == 1) {
$this->getPdo()->commit();
} $this->transactions = max(0, $this->transactions - 1); $this->fireConnectionEvent('committed');
} /**
* Rollback the active database transaction.
*
* @param int|null $toLevel
* @return void
*/
public function rollBack($toLevel = null)
{
// We allow developers to rollback to a certain transaction level. We will verify
// that this given transaction level is valid before attempting to rollback to
// that level. If it's not we will just return out and not attempt anything.
$toLevel = is_null($toLevel)
? $this->transactions - 1
: $toLevel; if ($toLevel < 0 || $toLevel >= $this->transactions) {
return;
} // Next, we will actually perform this rollback within this database and fire the
// rollback event. We will also set the current transaction level to the given
// level that was passed into this method so it will be right from here out.
$this->performRollBack($toLevel); $this->transactions = $toLevel; $this->fireConnectionEvent('rollingBack');
} /**
* Perform a rollback within the database.
*
* @param int $toLevel
* @return void
*/
protected function performRollBack($toLevel)
{
if ($toLevel == 0) {
$this->getPdo()->rollBack();
} elseif ($this->queryGrammar->supportsSavepoints()) {
$this->getPdo()->exec(
$this->queryGrammar->compileSavepointRollBack('trans'.($toLevel + 1))
);
}
} /**
* Get the number of active transactions.
*
* @return int
*/
public function transactionLevel()
{
return $this->transactions;
}
}
也可以只开启一个事物，判断如果是嵌套事务，则不做操作直接返回。
最新文章
- spring自动扫描、DispatcherServlet初始化流程、spring控制器Controller 过程剖析
- css解决display:inline-block;产生的缝隙（间隙）
- Glide请求图片能携带Cookie的哟！
- haoop 断电后导致block文件损坏
- 自定义多列排序：C++/Java实现
- Keypress – 超强大！捕获键盘输入的 JavaScript 库
- SSH整合所需jar
- angularjs应用骨架(4)
- MongoDB备份恢复与导出导入
- vue 外部字体图标使用，无须绝对路径引入办法
- monkey日志管理
- 吴恩达机器学习笔记47-K均值算法的优化目标、随机初始化与聚类数量的选择（Optimization Objective & Random Initialization & Choosing the Number of Clusters of K-Means Algorithm）
- C#简单的九九乘法表
- Python2.7-内置异常类型
- quartz-job实现定时任务配置
- stl算法:next_permutation剖析
- 数据需求统计常用awk命令
- Linux系统下 MYSQL数据库中的数据库文件在本机内迁移 （需暂停服务的方式）
- [html][javascript]动态增删页面元素
- 使用Socket&反射&Java流操作进行方法的远程调用(模拟RPC远程调用)