gpt4 book ai didi

mysql - 在 laravel 中处理死锁异常

转载 作者:可可西里 更新时间:2023-11-01 08:09:37 25 4
gpt4 key购买 nike

当我使用 Laravel Eloquent 执行一些插入/更新查询时,我在我的 Laravel 应用程序中遇到了这个错误

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found

如何重新执行查询直到完成?

最佳答案

这个解决方案适用于 Laravel 5.1,但我相信它可以用于框架的新版本,只需稍作改动。

以下代码假定默认数据库连接名称为“mysql”。在 config/database.php 字段 default 中检查它。

创建扩展 Illuminate\Database\MySqlConnection 的新类:

namespace App\Helpers\MySQL;

use Closure;
use Exception;
use Illuminate\Database\MySqlConnection;
use Illuminate\Database\QueryException;
use Log;
use PDOException;

/**
* Class DeadlockReadyMySqlConnection
*
* @package App\Helpers
*/
class DeadlockReadyMySqlConnection extends MySqlConnection
{
/**
* Error code of deadlock exception
*/
const DEADLOCK_ERROR_CODE = 40001;

/**
* Number of attempts to retry
*/
const ATTEMPTS_COUNT = 3;

/**
* Run a SQL statement.
*
* @param string $query
* @param array $bindings
* @param \Closure $callback
* @return mixed
*
* @throws \Illuminate\Database\QueryException
*/
protected function runQueryCallback($query, $bindings, Closure $callback)
{
$attempts_count = self::ATTEMPTS_COUNT;

for ($attempt = 1; $attempt <= $attempts_count; $attempt++) {
try {
return $callback($this, $query, $bindings);
} catch (Exception $e) {
if (((int)$e->getCode() !== self::DEADLOCK_ERROR_CODE) || ($attempt >= $attempts_count)) {
throw new QueryException(
$query, $this->prepareBindings($bindings), $e
);
} else {
$sql = str_replace_array('\?', $this->prepareBindings($bindings), $query);
Log::warning("Transaction has been restarted. Attempt {$attempt}/{$attempts_count}. SQL: {$sql}");
}
}
}

}
}

扩展基本连接工厂Illuminate\Database\Connectors\ConnectionFactory:

namespace App\Helpers\MySQL;

use Config;
use Illuminate\Database\Connectors\ConnectionFactory;
use Illuminate\Database\MySqlConnection;
use Illuminate\Database\PostgresConnection;
use Illuminate\Database\SQLiteConnection;
use Illuminate\Database\SqlServerConnection;
use InvalidArgumentException;
use PDO;

/**
* Class YourAppConnectionFactory
*
* @package App\Helpers\MySQL
*/
class YourAppConnectionFactory extends ConnectionFactory
{
/**
* Create a new connection instance.
*
* @param string $driver
* @param PDO $connection
* @param string $database
* @param string $prefix
* @param array $config
* @return \Illuminate\Database\Connection
*
* @throws InvalidArgumentException
*/
protected function createConnection($driver, PDO $connection, $database, $prefix = '', array $config = [])
{
if ($this->container->bound($key = "db.connection.{$driver}")) {
return $this->container->make($key, [$connection, $database, $prefix, $config]);
}

switch ($driver) {
case 'mysql':
if ($config['database'] === Config::get('database.connections.mysql.database')) {
return new DeadlockReadyMySqlConnection($connection, $database, $prefix, $config);
} else {
return new MySqlConnection($connection, $database, $prefix, $config);
}
case 'pgsql':
return new PostgresConnection($connection, $database, $prefix, $config);
case 'sqlite':
return new SQLiteConnection($connection, $database, $prefix, $config);
case 'sqlsrv':
return new SqlServerConnection($connection, $database, $prefix, $config);
}

throw new InvalidArgumentException("Unsupported driver [$driver]");
}
}

现在我们应该在 Providers/AppServiceProvider.php 中替换标准框架的数据库连接工厂(或创建新的服务提供者)

public function register()
{
$this->app->singleton('db.factory', function ($app) {
return new YourAppConnectionFactory($app);
});
}

就是这样!现在所有因死锁而失败的查询都应该重新启动。

关于mysql - 在 laravel 中处理死锁异常,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/44859667/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com