1

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

2 Answers 2

2

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}
2
  • did you literally mean for ? Or I should use ; query ; set back?
    – Freedo
    Commented Apr 14, 2022 at 3:43
  • literal for and no ; before the query. No set back is required. See the SET STATEMENT linked manual.
    – danblack
    Commented Apr 14, 2022 at 3:48
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.