1. An API now needs to insert into 4 tables. The first table is in database A, and the other three tables are in database B. The primary key of the first table will be inserted into the second table. Two database connections have been configured, named wordpress and db, respectively. Connecting to only one database within an application is recommended, but this is a legacy issue.
2. The existing code implementation is as follows. A database transaction is only used when inserting into the three tables in database B. If the transaction rolls back, the data already inserted into 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 connection configuration corresponding to the first table is as follows:
class Theme extends Model
{
protected $connection = 'wordpress';
}
4. The model connection configuration corresponding to the 2nd, 3rd, and 4th tables is the default connection.
5. Reference implementation: https://laracasts.com/discuss/channels/laravel/transactions-with-multiple-database-connections . However, what happens if DB::commit() succeeds but DB::connection(‘wordpress’)->commit() fails? In this case, we cannot roll back in db, and consistency is broken.
6. First, test the scenario where the transaction succeeds, confirming that data is successfully inserted into all 4 tables.
7. When the 1st table (corresponding to connection wordpress, corresponding to $wpTheme) is inserted successfully, but the insertion into the 2nd table (corresponding to connection db) fails, the transaction rolls back, and no data is inserted into any table. This meets expectations. See 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 connection wordpress, corresponding to $wpTheme) is inserted successfully, the 2nd table (corresponding to connection db) is inserted successfully, but the insertion into the 3rd table (corresponding to connection db) fails, the transaction rolls back, and no data is inserted into any table. This meets expectations. See 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 insertion into the 1st table (corresponding to connection wordpress, corresponding to $wpTheme) fails, no data is inserted into any table. This meets expectations. See 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. Regarding the issue: what happens if DB::commit() succeeds but DB::connection(‘wordpress’)->commit() fails? It is temporarily difficult to simulate, so I have decided to leave it for subsequent analysis and handling.