John Davidson

Message:

i think i will have a problem with race condition because of dynamic database.

My API in each request will connect to a different database, multiple clients will use it, but each client has his own data base (SQL SERVER).

I'm changing the connection in the constructor of the controller:

On construct:
DB::disconnect(env("DB_CONNECTION"));

    Config::set('database.connections.' . env("DB_CONNECTION"), array(

        'driver'    => $data->driver,

        'host'      => $data->servername,

        'database'  => $data->database,

        'username'  => $data->user,

        'password'  => $data->password,

        'charset'   => 'utf8',

        'prefix'    => '',

    ));

On destruct:

    DB::disconnect(env("DB_CONNECTION"));

With this, the rest of the controller, can work normal with the ORM.

My question is, if i have about 200 requests on each client, how are the chances, of a wrong connection?

Because the config is global, if another request comes in, before the last is finished, what will happen?

I'm thinking to change the connetion in the model, it will be focused there, and not long before, like de controller. But I dont know if will be enough. PHP only suports one connection at time rigth? So it is not even a laravel problem.

I'm using:

• Laravel 9


• PHP 8


• SQL SERVER

Thank you all for your time, have a great day.

---

My Answer:
'DB_CONNECTION'));
DB::purge(env('DB_CONNECTION'));
Config::set('database.connections.' . env('DB_CONNECTION') . '.database', $client_database_name);
Config::set('database.connections.' . env('DB_CONNECTION') . '.username', $client_database_username);
Config::set('database.connections.' . env('DB_CONNECTION') . '.password', $client_database_password);
DB::reconnect(env('DB_CONNECTION'));

This code changes the database connection dynamically based on the client's database credentials. However, this can lead to race conditions if multiple requests are trying to change the connection at the same time.

To prevent race conditions, you can use a mutex or a semaphore to ensure that only one request can change the database connection at a time. Here's an example using Laravel's built-in mutex:

use Illuminate\Support\Facades\Cache;

$mutexKey = 'database_connection_mutex';

$mutex = Cache::lock($mutexKey);

if ($mutex->get()) {
DB::disconnect(env('DB_CONNECTION'));
DB::purge(env('DB_CONNECTION'));
Config::set('database.connections.' . env('DB_CONNECTION') . '.database', $client_database_name);
Config::set('database.connections.' . env('DB_CONNECTION') . '.username', $client_database_username);
Config::set('database.connections.' . env('DB_CONNECTION') . '.password', $client_database_password);
DB::reconnect(env('DB_CONNECTION'));

$mutex->release();
} else {
// Handle the case where the mutex could not be acquired
}

By using a mutex, you ensure that only one request can change the database connection at a time, preventing race conditions.