Increase lock timeout for a single query only?



  • 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



  • https://mariadb.com/kb/en/innodb-system-variables/#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 https://mariadb.com/kb/en/set-statement/ that can apply to any session variable.

    So:

    set statement innodb_lock_wait_timeout=100 for {query}
    

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2