The question:
Is there a way to increase a lock timeout for a single query, or maybe just that connection, as to not affect the entire database?
I have a problematic query that sometimes fails with 1 second timeout.
So I thought about something like this:
set innodb_lock_wait_timeout=100; query; set innodb_lock_wait_timeout=1;
But this would apply to the entire database right? Is what I want possible? Using mariadb 10.5
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
innodb_lock_wait_timeout is also a session and global variable, so by default SET changes the session only.
An easier way to apply the setting and avoid resetting it is using
SET STATEMENT that can apply to any session variable.
So:
set statement innodb_lock_wait_timeout=100 for {query}
Method 2
Since you are using MariaDB, perhaps you want max_statement_time
If the max_statement_time variable is set, any query (excluding stored procedures) taking longer than the value of max_statement_time (specified in seconds) to execute will be aborted. This can be set globally, by session, as well as per user and per query. See Aborting statements that take longer than a certain time to execute.
Suggest using the slowlog to find the other query that is causing the timeout. Then see if you can speed it up and/or avoid the conflict.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0