Transactions Rollback breaking with multi-schema setup

I'm designing a project where I intend to use multiple schemas. I have multiple entries in my db config file, for example:

'content' => [
            'driver' => 'mysql',
            'url' => env('DB_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => 'content',
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => env('DB_CHARSET', 'utf8mb4'),
            'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => FALSE,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

        'cron' => [
            'driver' => 'mysql',
            'url' => env('DB_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => 'cron',
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => env('DB_CHARSET', 'utf8mb4'),
            'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => FALSE,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

        'employee' => [
            'driver' => 'mysql',
            'url' => env('DB_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => 'employee',
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => env('DB_CHARSET', 'utf8mb4'),
            'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => FALSE,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

The basic transaction:



        
        DB::beginTransaction();
        
        try {

			Content::factory()->create([]

					'id' => 1,
					'content_name' => 'thing'


			);

			Content::factory()->create([]

					'id' => 2,
					'content_name' => 'other thing'


			);

			Employee::factory()->create([]

					'id' => 1,
					'employee_name' => 'Bob'


			);

					Employee::factory()->create([]

					'id' => 2,

					// force failure with incorrect column name
					'employeename' => 'Jack'

			);

					
        
            DB::commit();
        
        } catch (\Exception $e) {
        
            DB::rollBack();
        
            return $e->getMessage();
        
        }

So the transaction throws the error, but does not roll back. I first discovered this in my feature tests, but this also happens outside of the tests. My models are set up correctly with schema/table definitions like so. The shcema is content, the table is contents:


protected $table = 'content.contents';

All of my factory tests work. I feel like I must be missing something basic in my config. I have a large database with over 250 tables and growing. I love breaking it into smaller schemas, but I'm beginning to wonder if it's worth it due to the extra coding involved in the queries, so some thoughts of wisdom would be appreciated. Single schema would be a lot simpler to implement, just unwieldy visually, which aggravates my ADD. Before I abandon multi-schema, I want to make sure I at least have the configuration right. Any help would be greatly appreciated, thank you.

LaryAI

1 year ago

Level 58

When dealing with transactions across multiple schemas or databases in Laravel, it's important to understand that a transaction is typically bound to a single database connection. If you're trying to perform a transaction that spans multiple database connections, each connection will have its own transaction scope, and a rollback on one connection won't affect the others.

Here's a step-by-step approach to address the issue:

Ensure Single Connection for Transaction: Make sure that all operations within a transaction are using the same database connection. If you're using multiple connections, you need to handle transactions separately for each connection.

Use Connection-Specific Transactions: If you need to perform operations on different schemas that are on different connections, you should manage transactions for each connection separately. For example:

DB::connection('content')->beginTransaction();
DB::connection('employee')->beginTransaction();

try {
    // Operations on 'content' schema
    DB::connection('content')->table('contents')->insert([
        'id' => 1,
        'content_name' => 'thing'
    ]);

    // Operations on 'employee' schema
    DB::connection('employee')->table('employees')->insert([
        'id' => 1,
        'employee_name' => 'Bob'
    ]);

    // Commit both transactions
    DB::connection('content')->commit();
    DB::connection('employee')->commit();
} catch (\Exception $e) {
    // Rollback both transactions
    DB::connection('content')->rollBack();
    DB::connection('employee')->rollBack();

    return $e->getMessage();
}

Consider Using a Single Connection with Multiple Schemas: If your database supports it, you can use a single connection and specify the schema in your queries. This way, you can manage transactions more easily. For example, in PostgreSQL, you can set the search path to include multiple schemas.
Check for Configuration Issues: Ensure that your database configuration is correct and that each connection is properly set up in your config/database.php file. Double-check the credentials and database names.
Testing and Debugging:
- Use Laravel's DB::listen to log queries and see if they are being executed on the expected connection.
- Ensure that your models are correctly configured to use the intended connection by setting the $connection property in your models.
Consider Using a Transaction Manager: If you frequently need to manage transactions across multiple connections, consider implementing a custom transaction manager that can handle this complexity.

By following these steps, you should be able to manage transactions effectively in a multi-schema setup. If the complexity becomes too high, you might want to reconsider the architecture or explore database features that better support your use case.

Please or to participate in this conversation.