20

Where would I set the maximum time a query will wait for a lock in MySQL 5.0.68 before timing out?

Stephane Rolland
  • 8,641
  • 10
  • 32
  • 40
Gaius
  • 11,200
  • 3
  • 31
  • 64

3 Answers3

14

By default, it is 50 seconds

Just set innodb_lock_wait_timeout as needed.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • How to set this parameter permanently? – Hari Nov 18 '20 at 16:49
  • 2
    @Hari For MySQL 8, you can run SET PERSIST innodb_lock_wait_timetout = 120;. For MySQL 5.x, you can run SET GLOBAL innodb_lock_wait_timetout = 120; and add the line innodb_lock_wait_timetout = 12; under the [mysqld] group header in your my.cnf so the value would be resused on the next restart of mysqld. – RolandoMySQLDBA Nov 18 '20 at 16:56
  • Thank you @RolandoMySQLDBA – Hari Nov 18 '20 at 17:27
9

Other timeout settings that are general (not just for locks, since innodb_lock_wait_timeout only applies to InnoDB row locks) would be wait_timeout and interactive_timeout (both default to 28,800)

Derek Downey
  • 23,440
  • 11
  • 78
  • 104
4

If you hit the 50-sec InnoDB timeout, you have a poorly designed application!

That timeout exists to catch naughty things that cannot be caught any other way.

Rick James
  • 78,038
  • 5
  • 47
  • 113
  • 5
    Or due to errors, I've an extremely busy mysql and mariadb server and deadlocks are a normal thing ot happen all the time. Even if "impossible".
    If you have a hundred million rows and ten thousands of sql command every second you'll find every sort of bug in mysql (crashes) as well as deadlocks of various sorts.     – John Dec 19 '18 at 19:15
  • 5
    It depens to the situation. From the mysql docs:

    You might decrease this value for highly interactive applications or OLTP systems, to display user feedback quickly or put the update into a queue for processing later. You might increase this value for long-running back-end operations, such as a transform step in a data warehouse that waits for other large insert or update operations to finish.

     – g4b0 Sep 05 '19 at 07:38
  • 1
    Checking that application is poorly built...yep! – Noah Duncan Dec 18 '19 at 20:33
  • This will happen in every application. You might not have enough records to make it happen yet, though. Wait until you are missing the cache all the time. Fun to pretend to be in the elite never-time-out club though. – doug65536 Dec 21 '21 at 07:58
  • that depends, you might be running data archiving using partitioning and you might get other requests like on the API that would require update or insert but is waiting due to the partitioning script is running and has locked the table for the moment – Muhammad Omer Aslam Feb 13 '22 at 13:55
  • @MuhammadOmerAslam - "Partitioning script" -- That sounds like a bad idea. Are you referring to PARTITION BY ...? Or something more generic? – Rick James Feb 13 '22 at 20:10
  • i am talking about table partition by range thats the only way i found so far that is less time consuming in comparision to delete millions of rows after arachiving them to another db on totally a different server than the live one. we need to archive the logs from our tables regularly as they fill up quickly and i use partition by range and then replace partition with a standalone table, which separates the live table from the data that is to be archived. @RickJames – Muhammad Omer Aslam Feb 14 '22 at 12:45
  • @MuhammadOmerAslam - Good. PARTITION BY RANGE, then use "transportable tablespaces" to turn a partition into a table, move to the archive machine, and (optionally) turn it back into a partition. – Rick James Feb 14 '22 at 16:08
  • @RickJames , i use ALTER TABLE {$tableName} EXCHANGE PARTITION oldPt WITH TABLE {$backupTableName}; and then remove partitioning from the main table once exchanged. i also added a question here on code review if anyone can improve or add suggestions https://codereview.stackexchange.com/questions/261415/archiving-moving-data-from-one-database-server-to-another-database-server – Muhammad Omer Aslam Feb 14 '22 at 16:14
  • 1
    @MuhammadOmerAslam - I responded to that Q. For performance issues, come to here or stackoverflow.com. I suspect "Code review" focuses on syntax, which is not the main question you have. – Rick James Feb 14 '22 at 17:35