Increase lock timeout for a single query only?

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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

Leave a Reply