Laravel 5 - maximum supervisord processes to avoid mysql error "error: 1205 Lock wait timeout exceeded"

In Laravel 5 (and later versions), the "Lock wait timeout exceeded" error can occur when multiple processes or requests are attempting to access and modify the same MySQL database resource simultaneously, and one process is holding a lock that prevents other processes from proceeding.

To avoid this error, you can take the following measures:

1. Optimize Database Queries: Ensure that your database queries are optimized to minimize the time they take to execute. This involves proper indexing, avoiding unnecessary queries, and optimizing complex queries where possible.

2. Implement Database Transactions: Use database transactions appropriately to group related database operations into a single transaction. Transactions can help prevent locking issues by ensuring that a set of related database changes is processed together or not at all.

phpCopy code
DB::transaction(function () {
    // Your database operations here
});

3. Increase MySQL's Lock Wait Timeout: You can increase the MySQL server's lock wait timeout to allow for longer periods before a lock times out. However, increasing the timeout may not be a complete solution if the underlying issue is due to prolonged lock contention.

To change the lock wait timeout, you can set the innodb_lock_wait_timeout variable in your MySQL configuration (my.cnf or my.ini) or set it dynamically in your Laravel configuration file (config/database.php).

phpCopy code
'mysql' => [
    // Other database settings
    'innodb_lock_wait_timeout' => 60, // Set the lock wait timeout in seconds
],

4. Limit Concurrent Processes: You can limit the number of concurrent processes using Laravel's built-in job queues or supervisor. By controlling the number of processes running concurrently, you can reduce the chance of database contention.

Using Supervisor to control the number of concurrent processes can be achieved by adjusting the numprocs option in your Supervisor configuration file.

5. Implement Application-level Locking: If necessary, you can implement application-level locking using Laravel's Lock facade to prevent concurrent access to specific resources or sections of your application.

phpCopy code
use Illuminate\Support\Facades\Lock;

Lock::get('resource_name')->block(5, function () {
    // Code that should be executed exclusively
});

6. Analyze Application Flow and Usage: Analyze your application's flow and usage to identify areas where multiple processes might access the same resource simultaneously. Consider restructuring the application or adding rate limiting or throttling to prevent excessive concurrent requests.

Remember that the "Lock wait timeout exceeded" error may have multiple underlying causes, and the appropriate solution depends on your specific application and usage patterns. Analyze your application's database interactions and resource usage to identify the best approach for avoiding this error.