Framework: Database lock wait timeout after errors within transaction

[awwang10]

I have a lot of transactions in my code, and if an error occurs in executing in one of these transactions that doesn't trigger commit or rollback, then the database is locked and any subsequent attempts to access the database results in this:

production.ERROR: PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction in /home/forge/default/vendor/laravel/framework/src/Illuminate/Database/Connection.php:390

In the Controller:

DB::beginTransaction();

try {
    //Code that uses exec() to process some images. <-- If code breaks here, for example, due to say, 504 error, then the lock wait timeout exceeded error appears on subsequent requests.
    //Code that accesses the database
}
catch(\Exception $e){
    //Do stuff
    DB::rollback();
    throw $e;
}
DB::commit();

Even php artisan migrate:refresh or php artisan migrate:reset stops working as well.

If I manually kill the process that is causing the lock at the beginning of every request, this is solved, but should this not be handled by framework?

[themsaid]

Helo @awwang10

Did you ever manage to solve this issue or stand on the reasons why it's happening?

[themsaid]

ping @awwang10

[thecrypticace]

I've encountered this many many times before. I figured out what caused it for me:

• Cross Database Foreign Keys
• Multiple databases transactions open

The combination of the two just don't work well (note: using MySQL at least). I can go into some detail as to what I believe is happening here but it may be just coincidentally related.

Not sure what could be causing the issue for you though. More detail is always helpful.

[themsaid]

@thecrypticace I'm closing this issue due to lack of activity, but please feel free to open a new issue with steps to reproduce so that we can work on it :)

[spyp]

I have this issue few days ago and all mysql server was crashed
my workers is 18 work that using 2 database in one server
So after googling
in dev.mysql.com
said:You should increase max_wait_lock_time for big processing in server when you use innodb
but for user client is better to use small
default is 50 sec
i want to test in cents 7
with ram 32 and 18 cpu core
for 10,000 job that
for now i increased to 50000
when i test it i send feedback for all of you
Hope after that laravel leaders set this issue with documentation in queue section in laravel.com
to warning new members
Good luck

[shadoWalker89]

For me the problem occurs only when running the test, when i open the app in the browser i have no errors at all.
But my app is running laravel 5.3, i don't about newer versions.
I have two connections and cross database foreign keys.

[molerat619]

@thecrypticace we are doing exactly what you mentioned: we use cross database foreign keys and multiple database transactions. That's causing the issue for us.

Any idea how to work around it? Example:

Database::transaction(function() use ($request) {
       $customer = Customer::create($request->all());

       $customer->qualifications()->sync(array_column($request->qualifications, "id"));

       $customer->branches()->sync(array_column($request->branches, "id"));
 });

branches() is a many-to-many relationship to customers and means something like sites, plants or stations.

The reason why we need cross origin foreign keys etc. is that we have some base data that we need across multiple applications and it seems only logical to put it in one central database that the other databases talk to.

[thecrypticace]

If you absolutely require having the cross-db foreign keys then the only suggestion I can provide is that you do all work across multiple databases from a single connection & transaction.

[molerat619]

Thank you @thecrypticace . We are using multiple laravel connections (but the databases are on the same server).

I have realized today that we only have this issue when we have the create-method inside the transaction. It works absolutely fine in the update method.

DB::transaction(function () use ($request, $id) {
    $customer = Customer::find($id);

    $customer->update($request->all());

    $customer->qualifications()->sync(array_column($request->qualifications, "id"));
    $customer->branches()->sync(array_column($request->branches, "id"));
});

Does that help anyone track down the issue a bit more? Or does anyone have ideas how we can achieve the same thing with a different form or creating the element inside the transaction?

[jarnovanleeuwen]

Ran across this while using the DatabaseTransactions trait in our tests. Solved it by disabling foreign key constraints while creating models with cross-database foreign keys:

use Illuminate\Database\Eloquent\Model;
use Schema;

Entity::creating(function (Model $model): void {
    Schema::setConnection($model->getConnection())->disableForeignKeyConstraints();
});

Entity::created(function (Model $model): void {
    Schema::setConnection($model->getConnection())->enableForeignKeyConstraints();
});

Only do this in tests and where it makes sense, of course!

[andrey-helldar]

I just leave the information here, as it corresponds to the subject of the question and I did not find the answer to my case in Google.

Description

The site includes a CORS. When adding a domain name to the clients table, you need to get a list of all names from the database and save them to config/cors.php file, and then execute the configuration caching command.

Problem

laravel General error: 1205 Lock wait timeout exceeded; try restarting transaction

Cause of the problem

When a transaction is initialized, I change the configuration settings:

protected function setConfig(array $hosts): array
{
    config(['cors.allowed_origins' => $hosts]);

    return config('cors', []);
}

A configuration change is invoked through Observer.

The application thinks that we interrupted the connection to the database and honestly tries to open the second with the transaction, as a result of which we get an error.

Solution

I created a Job and transferred the call to the file update method to it.

Now the method will be called only after the transaction is applied in the database and the worker starts working on it.

Profit! 🙂

[JessengBijleng]

I am using a trait which extends the default RefreshDatabase trait. This trait wipes all databases listed in $databasesToTransact variable before starting migrations again. I have overridden the beginDatabaseTransaction method from the RefreshDatabase trait

So at the end over the overridden beginDatabaseTransactions method i added the following code:

$connections = $database->getConnections();

array_walk($connections, function (Connection $connection) {
    $connection->statement("SET FOREIGN_KEY_CHECKS=0;");
});

This, together with explicitly setting the $connection on my models prevents me from running into the lock await issue when using tests.

[cbj4074]

To cross-reference, this issue is discussed in some depth in #23413 .

@thecrypticace offers sound advice for working-around the lock-wait timeout:

If you absolutely require having the cross-db foreign keys then the only suggestion I can provide is that you do all work across multiple databases from a single connection & transaction.

However, this still does not solve the underlying problem, which is that MySQL does not support deferred constraint-checking for InnoDB. So, even if you adhere to the above advice, you still won't be able to insert a row in Table A and refer to it -- via foreign key -- from table Table B, within a transaction, even when using a single connection.

The true solution to this serious limitation in MySQL is to switch to a database that supports deferred constraint-checking for InnoDB, such as PostgreSQL. I describe how to solve this problem once using PostgreSQL in the above-cited thread (with accompanying test):

https://github.com/laravel/framework/issues/23413#issuecomment-708093359