Use transactions in multiple database connections (MySQL) in Laravel 6
1. Now for an API, 4 tables need to be inserted. The 1st table is the table in database A, and the other 3 tables are the table in database B, and the primary key of the 1st table will be inserted into the 2nd table. 2 database connections have been configured. are WordPress and DB respectively. It is recommended to connect only one database in one application, but now it is a problem left over from history.
2. The existing code implementation is as follows. Database transactions are only used when inserting 3 tables in database B. If the transaction is rolled back, the inserted data in the table in database A becomes dirty data.
$wpTheme = Theme::create([
'name' => $name,
'custom_name' => $custom_name,
'content' => $content,
'is_original' => $isOriginal,
'original_theme_name' => $config['name'],
]);
DB::beginTransaction();
try {
$themeInstallation = new ThemeInstallation();
$themeInstallation->theme_store_theme_id = $saasThemeConfig[$name]['id'];
$themeInstallation->theme_id = $name;
$themeInstallation->wp_theme_id = $wpTheme->id;
$themeInstallation->theme_custom_name = $custom_name;
$themeInstallation->save();
$themeInstallationVersion = new ThemeInstallationVersion();
$themeInstallationVersion->theme_installation_id = $themeInstallation->id;
$themeInstallationVersion->theme_store_theme_id = $saasThemeConfig[$name]['id'];
$themeInstallationVersion->theme_store_theme_version_id = $saasThemeConfig[$name]['id'];
$themeInstallationVersion->save();
$themeInstallationTask = new ThemeInstallationTask();
$themeInstallationTask->theme_installation_id = $themeInstallation->id;
$themeInstallationTask->theme_installation_version_id = $themeInstallationVersion->id;
$themeInstallationTask->save();
DB::commit();
} catch(\Throwable $e) {
DB::rollBack();
throw $e;
}
3. The model configuration connection corresponding to the 1st table is as follows
class Theme extends Model
{
protected $connection = 'wordpress';
}
4. The model configuration connection corresponding to the 2, 3 and 4 tables is the default connection.
5. Reference implementation:https://laracasts.com/discuss/channels/laravel/transactions-with-multiple-database-connections. However, if db::commit() is successful and db::connection(WordPress)->commit() what happens if it fails? In this case, we cannot roll back in the DB and the consistency is broken.
6. Test the successful transaction of the transaction first, and confirm that all 4 tables are successfully inserted into the data.
7. When the 1st table (corresponding to the connection wordpress, corresponding $wptheme) is inserted successfully, the insertion in the 2nd table (corresponding to the connection db) fails, the transaction is rolled back, and no data is inserted in all tables. in line with expectations. as shown in Figure 1
Figure 1
{
"message": "SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'theme' for key 'theme_installation_theme_id_unique' (SQL: insert into `theme_installation` (`theme_custom_name`, `role`, `processing`, `processing_failed`, `theme_store_theme_id`, `theme_id`, `wp_theme_id`, `updated_at`, `created_at`) values (theme, unpublished, 1, 0, 9, theme, 31, 2022-05-18 10:39:55, 2022-05-18 10:39:55))",
"code": "23000",
"status_code": 500,
"debug": {
"line": 669,
"file": "\\.\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php",
"class": "Illuminate\\Database\\QueryException",
"trace": {
}
}
}
8. When the 1st table (corresponding to the WordPress, corresponding $WPTHEME) is inserted successfully, the insertion of the 2nd table (corresponding to the connection dB) is successful, and the 3rd table (corresponding to the connection Insertion failed in db), the transaction is rolled back, and no data is inserted in all tables. in line with expectations. as shown in Figure 2
Figure 2
{
"message": "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'theme_installation_ide' in 'field list' (SQL: insert into `theme_installation_version` (`update_state`, `processing`, `processing_failed`, `theme_installation_ide`, `theme_store_theme_id`, `theme_store_theme_version_id`, `updated_at`, `created_at`) values (manual_update, 1, 0, 7, 9, 9, 2022-05-18 10:46:32, 2022-05-18 10:46:32))",
"code": "42S22",
"status_code": 500,
"debug": {
"line": 669,
"file": "\\.\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php",
"class": "Illuminate\\Database\\QueryException",
"trace": {
}
}
}
9. When the 1st table (corresponding to the wordpress corresponding to the connection, corresponding to $wptheme) fails to insert, no data is inserted in all tables. in line with expectations. as shown in Figure 3
Figure 3
{
"message": "Array to string conversion",
"status_code": 500,
"debug": {
"line": 419,
"file": "\\.\\vendor\\laravel\\framework\\src\\Illuminate\\Support\\Str.php",
"class": "ErrorException",
"trace": {
}
}
}
10. For the problem, if db::commit() is successful and db::connection(WordPress)->commit() what happens if it fails? It is not easy to simulate for the time being, and it is decided to leave it for subsequent analysis and processing.
![在 Laravel 中，当在中间件中响应 404 时，报错：View [components.css] not found](https://www.shuijingwanwq.com/wp-content/uploads/2023/05/1-520x245.png)
